今天有个需求是把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