mysql5.5同步数据到mysql 5.7报错

今天有个需求是把mysql 5.5升级到5.7，同步数据的时候报下面错误

1067 - Invalid default value for 'publish_date'

1067 - Invalid default value for 'DataChange_CreatedTime'

 

时间的默认值不兼容，改下sql_mode即可

mysql> set global
 sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected

mysql> show variables
 like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
1 row in set

上面后可以正常导数了，还可以把它添加到配置文件my.cnf的[mysqld]下，然后重启mysql

sql_mode =STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION