• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    MySQL sql_mode修改不生效的原因及解决

    前言

    近期多次聊到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();
    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

    (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();
    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

    (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';
    +-----------------+----------------+-------------------+--------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | routine_catalog | routine_schema | routine_name      | routine_type | created             | last_altered        | sql_mode                                                                                                                                  |
    +-----------------+----------------+-------------------+--------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | 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)这里我们也可以知道,系统变量修改只对新建对象有效,对已有对象不生效;解决办法很简单,重建该存储过程即可

    session 3:
    mysql> drop procedure test_for_group_by;
    Query OK, 0 rows affected (0.01 sec)
    
    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 ;
    
    mysql> call test_for_group_by();
    +--------+-------------------------------------------------------------+----------+
    | k      | pad                                                         | count(*) |
    +--------+-------------------------------------------------------------+----------+
    | 393975 | 35227182905-15234265621-59793845249-15413569710-23749555118 |        1 |
    | 495688 | 09512147864-77936258834-40901700703-13541171421-15205431759 |        1 |
    | 497896 | 13152283289-69561545685-52868757241-04245213425-69280254356 |        1 |
    | 498573 | 43131080328-59298106536-35954612339-97546855884-75769514803 |        1 |
    | 500775 | 27590239742-20204899609-34345212327-79811525340-24267764271 |        1 |
    | 501885 | 63188288836-92351140030-06390587585-66802097351-49282961843 |        1 |
    | 503330 | 01495266405-82925129145-92643983850-90243995398-18709399387 |        1 |
    | 503666 | 40929980986-33813039690-13155419391-97985458477-39771362212 |        1 |
    | 504353 | 00505722282-72931248925-57037623248-81117963809-88658076981 |        1 |
    | 514246 | 21979564480-87492594656-60524686334-78820761788-57684966682 |        1 |
    +--------+-------------------------------------------------------------+----------+
    10 rows in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)

    总结

    通过这个案例,我们可以知道,修改sql_mode系统变量,只对新建连接和新建对象(主要包括函数和存储过程)有效,对已有连接和已有对象不生效。

    以上就是MySQL sql_mode修改不生效的原因及解决的详细内容,更多关于MySQL sql_mode修改不生效的资料请关注脚本之家其它相关文章!

    您可能感兴趣的文章:
    • 详解MySQL的sql_mode查询与设置
    • MySQL中SQL Mode的查看与设置详解
    • mysql中的sql_mode模式实例详解
    • Django2 连接MySQL及model测试实例分析
    • 关于MySQL的sql_mode合理设置详解
    • MySQL关于sql_mode解析与设置讲解
    • MySQL5.7中的sql_mode默认值带来的坑及解决方法
    • MySql版本问题sql_mode=only_full_group_by的完美解决方案
    • 解决MySQL 5.7.9版本sql_mode=only_full_group_by问题
    • Mysql之SQL Mode用法详解
    • mysql sql_mode="" 的作用说明
    • MySQL sql_mode的使用详解
    上一篇:一篇文章弄懂MySQL查询语句的执行过程
    下一篇:MySQL Delete 删数据后磁盘空间未释放的原因
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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