NO_ZERO_IN_DATE with MySQL 5.7
MySQL
I’m going through some old notes and found this little gem that really confused me last time I was migrating MySQL databases.
Database imports failed for all of my Wordpress websites with the same error:
ERROR 1067 (42000) at line 27: Invalid default value for 'comment_date'
It was weird because I could still import the same databases back on the original server.
A quick search online helped me realise what was happening: MySQL 5.7 changed some default settings and this meant unspecified datestamps would no longer be accepted.
Here’s what I had in the database dump:
CREATE TABLE `wp_comments` (
`comment_ID` bigint unsigned NOT NULL AUTO_INCREMENT,
`comment_post_ID` bigint unsigned NOT NULL DEFAULT '0',
`comment_author` tinytext COLLATE utf8mb4_unicode_520_ci NOT NULL,
`comment_author_email` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`comment_author_url` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`comment_author_IP` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
...
Since I neither wanted nor needed fixing these datestamps, I opted for the workaround:
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
I did this just for the few sessions when I was doing imports, but it’s also possible to configure this as a global variable in MySQL.
pS: later research confirmed this behavior was part of MySQL “STRICT mode” in MySQL 5.7
