|
|
现象描述
今天，在程序开启自启动后，所有统计相关的接口都报错了，报错信息为：“this is incompatible with sql_mode=only_full_group_by” 这个问题之前也遇到过，是由于sql_mode的模式选择的不对，导致sql语句中的Group By相关的语句报错，解决办法很简单，就是更改下sql_mode就行了。
解决之路
出现的原因
原理层面
这个错误发生在mysql 5.7 版本及以上版本会出现的问题： mysql 5.7版本默认的sql配置是:sql_mode="ONLY_FULL_GROUP_BY"，这个配置严格执行了"SQL92标准"。 很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。
sql层面
在sql执行时，假设sql语句的基本格式为：
select a,b,c from tableA Group by a,b,c
select target list from tableA gruop by colums出现该原因为：
将输出的结果是叫target list，就是group by后面跟着的字段叫做 colums。sql_mode是一组语法校验规则。由于开启了sql_mode = ONLY_FULL_GROUP_BY的设置，在该模式下，target list中的字段必须在 columns 中.所以如果一个字段没有在target list和group by字段中同时出现，那么这条sql查询是被mysql认为非法的，会报该错误。
初步解决方案
sql_mode
sql_mode是一组语法校验规则
常用sql_mode
①ONLY_FULL_GROUP_BY 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
②NO_AUTO_VALUE_ON_ZERO 该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
③STRICT_TRANS_TABLES 如果一个值不能插入到一个事务中，则中断当前的操作，对非事务表不做限制。
④NO_ZERO_IN_DATE 不允许日期和月份为零
⑤NO_ZERO_DATE mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告
⑥ERROR_FOR_DIVISION_BY_ZERO 在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时Mysql返回NULL
⑦NO_AUTO_CREATE_USER 禁止GRANT创建密码为空的用户
⑧NO_ENGINE_SUBSTITUTION 如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
⑨PIPES_AS_CONCAT 将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样是，也和字符串的拼接函数Concat想类似
⑩ANSI_QUOTES 不能用双引号来引用字符串，因为它被解释为识别符
应该配置的sql_mode
通过对sql_mode的了解，可知解决该问题，只需要在mysqld.cnf配置文件中，删除ONLY_FULL_GROUP_BY这项sql_mode配置即可。
实施步骤
通过查看mysql的配置，发现配置文件中已经删除了ONLY_FULL_GROUP_BY 这项语法约束。且这个问题，之前重启mysql都未出现。查看mysql的配置项如下：
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
#sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=1000所以，现在的问题重点在于，为何mysql的配置文件为何没有生效！
查看mysql的配置文件加载顺序
通过在docker容器中，运行如下命令:
root@b2583801e28b:/# mysql -uroot -p123456 -h127.0.0.1 -P 3306 -D mysql --help | grep .cnf查看文件加载顺序为：
mysql: [Warning] Using a password on the command line interface can be insecure.
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf配置文件生效的顺序，是按照从左往右加载的，因此 ~/.my.cnf 会覆盖前面所有的设置。
项目中的配置文件是由mysql配置信息如下。
version: '3'
services:
mysql:
container_name: mysql
restart: always
image: mysql:5.7.32
ports:
- '13306:3306'
volumes:
- /home/cps/es6000/data/mysql:/var/lib/mysql
- /etc/localtime:/etc/localtime
- /home/sxl/etc/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf配置文件为：/etc/mysql/mysql.conf.d/mysqld.cnf
并未指定上述/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 等配置文件作为mysql的配置文件。进一步查看，发现在docker容器中，/etc/mysql 中包含如下文件：
conf.d my.cnf my.cnf.fallback mysql.cnf mysql.conf.d按照mysql默认的加载顺序，docker 启动时应该加载的是/etc/mysql/my.cnf文件。打开此文件，发现里面的内容如下：
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/其中，!includedir 表示包含/etc/mysql/conf.d/ 和 !includedir /etc/mysql/mysql.conf.d/ 这个路径下面的配置文件。支持，总算明白了，配置文件还是以 /etc/mysql/my.cnf 这个文件加载的时候，顺带加载进去的。
配置文件为何不生效？
经过上面的调查，发现配置文件的配置，加载顺序都是正确的。就是配置文件未生效。在使用 mysql --help命令查看配置文件的过程中，命令行报了一个错误信息：
mysql: [Warning] World-writable config file ‘/etc/mysql/mysql.conf.d/mysqld.cnf‘ is ignored.通过ls - l 查看该配置文件的权限为：
-rwxrwxrwx 1 cps 1001 1822 2月 23 14:48 mysqld.cnf发现该文件的读写权限属于 777。表示其他组的其他权限都是rwx,这是的mysql直接忽略了该配置文件，采用了默认的配置。 如下图所示：
修改配置文件权限
对配置文件进行权限修改，并重启mysql，最终实现了配置文件的更新。
chmod 644 /etc/mysql/mysql.conf.d/mysqld.cnf |
|
发表于 2022-11-28 14:14:44