Why is ON DUPLICATE KEY not working as expected? 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
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.