variable max_allowed_packet does not take effect

[aylei]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

TiDB:

# run a tidb-server with 2GB memory limit
> docker run -p 4000:4000 -m 2048m  pingcap/tidb:v5.3.0

> mysql -h 127.0.0.1 -P4000 -uroot  -Dtest -e 'select @@max_allowed_packet'
+----------------------+
| @@max_allowed_packet |
+----------------------+
|             67108864 |
+----------------------+

# prepare a large `INSERT INTO` sql
> du -sh ./sql.txt
720M	./sql.txt

# execute the sql and OOM observed
> mysql -h 127.0.0.1 -P4000 -uroot  -Dtest < sql.txt
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

TiDB OOM observed during executing the SQL.

MySQL:

> du -sh sql.txt
 96M	sql.txt

> mysql -h 127.0.0.1 -P3306 -uroot  -Dtest -e 'select @@max_allowed_packet'
+----------------------+
| @@max_allowed_packet |
+----------------------+
|             67108864 |
+----------------------+

# mysql server close the connection but not OOM
> mysql -h 127.0.0.1 -P3306 -uroot  -Dtest < sql.txt
ERROR 2006 (HY000) at line 1: MySQL server has gone away

# use larger max_allowed_packet
> mysql -h 127.0.0.1 -P3306 -uroot  -Dtest -e 'set @@global.max_allowed_packet=671088640'

> mysql -h 127.0.0.1 -P3306 -uroot  -Dtest -e 'select @@max_allowed_packet'
+----------------------+
| @@max_allowed_packet |
+----------------------+
|            671088640 |
+----------------------+

# after enlarge max_allowed_packet, the sql execution succeeds
> mysql -h 127.0.0.1 -P3306 -uroot  -Dtest < sql.txt
(succeed)

Also, according to the documentation, mysql will check the packet size on server-side https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html

2. What did you expect to see? (Required)

TiDB reject the sql that exceeds max_allowed_packet

3. What did you see instead (Required)

TiDB accept the SQL and run into OOM

4. What is your TiDB version? (Required)

v5.3.0

[mjonss]

Duplicate of #31422. I will close this issue and add a comment in the referred issue, that it also can cause OOM.

[bb7133]

Interesting...max_allowed_packet is not actually implemented in the current protocol handling implementations.

Close this issue as stated by @mjonss , let's track it in #31422.