Importing a MySQL Dump with Bad Dates

A problem that I run into fairly regularly when transferring older sites to my local environment is that, when importing the MySQL dump, I get an error like:

ERROR 1292 - Incorrect date value: '0000-00-00'

It seems that, prior to version 5.7, MySQL used to allow invalid dates like 0000-00-00. However, this behavior changed in MySQL 5.7 and an error will be thrown when trying to import a date like this. In my experience it is not uncommon to run into these dates lurking in the databases of older websites.

I used to open up the SQL dump in a text editor. Often, it would have to be opened in a program like BBEdit or Vim because of the large size of the SQL dump. Then I would go though the file and replace the bad dates with NULL. This process was slow, monotonous and frustrating. Thankfully, there is a much easier way to import data with these bad dates embedded in them.

Open up your MySQL dump in a text editor. Oh… yeah, as mentioned before, the dump file might be huge so you may end up having to use an editor that can handle very large files. I don’t have a way around that. Thankfully BBEdit has a free version available and, after a year or so, I have learned how to make a simple edit, save the file, and quit in Vim. Seriously, it isn’t that bad and is a good thing to learn for the times when you are on a server and Vim is the only editor available to you.

Okay, you’ve got the file open? Simply add this line to the top:

SET SQL_MODE='ALLOW_INVALID_DATES';

And re-run the import. The 0000-00-00 dates should now be imported into your database without complaint from MySQL.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>