MySQL的sql_mode设置导致报错1292

2022年5月13日 254点热度 0人点赞 0条评论

在MySQL8.0的一个PXC集群中,默认的sql_mode设置如下:

select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

此时某些insert操作会报1292错:
[ERR] 1292 - Incorrect datetime value
字面意思很明显,是数据格式的问题,默认情况下MySQL都设置了严厉模式,不同于Oracle默认的相对宽松的模式,8.0版本的这些严厉模式包括建表时不允许没有主键,插入日期型数据不能为0或其他非法格式等等,这些模式的设置会导致在数据迁移、兼容性测试时报错,要么修改应用限制以适配数据库,要么修改数据库配置以适配已有的应用。
我们选择去掉date相关的2个限制NO_ZERO_IN_DATE和NO_ZERO_DATE,也去掉STRICT_TRANS_TABLES,以下临时修改数据库全局配置:

set global sql_mode = 'ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

这样后续的会话连接就不错报错了,需要注意的是,在PXC集群需要修改所有的数据库节点,如果只修改一个节点,会因为无法实时同步数据而继续报错。
一劳永逸的方法就是修改数据库配置文件/etc/my.cnf,sql_mode设置如下,重启数据库生效:

sql_mode='ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

liking

我是雪人

文章评论