Mysql – Why is ON DUPLICATE KEY not working as expected

MySQLPHPphpmyadmin

Before I start, I'm not a web developer by trade. I'm self taught using google and StackOverflow.

So I recently moved my site from a shared hosting to a VPS.

My provider moved my site / files over to my new hosting. Both run cPanel.

I had a script which worked perfectly fine on my old hosting-

INSERT INTO `database`.`table`
    (`UUID`, `UpdateDate`, `JoinDate`) 
VALUES 
    ('$uuid',NOW(),NOW())
ON DUPLICATE KEY UPDATE
   `UpdateDate` = NOW()

The problem is it doesnt work any more on my new server.

The problem i think is it doesnt update or insert due to not updating all the columns in that table.

I created two test tables, Table A with the 3 columns in above UUID, UpdateDate, JoinDate
and a second
Table B with 4 columns in UUID, Username, UpdateDate, JoinDate.

UUID is both Unique and Primary in both tables.

Table A – This table works as expected.

Table B – This table doesnt insert/update due to the extra column Username.

Any idea why this query doesn't work with table B? Do you have to insert/update every table column when running the query? Is it a PHP settings I need to change on my hosting?

Sorry if I haven't provided all information, this is my first DBSE question.

CREATE TABLE TestUserA` 
( UUID varchar(40) NOT NULL, 
  UpdateDate datetime NOT NULL, 
  JoinDate datetime NOT NULL 
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

ALTER TABLE TestUser 
  ADD PRIMARY KEY (UUID), 
  ADD UNIQUE KEY UUID (UUID), 
  ADD KEY UUID_2 (UUID);

Server type: MariaDB Server version: 10.2.14-MariaDB – MariaDB Server
Protocol version: 10 cpsrvd 11.68.0.36
Database client version: libmysql – 5.1.73
PHP extension: mysqli
PHP version: 5.6.30

Best Answer

Any idea why this query doesn't work with table B? Do you have to insert/update every table column when running the query?

Yes, UserName is a NOT NULL column and doesn't have a DEFAULT value. Therefore you need to provide a value for every row INSERT. Not for UPDATE though, the column can be updated or not, depending on your requirements.

Is it a PHP settings I need to change on my hosting?

No, nothing to do with PHP.

I had a script which worked perfectly fine on my old hosting ...

If the script was working perfectly in the previous server, when inserting in either table, then the tables were not transferred identically. The extra columns - besides the 3 that the script provides values for - would have been defined as NULL (so they'd have a DEFAULT NULL) or there might be a DEFAULT attribute defined when the table was created.

Another possibility - besides lack of DEFAULT attribute - is that the table had an INSERT trigger that supplied a value for the UserName automatically upon insert and that the triggers were not properly copied when the the database was transfered.

Related Solutions

Thesql trigger to insert/update date column from datetime column

It's unclear why you need this but assuming that the table looks something like

CREATE TABLE Table1
    (`id` int not null auto_increment primary key, 
     `dt1` datetime, 
     `dt2` datetime, 
     `d1`  date, 
     `d2`  date);

your triggers might look the following way

CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1), 
      NEW.d2 = DATE(NEW.dt2);

CREATE TRIGGER tg_bu_table1
BEFORE UPDATE ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1), 
      NEW.d2 = DATE(NEW.dt2);

Here is SQLFiddle demo

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Best Answer

Related Solutions

Thesql trigger to insert/update date column from datetime column

Mysql – Adding index to large thesql tables

Related Question