Bug #75191 Client thread incorrectly caches old max_allowed packet
Submitted: 12 Dec 2014 10:36 Modified: 12 Dec 2015 23:10
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.22 (probably any) OS:Any
Assigned to: Paul Dubois CPU Architecture:Any

[12 Dec 2014 10:36] Peter Laursen
Description:
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_allowed_pac... 

.. clearly states that max_allowed_packet has only global (and not session) scope. But changing its global does not affect how it is displayed for  existing session in SHOW VARIABLES.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

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> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> SET GLOBAL max_allowed_packet = 1073741824;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql>

However in a new connection with any client the correct value (1073741824) is displayed.

Suggested fix:
SHOW VARIABLES should display the actual value an not an old value.
[13 Dec 2014 0:43] Shane Bester
Doubt this is a bug at all. I think all docs are saying is you cannot do SET SESSION max_allowed_packet=.... 
The session value you're seeing is current for that session, even if global value was changed.

------
mysql> select @@global.max_allowed_packet,@@session.max_allowed_packet,version();
+-----------------------------+------------------------------+-----------+
| @@global.max_allowed_packet | @@session.max_allowed_packet | version() |
+-----------------------------+------------------------------+-----------+
|                     4194304 |                      4194304 | 5.6.23    |
+-----------------------------+------------------------------+-----------+
1 row in set (0.00 sec)

mysql> select repeat('a',4194305);
+---------------------+
| repeat('a',4194305) |
+---------------------+
| NULL                |
+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------+
| Level   | Code | Message                                                                     |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1301 | Result of repeat() was larger than max_allowed_packet (4194304) - truncated |
+---------+------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@global.max_allowed_packet,@@session.max_allowed_packet,version();
+-----------------------------+------------------------------+-----------+
| @@global.max_allowed_packet | @@session.max_allowed_packet | version() |
+-----------------------------+------------------------------+-----------+
|                  1073741824 |                      4194304 | 5.6.23    |
+-----------------------------+------------------------------+-----------+
1 row in set (0.00 sec)

mysql> select repeat('a',4194305);
+---------------------+
| repeat('a',4194305) |
+---------------------+
| NULL                |
+---------------------+
1 row in set, 1 warning (0.00 sec)
---
[13 Dec 2014 10:05] Peter Laursen
Doubt this is a bug at all. I think all docs are saying is you cannot do
SET SESSION max_allowed_packet=....
>> I agree. I "SET GLOBAL"

The session value you're seeing is current for that session, even if
global value was changed.
>> If it is possible at all for a session to have (and use) another setting than the global value at any time, I don't find it documented. On the opposite docs clearly state that the scope is global only.
[13 Dec 2014 13:22] Shane Bester
> >> If it is possible at all for a session to have (and use) another setting than the global value at any time, I don't find it documented. On the opposite docs clearly state that the scope is global only.

It is surely possible for a client to specify a lower max-allowed-packet during connection, than the global server value.  It is not possible for a client-side max packet to be causing the server to allocate more memory than the global server max-allowed-packet would allow.  

So, I will convert this to a docs bug, and let docs explain it better.
[12 Dec 2015 23:10] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

re: clearly states that max_allowed_packet has only global (and not session) scope.

That's incorrect, it does have session scope. The session variable is read only, but both scopes exist. I'll correct that. I'll also note that the server won't send to the client more than its current *global* value of this variable. (The global value could be less than the session value if it is changed after the client connects.)