Reasons and solutions for MySQL sql_mode modification not taking effect

Reasons and solutions for MySQL sql_mode modification not taking effect

Preface

We have talked about the topic of sql_mode many times recently, and have encountered related problems many times. Today, I will strike while the iron is hot and share with you another case study of sql_mode.

Scenario simulation

Due to business sensitivity considerations, the tables and stored procedures involved below are not real data, but this does not affect the troubleshooting process.

(1) The client developer created a stored procedure that did not strictly follow the standard group by syntax.

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) The client developer calls the stored procedure and reports ERROR 1140. Because the stored procedure was complex and difficult to modify, the client chooses to modify 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) After the client changes sql_mode and executes again, it is still reported 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) At this point, I realized that modifying the system variables is only effective for new connections, not for existing connections. So I asked the client to reconnect, confirm that the system variables have taken effect, and call the stored procedure again, but the error message ERROR 1140 was still displayed. I tried several times and got the same result.

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) Further investigation was conducted by asking the client to execute a non-standard group by statement in the session, and it was found that the statement could be executed normally.

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) Continuing to investigate, we found that the sql_mode of the stored procedure still included ONLY_FULL_GROUP_BY, so the execution reported an error.

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) Here we can also know that system variable modification is only effective for newly created objects, not for existing objects; the solution is very simple, just rebuild the stored procedure

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)

Summarize

Through this case, we can know that modifying the sql_mode system variable is only effective for newly created connections and newly created objects (mainly including functions and stored procedures), and is not effective for existing connections and existing objects.

The above are the detailed contents of the reasons why MySQL sql_mode modification does not take effect and the solutions. For more information about MySQL sql_mode modification not taking effect, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Detailed explanation of MySQL sql_mode query and setting
  • Detailed explanation of viewing and setting SQL Mode in MySQL
  • Detailed explanation of sql_mode mode example in MySQL
  • Django2 connects to MySQL and model test example analysis
  • Detailed explanation on reasonable settings of MySQL sql_mode
  • MySQL sql_mode analysis and setting explanation
  • The pitfalls and solutions caused by the default value of sql_mode in MySQL 5.7
  • The perfect solution for MySql version problem sql_mode=only_full_group_by
  • Solve the MySQL 5.7.9 version sql_mode=only_full_group_by problem
  • Detailed explanation of SQL Mode usage in MySQL
  • mysql sql_mode="" function description
  • Detailed explanation of the use of MySQL sql_mode

<<:  Implement MaterialUI button click animation based on CSS and encapsulate it into a React component

>>:  How to set the border of a web page table

Recommend

Cross-browser local storage Ⅰ

Original text: http://www.planabc.net/2008/08/05/...

Summary of event handling in Vue.js front-end framework

1. v-on event monitoring To listen to DOM events,...

How to simulate network packet loss and delay in Linux

netem and tc: netem is a network simulation modul...

10 performance configuration items that need to be adjusted after installing MySQL

In this blog, we will discuss ten performance set...

Example code for css flex layout with automatic line wrapping

To create a flex container, simply add a display:...

How to remove the dotted border when clicking a link in FireFox

I encountered several browser compatibility issue...

Vue uses v-model to encapsulate the entire process of el-pagination components

Use v-model to bind the paging information object...

Example of stars for CSS rating effect

What? What star coat? Well, let’s look at the pic...

Docker uses the Prune command to clean up the none image

Table of contents The creation and confusion of n...

Detailed tutorial on installing CentOS, JDK and Hadoop on VirtualBox

Table of contents 1. Prerequisites 1.1 Supported ...

MySQL DeadLock troubleshooting full process record

【author】 Liu Bo: Senior Database Manager at Ctrip...

HTML web page creation tutorial Use iframe tags carefully

Using iframes can easily call pages from other we...

WeChat Mini Program uses simultaneous interpretation to implement speech recognition

I use the simultaneous interpretation voice recog...

Detailed explanation of MYSQL character set setting method (terminal character set)

Preface Every time I use the terminal to create a...

MySQL multi-table query detailed explanation

Eating well and getting enough rest sounds simple...