2

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 A - This table works as expected

Table B - This table doesnt insert/update due to the extra column Username. Table B - This doesnt work as it has an extra column named 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
3
  • CREATE TABLE TestUserA Add DEFAULT / ON UPDATE modificators to datetime fields - and You do not need in ODKU, simple INSERT IGNORE enough. ALTER TABLE TestUser You make 3 copies of index. WTF? Any idea why this query doesn't work with table B? Enable PHP errors output - and You will SEE errors instead of guess them.
    – Akina
    May 28, 2018 at 18:43
  • To elaborate on what @Akina is saying: A PK is a UNIQUE KEY and a UNIQUE KEY is a key. Don't create redundant keys.
    – Rick James
    Jun 3, 2018 at 13:18
  • What version of MySQL/MariaDB was on the previous server? Current is MariaDB 10.2.14? There may have been changes to the system default settings.
    – Rick James
    Jun 3, 2018 at 13:22

1 Answer 1

5

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.

3
  • Hi @ypercube. I actually tweeted you earlier regarding this. I checked the box for NULL in the username field and ran my query again and it worked first time. i.imgur.com/rz3A9rF.png Although on my old server it never used to say NULL in the blank username box, it would just be blank. I feel like such an idiot.
    – Bonxy
    May 28, 2018 at 16:00
  • Is there any setting i can enable to allow all DB fields to be blank.
    – Bonxy
    May 28, 2018 at 16:08
  • @Bonxy - a VARCHAR column is always allowed to be blank. To have it default to blank requires DEFAULT ('') in the CREATE TABLE.
    – Rick James
    Jun 3, 2018 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.