| Bug #102789 | Different results of SQL query cause by max_allowed_packet | ||
|---|---|---|---|
| Submitted: | 2 Mar 2021 15:35 | Modified: | 2 Mar 2021 16:46 |
| Reporter: | Heng Yang | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 8.0.22 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 Mar 2021 15:38]
Heng Yang
The file is used to create table and load data to repeate this bug
Attachment: load_data.sql (application/octet-stream, text), 700.75 KiB.
[2 Mar 2021 16:46]
MySQL Verification Team
Thank you for the bug report.
With server 8.0.23 and default value of max_allowed_packet I got 483 warnings:
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source c:/tmp/load_data.sql
Query OK, 0 rows affected (0.00 sec)
<CUT>
mysql> SHOW VARIABLES LIKE "max_allowed_packet";
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.02 sec)
mysql> select
-> ref_0.c_phone as c0,
-> ref_0.c_zip as c1,
-> ref_0.c_first as c2,
-> ref_0.c_id as c3
-> from
-> sqltester.t1 as ref_0
-> where (lpad(
-> ref_0.c_zip,
-> ref_0.c_delivery_cnt,
-> ref_0.c_credit) is not NULL)
-> group by 1,2,3,4
-> order by 1,2,3,4;
Empty set, 483 warnings (0.01 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1301 | Result of lpad() was larger than max_allowed_packet (4194304) - truncated |
| Warning | 1301 | Result of lpad() was larger than max_allowed_packet (4194304) - truncated |
And configuring with appropriate value I got 483 results instead of correct warnings:
+--------------------+------------+
| Variable_name | Value |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.02 sec)
mysql> select
-> ref_0.c_phone as c0,
-> ref_0.c_zip as c1,
-> ref_0.c_first as c2,
-> ref_0.c_id as c3
-> from
-> sqltester.t1 as ref_0
-> where (lpad(
-> ref_0.c_zip,
-> ref_0.c_delivery_cnt,
-> ref_0.c_credit) is not NULL)
-> group by 1,2,3,4
-> order by 1,2,3,4;
+--------------+-----------+------+--------+
| c0 | c1 | c2 | c3 |
+--------------+-----------+------+--------+
| 131101912434 | NingBo | lucy | 335721 |
| 131112655729 | Dongguan | lily | 542686 |
| 131118676101 | XinJiang | John | 606763 |
| 131121586335 | ChongQing | John | 55246 |
<cut>
| 199987677526 | NingBo | Jeff | 574160 |
| 199991536006 | Guangdong | lily | 311212 |
+--------------+-----------+------+--------+
483 rows in set (2 min 58.38 sec)
Description: When add max_allowed_packet = 1073741824 in my.cnf,the result of SQL query changed. How to repeat: 1. use sql command in the uploaded file to create table and load data: 2. do not set max_allowed_packet so it is default value. Execute these SQL query as result_1. select ref_0.c_phone as c0, ref_0.c_zip as c1, ref_0.c_first as c2, ref_0.c_id as c3 from sqltester.t1 as ref_0 where (lpad( ref_0.c_zip, ref_0.c_delivery_cnt, ref_0.c_credit) is not NULL) group by 1,2,3,4 order by 1,2,3,4; 3.Set max_allowed_packet = 1073741824 in my.cnf of mysqld, and execute those SQL query above as result_2 and it is different from result_1.