MySQL 5.7 Errors Importing Dates 0000-00-00

One of the gotchas for users upgrading to MySQL 5.7 from previous versions is that strict mode is on by default. Thus, if you have dates in a dump file formatted as zeroes (e.g. 0000-00-00), your import will fail because that format is not allowed in in strict mode.

The fix is pretty simple. For example, on Ubuntu sudo nano /etc/mysql/my.cnf and add the follwing line uder [mysqld]:

sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

This change removes NO_ZERO_IN_DATE and NO_ZERO_DATE modes from the default MySQL modes. See: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict

If you happen to see the following error:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘support_desk.mod_users_groups.group_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

remove ONLY_FULL_GROUP_BY from the sql_mode string. If you want to know the pros/cons of doing this, see this post: Why I Only Use Full Group By

Leave a Comment

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