为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【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