MySQL sql_mode修改不生效的原因及解决

前言近期多次聊到sql_mode的话题，也是多次遇到相关问题，今天就趁热打铁，再给大家带来一个sql_mode的案例分享。场景模拟基于业务敏感性的考虑，下面涉及的表、存储过程等均非真实数据，但并不影响排查过程。（1）客户侧开发童鞋创建了一个存储过程，该存储过程没有严格遵守group by标准语法12345678910session 1：mysql> d

zhujibcom

6612人浏览 · 2021-05-08 01:57:34

zhujibcom · 2021-05-08 01:57:34 发布

前言

近期多次聊到sql_mode的话题，也是多次遇到相关问题，今天就趁热打铁，再给大家带来一个sql_mode的案例分享。

场景模拟

基于业务敏感性的考虑，下面涉及的表、存储过程等均非真实数据，但并不影响排查过程。

（1）客户侧开发童鞋创建了一个存储过程，该存储过程没有严格遵守group by标准语法

session 1：

mysql> delimiter //

mysql> create procedure test_for_group_by()

-> begin

-> select k,pad,count(*) from test.test group by k;

-> end //

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

（2）客户侧开发童鞋调用该存储过程，报错ERROR 1140；因为当时存储过程比较复杂，改造起来比较麻烦，所以客户侧选择修改sql_mode

session 1：

mysql> call test_for_group_by();

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.test.k'; this is incompatible with sql_mode=only_full_group_by

（3）客户侧修改完sql_mode，再次执行，发现仍然报错ERROR 1140

session 2：

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Query OK, 0 rows affected (0.00 sec)

session 1：

mysql> call test_for_group_by();

（4）此时想到，修改系统变量，只对新建连接有效，对已有连接不起作用；于是，让客户侧重新建立连接，确认系统变量已生效，再次调用存储过程，但仍然报错ERROR 1140，重复尝试几次都是这个结果

session 3：

mysql> show variables like 'sql_mode';

+---------------+------------------------------------------------------------------------------------------------------------------------+

| Variable_name | Value |

+---------------+------------------------------------------------------------------------------------------------------------------------+

| sql_mode | 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.01 sec)

mysql> call test_for_group_by();

（5）进一步排查，让客户侧在该会话，执行非标准的group by语句，发现可以正常执行

session 3：

mysql> select user,host,count(*) From mysql.user group by user;

+---------------+-----------+----------+

| user | host | count(*) |

+---------------+-----------+----------+

| mysql.session | localhost | 1 |

| mysql.sys | localhost | 1 |

| root | localhost | 1 |

| rpl_user | % | 1 |

| test | % | 1 |

+---------------+-----------+----------+

5 rows in set (0.00 sec)

（6）继续排查发现，该存储过程的sql_mode，还是包括ONLY_FULL_GROUP_BY，因此执行报错

session 2：

mysql> select routine_catalog,routine_schema,routine_name,routine_type,created,last_altered,sql_mode from routines where routine_name='test_for_group_by';

+-----------------+----------------+-------------------+--------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+

| def | test | test_for_group_by | PROCEDURE | 2020-12-24 12:12:10 | 2020-12-24 12:12:10 | 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)

（7）这里我们也可以知道，系统变量修改只对新建对象有效，对已有对象不生效；解决办法很简单，重建该存储过程即可