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'.

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";

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!

This database is running locally so if anything goes wrong I can delete it and start again. I have not run it in production and wouldn’t want to, cos I’m not confident I could fix anything that might break.

If anyone knows a better way to fix this then please let me know in the comments.