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.