SQL Mode: ONLY_FULL_GROUP_BY节点生效不统一的问题

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:

【TiDB 版本】
TiDB版本:v4.0.9
【问题描述】


问题:TiDB 3节点,全部设置了ONLY_FULL_GROUP_BY,但是只有1个节点生效,其他两节点不生效,导致SLB连接TiDB查询的时候,时正常时报错

测试记录:
–节点1:
[tidb@ali-rpt-prod-monitor .tiup]$ mysql -uyxtdba -p -h172.31.0.4 -P4000
MySQL [(none)]> select @@sql_mode;
±------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
±------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> use yxt;
MySQL [yxt]> SELECT pv.id AS project_id, pv.task_count AS pj_task_count, gmv.rpt_user_dept_id, SUM(IF(gmv.project_score > 0, gmv.project_score, 0)) AS dept_project_score
→ FROM o2o_project_view pv
→ LEFT JOIN o2o_group_member_view gmv ON pv.org_id = gmv.org_id AND pv.id = gmv.project_id
→ WHERE pv.org_id = ‘a’
→ AND pv.type = 0
→ AND gmv.deleted = 0
→ AND gmv.role = 1
→ GROUP BY pv.id, gmv.rpt_user_dept_id;
Empty set (0.00 sec)

–节点2:
MySQL [yxt]> exit
Bye
[tidb@ali-rpt-prod-monitor .tiup]$ mysql -uyxtdba -p -h172.31.0.2 -P4000
MySQL [(none)]> select @@sql_mode;
±------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
±------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> use yxt;
MySQL [yxt]> SELECT pv.id AS project_id, pv.task_count AS pj_task_count, gmv.rpt_user_dept_id, SUM(IF(gmv.project_score > 0, gmv.project_score, 0)) AS dept_project_score
→ FROM o2o_project_view pv
→ LEFT JOIN o2o_group_member_view gmv ON pv.org_id = gmv.org_id AND pv.id = gmv.project_id
→ WHERE pv.org_id = ‘a’
→ AND pv.type = 0
→ AND gmv.deleted = 0
→ AND gmv.role = 1
→ GROUP BY pv.id, gmv.rpt_user_dept_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘yxt.pv.task_count’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

可以在各个节点上 set global sql_mode=‘xxxx’ 设置一下变量值,然后再看下还有这个问题吗?

依次在各个TiDB节点设置了global sql_mode,但是仍然没有作用

–正常的节点:

–其他两个没生效的节点


1、连接每个节点看下这个表的表结构可以吗?
2、另外每个节点上执行 show variables 将 session 级别变量结果输出到文本中,看下是不是有别的变量不同的影响。

您好,这个是稳定多次出现的嘛
可以在执行 select sql 语句之前分别执行下看下,是不是变量域的问题
select @@global.sql_mode
select @@sql_mode

–节点1


–节点2

–节点3

昨天有一个节点执行了非标group by后会返回错误,今天所有节点居然都不报错了,但是sql_mode都是ONLY_FULL_GROUP_BY

方便发一下基表的表结构以及 view 视图创建的语句吗?我试试看能不能复现,可能是 view 视图的问题。

你好,很奇怪今天所有节点都不报错了,但是sql_mode设置的是ONLY_FULL_GROUP_BY,按理执行非标group by应该报错

我仔细核对了每个节点的表结构,都是一致的
另外输出参数到文本进行了比对,除了TiDB的节点IP不同,其他参数全部一致


表结构以及视图语句.txt (19.1 KB)
涉及到的表和视图已上传附件,麻烦了

sql_mode设置了ONLY_FULL_GROUP_BY,今天的结果和昨天有点不同,今天所有节点全部正常返回不会报错,另外我不用视图查询,也是可以正常返回,感觉sql_mode没生效

[root@ali-rpt-prod-monitor tmp]# mysql -uroot -p -h172.31.0.2 -P4000
MySQL [(none)]> use yxt;
Database changed
MySQL [yxt]> SELECT pv.id, pv.task_count, gmv.group_id, SUM(IF(gmv.project_score > 0, gmv.project_score, 0)) AS dept_project_score
→ FROM o2o_project pv
→ LEFT JOIN o2o_group_member gmv ON pv.org_id = gmv.org_id AND pv.id = gmv.project_id
→ WHERE pv.org_id = ‘a’
→ AND pv.type = 0
→ AND gmv.deleted = 0
→ AND gmv.role = 1
→ GROUP BY pv.id, gmv.group_id;
Empty set (0.00 sec)

当然group by 主键id没有意义,只是我们拿到的业务的sql是这样,我们也会建议改掉

我在我的环境上尝试复现这个问题,执行 SQL 的时候报了ERROR 1105 (HY000): runtime error: index out of range [-1] ,这个应该是个 bug ,需要研发定位一下

mysql> SELECT pv.id, pv.task_count, gmv.group_id, SUM(IF(gmv.project_score > 0, gmv.project_score, 0)) AS dept_project_score FROM o2o_project pv LEFT JOIN o2o_group_member gmv ON pv.org_id = gmv.org_id AND pv.id = gmv.project_id WHERE pv.org_id = ‘a’ AND pv.type = 0 AND gmv.deleted = 0 AND gmv.role = 1 GROUP BY pv.id, gmv.group_id;
ERROR 1105 (HY000): runtime error: index out of range [-1]

Sorry 这个 panic 的问题似乎是单引号是中文字符引起的,是另一个问题

不好意思,这是我随便敲了值作为参数测试的

随便值这个不影响的

mysql> select @@sql_mode;
±----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
±----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
±----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT pv.id, pv.task_count, gmv.group_id, SUM(IF(gmv.project_score > 0, gmv.project_score, 0)) AS dept_project_score FROM o2
o_project pv LEFT JOIN o2o_group_member gmv ON pv.org_id = gmv.org_id AND pv.id = gmv.project_id WHERE pv.org_id = ‘a’ AND pv.type =
0 AND gmv.deleted = 0 AND gmv.role = 1 GROUP BY pv.id, gmv.group_id;
Empty set (0.00 sec)

mysql> select version();
±----------+
| version() |
±----------+
| 8.0.22 |
±----------+
1 row in set (0.00 sec)

该列虽然是非聚集函数列,也不再 group 列中,但是:group 列中有能够使每个组都只包含其一行的列

This query might be invalid with ONLY_FULL_GROUP_BY enabled because the nonaggregated address column in the select list is not named in the GROUP BY clause:

SELECT name, address, MAX(age) FROM t GROUP BY name;

The query is valid if name is a primary key of t or is a unique NOT NULL column. In such cases, MySQL recognizes that the selected column is functionally dependent on a grouping column. For example, if name is a primary key, its value determines the value of address because each group has only one value of the primary key and thus only one row. As a result, there is no randomness in the choice of address value in a group and no need to reject the query.

关于 SQL mode 不一致的问题,比较难复现,这里不再继续调查了,建议下次遇到时候保留操作和现场,并及时联系我们。

好的,因为是group by的主键,所以本身分组后都是1条数据,当时也考虑到了这个问题,我们再次去复查一下,谢谢

:+1: