I’m going through some old notes and found this little gem that really confused me last time I was migrating MySQL databases.
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