How to fix “Invalid default value” MySQL error on WordPress database

Recently I wanted to duplicate a WordPress database table but I couldn’t because I got the error ERROR 1067 (42000): Invalid default value for 'post_date'.

This fix also works for other Invalid default value errors such as invalid default value for 'user_registered' and invalid default value for 'post_date_gmt'.

It took a bit of Googling but eventually I found the running the following query fixed the issue.

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

You may also find that you need to reset the timezone which can be done with:

SET time_zone = "+00:00";

I’m using Sequel Ace to manage my WordPress databases. To use this load up the database you want to change, add this query to the query tab, then press “run current”.

I should caveat this with the fact that I am not a MySQL expert. I know enough MySQL to be dangerous, so if this goes wrong it’s not my fault!

My database is running locally so if anything goes wrong I can delete it and start again. If you need to run this in production then make sure you do a database backup first and know how to restore it if something doesn’t work as expected.