mysql error: Invalid default value for date

Recently encountered this error during MySQL data migration. I inferred it was caused by MySQL version differences - my A and B machines have MySQL versions 5.6 and 5.7 respectively.

Cause

Googled it and found it was actually a MySQL SQL Mode issue.

Solution

Modify configuration file, restart service

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

Note: Removing the sql_mode configuration won’t work