MySQL Bugs: #52802: SET GLOBAL max_allowed

Bug #52802	SET GLOBAL max_allowed_packet silently fails
Submitted:	14 Apr 2010 2:47	Modified:	14 Apr 2010 4:58
Reporter:	Robert Poor	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S2 (Serious)
Version:	5.1.36	OS:	MacOS (10.5.8)
Assigned to:		CPU Architecture:	Any
Tags:	max_allowed_packet

Description:
This appears to be a bug: the SET command can complete without error, but the value is unchanged (even in 5.1.36).  

I'm logged into mysql as root, the SET completes without error, but the value of max_allowed_packet is unchanged.  This prevents our system from loading large BLOBs.
====================
mysql> set global max_allowed_packet=8388608;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 | 
+--------------------+---------+
1 row in set (0.05 sec)

How to repeat:
As shown above.

I wrote a Blog about problems with max_allowed_packet some time ago:
http://www.webyog.com/blog/2009/08/10/mysql-server-has-gone-away-part-1-max_allowed_packet...

.. inside it you will some some links to earlier bug reports that are now closed. Looks like a regression. Definitely an error should occur telling that this parameter will have to be set at startup.

Peter
(not a MySQL person)

-- OK .. I also noted with 5.1.37 this 

SHOW VARIABLES LIKE ‘max_allowed_packet’; — 1048576
SET GLOBAL max_allowed_packet = 20000000; — no error
SHOW VARIABLES LIKE ‘max_allowed_packet’; — 1048576

-- However with MySQL 5.1.44 

SET GLOBAL max_allowed_packet = 40000000;
-- no error

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

SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'; -- 39999488
SHOW SESSION VARIABLES LIKE 'max_allowed_packet'; -- 39999488

-- so now it seems like they allow for specifying max-allowed_packet 'on the fly'
-- (as opposite to what they told in the old reports)
-- I wonder if it works!
--
-- clarification much appreciated!

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/set-option.html:

If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL  statement).

@Sveta .. my 5.1.44 example shows that this 

"However, the change affects the corresponding session
variable only for clients that connect after the change."

.. is NOT how the server behaves with 'max_allowed_packet'.  I SET GLOBAL and SESSION is affected.

Also read the old reprots http://bugs.mysql.com/bug.php?id=22891 and http://bugs.mysql.com/bug.php?id=32223

.. where the conclusion was that max_allowed_packet should be a startup parameter only.

Besides documentation is wrong.  See newly created report at:
http://bugs.mysql.com/52805