MySQL Bugs: #75191: Client thread incorrectly caches old max

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

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.

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)
---

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.

> >> 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.

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.)