Labels

Friday, February 15, 2019

Incorrect datetime value: '0000-00-00 00:00:00' datetime

When attempting to upgrade a server's MySQL that was several version behind I ran into an error on one of the PHP pages doing an insert into a table "Incorrect datetime value: '0000-00-00 00:00:00' datetime".  When I looked at the table I saw that the column in question was set to NOT NULL and had a default value of '0000-00-00 00:00:00'.  After some googling I discovered that this came down to a setting that was set by default on newer version of MySQL called sql-mode.  In order to fix this I ran the following command in MySQL:

show variables like 'sql_mode';

This showed the following:

sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The problem is two of those in the middle, specifically, NO_ZERO_IN_DATE and NO_ZERO_DATE.  I'm not sure what the others do but in deference to the builders of MySQL that put them in there by default I decided to leave the rest in there but remove those two.  In order to do that I changed the my.cnf file (on my system /etc/mysql/my.cnf) and added the following line after the [mysqld] header:

sql-mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

After restarting the mysql server (sudo service mysql restart) when I ran the "show variables" query in MySQL it showed the proper values (i.e. missing the two problematic ones).  For the record, the MySQL version is 5.7.25 and I started at 5.5.62.