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修改不生效的资料请关注我们其它相关文章!

(0)

相关推荐

  • mysql sql_mode="" 的作用说明

    sql_mode="",即强制不设定MySql模式(如不作输入检测.错误提示.语法模式检查等)应该能提高性能,但有如下问题: 如果插入了不合适数据(错误类型或超常),mysql会将数据设为"最好的可能数据"而不报错,如: /数字 设为:0/可能最小值/可能最大值 /字符串 设为:空串/能够存储的最大容量字符串 /表达式 设为:返回一个可用值(1/0-null) 所以,解决办法是:所有列都要采用默认值,这对性能也好. mysql_mode的详细描述: 在mysql 5

  • mysql中的sql_mode模式实例详解

    本文实例讲述了mysql中的sql_mode模式.分享给大家供大家参考,具体如下: mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法,数据校验等!我们可以通过以下方式查看当前数据库使用的sql_mode: mysql> select @@sql_mode; +----------------------------------------------------------------+ | @@sql_mode | +------------------

  • 详解MySQL的sql_mode查询与设置

    1,执行SQL查看 select @@session.sql_mode; 全局级别: 查看 select @@global.sql_mode; 2,修改 set @@session.sql_mode='xx_mode' set session sql_mode='xx_mode' 全局级别:修改 set global sql_mode='xx_mode'; set @@global.sql_mode='xx_mode'; session均可省略,默认session,仅对当前会话有效 全局修改的话

  • 关于MySQL的sql_mode合理设置详解

    MySQL的sql_mode合理设置 sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入.在生产环境必须将这个值设置为严格模式,所以开发.测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题. 1.sql model 常用来解决下面几类问题: (1) 通过设置sql mode, 可以完成不同严格程度的数据校验,有效地保障数据准备性. (2) 通过设置sql model 为宽松模式,来保证大多数sql符合标准的sql语法

  • MySql版本问题sql_mode=only_full_group_by的完美解决方案

    1.查看sql_mode select @@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 2.去掉ONLY_FULL_GROUP_BY,重新设置值. set @@sql_mode ='STRICT_TRANS_TABLES,NO_ZE

  • 解决MySQL 5.7.9版本sql_mode=only_full_group_by问题

    MySQL 5.7.9版本sql_mode=only_full_group_by问题 用到GROUP BY 语句查询时com.MySQL.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col_user_6.a.START_TIME' which is not func

  • Django2 连接MySQL及model测试实例分析

    本文实例讲述了Django2 连接MySQL及model测试.分享给大家供大家参考,具体如下: 参考:https://www.jb51.net/article/176066.htm 新建个应用 manage.py startapp webtest // 新建一个应用用来测试 然后再到该应用下的**init.py**插入代码(防止报错) import pymysql pymysql.install_as_MySQLdb() 然后再去setting.py配置下数据库连接 接着去models.py里添

  • MySQL中SQL Mode的查看与设置详解

    MySQL中SQL Mode的查看与设置 MySQL可以运行在不同的模式下,而且可以在不同的场景下运行不同的模式,这主要取决于系统变量 sql_mode 的值.本文主要介绍一下这个值的查看与设置,主要在Mac系统下. 对于每个模式的意义和作用,网上很容易找到,本文不做介绍. 按作用区域和时间可分为3个级别,分别是会话级别,全局级别,配置(永久生效)级别. 会话级别: 查看- select @@session.sql_mode; 修改- set @@session.sql_mode='xx_mod

  • MySQL5.7中的sql_mode默认值带来的坑及解决方法

    在正常项目开发过程中,如果MySQL版本从5.6升级到5.7版本.作为DBA在考虑数据库版本升级带来的影响时,一般会有几个注意点: sql_mode optimizer_switch 本文主要内容是MySQL升级到5.7版本之后,由于默认的 sql_mode 值带来的坑以及对应的解决方案. 案例一:ONLY_FULL_GROUP_BY 问题描述 MySQL版本从5.6升级至5.7之后,部分SQL执行报错,报错信息如下: ERROR 1055 (42000): Expression #3 of X

  • MySQL关于sql_mode解析与设置讲解

    昨晚在往MySQL数据库中插入一组数据时,出错了!数据库无情了给我报了个错误:ERROR 1365(22012):Division by 0:意思是说:你不可以往数据库中插入一个 除数为0的运算的结果.于是乎去谷歌了一番,总算是明白了其中的原因:是因为MySQL的sql_mode 模式限制着一些所谓的'不合法'的操作. 解析 这个sql_mode,简而言之就是:它定义了你MySQL应该支持的sql语法,对数据的校验等等.. 如何查看当前数据库使用的sql_mode: mysql> select

  • Mysql之SQL Mode用法详解

    一.Mysql SQL Mode简介 通常来说MySQL服务器能够工作在不同的SQL模式下,并能针对不同的客户端以不同的方式应用这些模式.这样,应用程序就能对服务器操作进行量身定制以满足自己的需求.这类模式定义了MySQL应支持的SQL语法,以及应该在数据上执行何种确认检查.这样,就能在众多不同的环境下.与其他数据库服务器一起更容易地使用MySQL.可以使用"--sql-mode="modes""选项,通过启动mysqld来设置默认的SQL模式.而从MySQL 4.

随机推荐