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:


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

Contact Me

Follow me on Facebook, Twitter or Telegram:
I learn with Educative: Educative
IT Consultancy
I'm a principal consultant with Tech Stack Solutions. I help with cloud architectrure, AWS deployments and automated management of Unix/Linux infrastructure. Get in touch!
Recent Articles
24 Mar 2024

Homelab: Mac Pro 2013 32GB

30 Sep 2023

macOS Sonoma 14.0

26 Sep 2023

Video: What Browser Do You Use?

Recent Tweets