MySQL Error When Migrating WordPress Database Table Data

“Invalid default value for datetime”

When transferring data from WordPress DB between environments, you may encounter an error that prevents data transfer due to an issue with the data, resulting in a fail of the data transfer/migration. e.g. Err: Invalid default value for comment_date

The reason for this is that for some WordPress database tables, particularly comments table has data values that may be 0000-00-00 00:00:00

MySQL servers can have a setting which disallows this: “NO_ZERO_IN_DATE,NO_ZERO_DATE“. This is specified in the my.ini file under sql_mode

The Fix

Although it’s not technically a fix, more so a setting adjustment, you can change the sql_mode setting on your server to allow zero dates (which WordPress tables may have).

For Xampp installation, do this:

Navigate to C:\xampp\mysql\bin\my.ini

Find line “mysql_mode” e.g.

sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION

Remove the “no zero date” settings, like so:

sql_mode=NO_ENGINE_SUBSTITUTION

Restart your My SQL server.