MySQL Bugs: #107605: Inappropriate error handling for max_allowed

Bug #107605	Inappropriate error handling for max_allowed_packet
Submitted:	20 Jun 2022 8:00	Modified:	21 Jul 2022 14:46
Reporter:	hao chen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	mysql 8.0.29	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Inappropriate error handling for max_allowed_packet

Description:
Connect to the mysql server through the mysql native client, execute the query, and report an error: Got packet bigger than 'max_allowed_packet' bytes, which is no problem. But next time I execute any sql command, it will report the same error message.

How to repeat:
mysql> create database test;
Query OK, 1 row affected (0.03 sec)

mysql> use test;
Database changed
mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)

mysql>
mysql> create table t1(id int key auto_increment, id2 longtext);
Query OK, 0 rows affected (0.08 sec)

// Create 16MB file in OS: dd if=/dev/zero of=test2.txt bs=16M count=1

mysql> load data infile '/home/myhome/test2.txt' into table t1(id2);
Query OK, 1 row affected (1.44 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t1;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes
mysql> show tables in test;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes
mysql> show databases;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes
mysql> show tables in mysql;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes
mysql>

Suggested fix:
According to the source code, it is found that when the client executes select * from t1;, the client and server are disconnected, resulting in all subsequent commands reporting the same error. It is recommended to reconnect with the server when this error is reported for the first time.
Or the next time the command is executed, to be able to reconnect to the server.

Hi Mr. chen,

Thank you for your bug report.

Automatic reconnecting is enabled by default in our binaries. Please check whether it is disabled in your case. If it is disabled, then what you report is expected behaviour.

However, if automatic reconnection is enabled , then let us know whether you can repeat the same behaviour with 8.0.29 server and client binaries.

We are waiting on your reply.

Hi, I'm sure,Automatic reconnecting is enabled in my case.

My test case reproduced the same test results in version 8.0.29. The following is the reproduction process (the automatic reconnection function is enabled):
------------------------------------------------------------------
$ mysql --help |grep reconnect
                      and reconnecting may take a longer time. Disable with
                      mysql and disables rehashing on reconnect.
                      Will automatically be re-executed when reconnecting.
  --reconnect         Reconnect if the connection is lost. Disable with
                      --disable-reconnect. This option is enabled by default.
                      (Defaults to on; use --skip-reconnect to disable.)
reconnect                                 TRUE
------------------------------------------------------------------
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.89 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql>
mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.88 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 8.0.29-debug |
+--------------+
1 row in set (0.00 sec)

mysql> create table t1(id int key auto_increment, id2 longtext);
Query OK, 0 rows affected (1.73 sec)

$ dd if=/dev/zero of=test2.txt bs=16M count=1

mysql> load data infile '/home/myhome/test2.txt' into table t1(id2);
Query OK, 1 row affected (5.27 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql>
mysql> select * from t1;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes
mysql> show tables in test;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes
mysql> show databases;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes
mysql> show tables in mysql;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes
mysql>

Hi Mr. chen,

Thank you for your bug report.

We have repeated very successfully your test case.

This bug is now verified.

Posted by developer:
 
Changelog entry added for MySQL 8.0.31:

Following changes to connection error handling in MySQL 8.0.24, if an error such as exceeding max_allowed_packet caused the mysql client to disconnect from the server, the error was not reset afterwards to indicate the disconnect. On subsequent queries, the original error was returned, and automatic reconnection did not take place because the mysql client did not have a disconnection error.  The error is now reset to indicate disconnection and allow clients to reconnect.

Thank you, Margaret.