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.

Was it good/ useful/ a load of old rubbish? Let me know on Mastodon, or BlueSky (or Twitter X if you must).

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.

Related Posts

20 Jul 2016

Empathy in Web Design

I wasn’t able to make WordCamp Europe this year, but they’ve been really quick at getting all of the talks online, and so I have been watching some of them – and this one stood out.Morten Rand-Hendriksen is an experienced...
27 May 2013

WordPress: 10 Years Young, What Does The Future Hold?

WordPress is now 10 years old. I started using wordpress 9 years ago – which means I joined the WordPress community early on. The reason I chose WordPress is simply because of the fabled 5 minute install process – I...
08 Apr 2019

WordCamp London 2019

This weekend I went to WordCamp London. I think it was my 5th WordCamp London and, as always, it was a really interesting weekend. I learnt a lot, and came away with a bunch of ideas for things to try...
16 Jun 2017

Fixing Theme Issues with WordPress 4.8 Media Widgets

WordPress 4.8 has just been released and, whilst not a ground breaking update, it includes some nice features that make WordPress more pleasant to use.One of the main areas focused on is some new widgets. There haven’t been any new...
19 Jun 2008

Redesigning the WordPress admin Redesign

Ever since the first betas of WordPress 2.5 I have been making my own version of the admin panel. I like a lot of what they have done but there were some very basic things missing in the design, and...