MySQL Bugs: #81465: Docs claim that max_allowed

Bug #81465	Docs claim that max_allowed_packet has SESSION scope
Submitted:	17 May 2016 13:07	Modified:	16 Jun 2016 14:36
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5/5.6/5.7	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html saysthat scope is "BOTH" (ie. both GLOBAL and SESSION), but you cannot set its SESSION scope from a client.

Also see
http://bugs.mysql.com/bug.php?id=22891
http://bugs.mysql.com/bug.php?id=32223

How to repeat:
SELECT VERSION();
-- 5.7.12-log 

SET SESSION max_allowed_packet = 1073741824;
-- Error Code: 1621
-- SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value

Suggested fix:
I looks like docs were not properly updated when Shanes bug above was closed. It should read "GLOBAL" - not "BOTH" - in the tabular overview, as far as I can understand. 

Refers to 5.5 and 5.6 docs as well, I think.

Changed synopsis.

Thank you for the bug report.

Actually the error message is incorrect too. 

The error message states that the variable is "READ ONLY". This is not true as it is WRITEABLE (or 'dynamic' using the terminology of the MySQL documentation) as regards its (GLOBALonly) value/setting (but for users with SUPER privilege only, obviously).

So it is not only a docs bug.  There is both a docs error and a server_error_message bug here. *writeability* (or lack of same) and '*scope* is totally messed up here. 

(I can't help to add this analogy (as common sence unrelated to programming sometimes clarify tings) : An apple is EATABLE. But not for for everyone. For instance not for tigers, as tigers were not granted privilege to eat fruits. And tigers themselves cannot "SET FOR_ME APPLES EATABLE". They dont have privilege to do so either. Some(SUPER)body (be it The Lord, be it nature, be it Santa Claus - whatever you believe), has reserved that privilege for herself and has not shared it with tigers).

I'm not sure what the docs error is supposed to be. max_allowed_packet does indeed exist in session scope.

mysql> select @@session.max_allowed_packet;
+------------------------------+
| @@session.max_allowed_packet |
+------------------------------+
|                      4194304 |
+------------------------------+

That said, the session value is not writeable. Thus, the docs say:

The session value of this variable is read only. The client can receive up to as many bytes as the session value. However, the server will not send to the client more bytes than the current global max_allowed_packet value. (The global value could be less than the session value if the global value is changed after the client connects.)

Then have a look at this

SHOW SESSION VARIABLES LIKE 'check_proxy_users';

--returns

Variable_name      VALUE   
-----------------  --------
check_proxy_users  OFF  

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html tell that this variable has GLOBAL scope (only - not BOTH). Still SHOW SESSION VARIABLES .. return a value So either docs are wrong about max_allowed_packet or about check_proxy_users (and more) because their behavor is the same. The session value is inherited from the global value and cannot be set or changed by the client. 

In my understanding all variables will have a value for the session.  But some cannot have a value in the session different from the global value. Those listed in the docs as BOTH should be those here the client can change its own session value to something different than global value.

mysql> select @@session.max_allowed_packet;
+------------------------------+
| @@session.max_allowed_packet |
+------------------------------+
|                      4194304 |
+------------------------------+
1 row in set (0.01 sec)

mysql> select @@global.max_allowed_packet;
+-----------------------------+
| @@global.max_allowed_packet |
+-----------------------------+
|                     4194304 |
+-----------------------------+
1 row in set (0.01 sec)

The variable exists in both scopes.

mysql> select @@session.check_proxy_users;
ERROR 1238 (HY000): Variable 'check_proxy_users' is a GLOBAL variable

The session variable does not exist.

re: "Those listed in the docs as BOTH should be those here the client can change its own session value to something different than global value."

No, those listed as BOTH are those that exist in both scopes. Whether a given variable can be modified is a different question.