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.

How was it for you? Let me know on BlueSky or Mastodon

Link to this page

Thanks for reading. I'd really appreciate it if you'd link to this page if you mention it in your newsletter or on your blog.