Working servers suddenly start giving the error below when restoring a MySQL database that contains datetime fields with 0000-00-00 defaults:
user@server:/tmp# mysql -uuser -ppassword database_name < /tmp/backup_file.sql ERROR 1067 (42000) at line 1320: Invalid default value for 'XXX'
This annoying problem occurs because every few years MySQL changes fundamental defaults in MySQL and it’s up to IT specialists to deal with the fallout. In this case MySQL decided ‘0000-00-00 etc’ or lots of zeros isn’t a good default anymore so now blocks you from restoring a database whereas before this use to be perfectly fine.
You can spend a ton of time on research, or you can follow the command below to turn off this 0000 zero check:
mysql> SET GLOBAL sql_mode = ''; Query OK, 0 rows affected (0.00 sec)
Please note when you restart MySQL or reboot the old security mode will be back in, but Hasta la vista baby, at least you can go on with your day. If you’re scared about the temporary security implications, follow the links in the reference.