MySQL sql_mode修改不生效的原因及解决
作者:王文安 发布时间:2024-01-25 03:32:48
标签: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系统变量,只对新建连接和新建对象(主要包括函数和存储过程)有效,对已有连接和已有对象不生效。
来源:https://cloud.tencent.com/developer/inventory/2101/article/1765721
0
投稿
猜你喜欢
- 变量存储在内存中的值。这就意味着在创建变量时会在内存中开辟一个空间。基于变量的数据类型,解释器会分配指定内存,并决定什么数据可以被存储在内存
- 最近整理了一下网上关于MySql 链接url 参数的设置,有不正确的地方希望大家多多指教: mysql JDBC URL格式如下: jdbc
- PyQt5动态(可拖动控件大小)布局控件QSplitter简介PyQt还提供了特殊的布局管理器QSplitter。它可以动态地拖动子控件之间
- 一、背景起源于一个问题:怎样找到字符串中出现次数最多的字符其实使用max函数就能很轻松的解决这个问题:代码:str1 = "AAA
- 霍夫变换是一种检测任何形状的流行技术,可以检测形状,即使它被破坏或扭曲一点点.一条线可以表示成y = mx + c或参数形式,像ρ=xcos
- 1、去官网下载1.1、网址:https://www.jetbrains.com/pycharm/ 点这里进入选择版本页面1.2、如果就是我们
- 一般情况下只有需要长期运行的项目才会去关注内存的增长情况,即使是很小部分的内存泄露经过长期的运行仍然会产生很大的隐患。python本身也是支
- 这个目前还是有个别无法显示,翻了下msdn貌似没看到更好的解决方案,暂时放弃继续研究,有晓得完全解决的朋友不妨回复说一声。 先附bat创建畸
- 引言什么是数据结构?数据结构是指相互之间存在着一种或多种关系的数据元素的集合和该集合中数据元素之间的关系组成。简单来说,数据结构就是设计数据
- 代码如下:function HTMLEncode(fString) fString=Replace(fString,&q
- 本文较为深入的探究了php中in_array函数用法。分享给大家供大家参考。具体如下:今天突然想到php中的in_array函数有个其怪的用
- 今天来和大家聊聊抽样的几种常用方法,以及在Python中是如何实现的。抽样是统计学、机器学习中非常重要,也是经常用到的方法,因为大多时候使用
- 在程序中,变量就是一个名称,让我们更加方便记忆。cars = 100 space_in_a_car = 4.0 drivers = 30 p
- 在之前的文章中,我们介绍了PyQt5和PySide2中主窗口控件MainWindow的使用、窗口控件的4中基础布局管理。从本篇开始,我们来了
- cv的xdm可以直接翻到 需求 和 代码 用,想看中间的分析就老实往下看吧little 背景最近在做毕设,需要将几百篇整体结构差不多的文章中
- 本文实例讲述了Python实现的重启关机程序的方法,对Python程序设计有一定的参考价值。具体方法如下:实例代码如下:#!/usr/bin
- Matrix是Array的一个小的分支,包含于Array。所以matrix 拥有array的所有特性。但在数组乘和矩阵乘时,两者各有不同,如
- # 比较两个字符串,如果不同返回第一个不相同的位置# 如果相同返回0def cmpstr(str1, str2): &
- 一、多线程间的资源竞争以下列task1(),task2()两个函数为例,分别将对全局变量num加一重复一千万次循环(数据大一些,太小的话执行
- python多线程适合IO密集型场景,而在CPU密集型场景,并不能充分利用多核CPU,而协程本质基于线程,同样不能充分发挥多核的优势。针对计