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.
Let me know what you think on Mastodon, or BlueSky (or Twitter X if you must).