I have 2 MYSQL columns titled
[TABLE=“class: nowrap, cellspacing: 0”]
[TR]
created [TD]timestamp [0000-00-00 00:00:00][/TD]
[TD] [/TD]
[/TR]
[TR=“class: odd”]
updated [TD]timestamp [CURRENT_TIMESTAMP][/TD]
[/TR]
[/TABLE]
The PHP code is null, null and this worked in MariaDB, however, moving to Percona Server 8 on Debian 10 throws up this error:
Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘created’ cannot be null in /application/post.php
Upon research, this error is caused by MYSQL being in strict mode. What would be the safest/best course of action for this issue?
Thank you,
sudobash
Hi sudobash
Just to clarify, this is a behaviour of upstream MySQL and not specific to Percona Server for MySQL.
There are a number of differences between MySQL / Percona Server for MySQL and MariaDB relating to this issue: [LIST=1]
[]the default sql_mode differs between and MySQL includes NO_ZERO_IN_DATE, NO_ZERO_DATE
[]the default value for explicit_defaults_for_timestamp is ON for MySQL and OFF for MariaDB
[/LIST] The specific error that you are getting is due to explicit_defaults_for_timestamp, so if you really want this behaviour then you can do one of the following: [LIST=1]
[]set explicit_defaults_for_timestamp to 0 for the session in MySQL / Percona Server for MySQL and allow the same behaviour; note that this is not possible in MariaDB as they have made it a read-only variable
[]set explicit_defaults_for_timestamp to 0 globally for the server and persist to config, thus affecting all sessions
[/LIST] However, you should question why you want to do this as your table has been defined to reject null values (NOT NULL on the field definition) and so this breaks expectations and can cause you issues later on. In addition, with the sql_mode being different, you would not be able to insert a default value as then you would receive an “Incorrect datetime value” error.
As you are inserting null to trigger a real date, the best idea is probably to just insert the date in the first place and replace NULL with NOW() in your SQL. It is clear to the reader, it will not allow errors caused by configuration differences, and you always get what you ask for 
Ceri
Thank you Ceri for your help!
I was able to replace NULL, NULL with NOW(), NOW() in my PHP code and the script works like it should.
This is much appreciated, have a good rest of your day!