NO_ZERO_IN_DATE with MySQL 5.7

MySQL 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




Keep Learning with Me

Follow me on Facebook and Twitter or jump into Telegram chat!:
Recommended Software
I use Brave browser, it's awesome: Brave Browser I'm also a fan of SetApp for macOS: SetApp for macOS
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
18 Jun 2021

CentOS 8.4 Released

17 Jun 2021

yum/dnf Package Manager Basics

07 Jun 2021

/proc/mdstat

Recent Tweets