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

See Also

• Migrating Wordpress to Jekyll
• Book review: Learning MySQL
• Perfect Blogger