前几天,将生产服务器上的数据库导出到sql文件,然后准备在测试环境(Ubuntu 18.04)导入MySQL(5.7)的时候出意外了,导入到48%的时候报 1067 - Invalid default value for 'update_at' 错误,重试了几遍还是不行,因为涉及到数据更新,按照公司规定:不经过测试验证是不能上生产的,所有操作必须在测试环境验证,所以这个问题让我困扰了好几天。
问题所在
字段 'update_at' 为 timestamp类型,取值范围是:1970-01-01 00:00:00 到 2037-12-31 23:59:59(UTC +8 北京时间从1970-01-01 08:00:00 开始),而这里默认给了空值,所以导致失败。开始想过吧默认值改掉不就行了,可是这个文件有500多M,并且要经常导数据,总不能每次都手工改数据吧,思来想去还是得从根本上解决问题。
解决办法
在mysql命令行模式下输入
mysql> select @@sql_mode;
会得到类似以下结果
+--------------------------------------------------------------------------------------------+
| @@sql_mode |
+--------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION|
+--------------------------------------------------------------------------------------------+
1 row in set
只要把其中 NO_ZERO_IN_DATE,NO_ZERO_DATE 这两个参数去掉就行了,这是从5.6版本开始增加的 sql_mode 配置参数,用以适配各种环境。
最终解决
在命令行输入:
set GLOBAL sql_mode ='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
然后再次导入sql,成功!
但是,这样并不完美,总不能每次导入都来设置一次sql_mode吧,于是我在my.cnf 中 [mysqld] 小节下添加了如下语句:
sql_mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
保存后重启MySQL,不出意外的话就要出意外了,我发现根本没有任何改变,select @@sql_mode 还是之前的参数,再次导入sql文件还是失败。此刻,我辞职的心都有了。
静下心来慢慢的想,无意中发现 my.cnf 的权限是 777 ,之前修改的时候因为要root权限,我嫌麻烦就给改成了777,莫不是因为这个问题?因为如果文件权限过大(全局可写),MySQL 会出现安全考虑不会读取该配置文件。于是立马将文件权限改成 644 重启 MySQL,问题解决!