MySQL Bugs: #32223: SETting max_allowed

Bug #32223	SETting max_allowed_packet variable
Submitted:	9 Nov 2007 11:39	Modified:	16 Jul 2009 20:13
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any
Assigned to:	Staale Smedseng	CPU Architecture:	Any
Tags:	qc

Description:
"SET max_allowed_packet .."  has severaal inconsistencies!

Actually I do not know is this variable can be SET by a client, but 

1) If it cannot no error message is returned when doing

2) If it can then
* change does not seem to have effect
* the display for LOCAL and GLOBAL options are inconsitent
* 'M' and 'K' modifiers return an error (they work frrm configuration file)

How to repeat:
select version();
/* 5.0.45-community-nt */

set max_allowed_packet = 500M;
/*Error Code : 1232
Incorrect argument type to variable 'max_allowed_packet'*/

set max_allowed_packet = 500;
/* (0 row(s)affected) */

show local variables like 'max_all%';  
/* returns 1024 for 'max_allowed_packet' */

show global variables like 'max_all%';  
/* returns 104856576 for 'max_allowed_packet' */

Suggested fix:
clarify and fix inconsistencies!

session max_allowed_packet cannot be set for current session anyway. related Bug #22891

Thank you for a bug report. This at least should be clarified in documentation. 

See also bug report Shane mentioned. This variable should become read-only soon, it seems.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76603

2739 Staale Smedseng	2009-06-18
      Bug #32223 SETting max_allowed_packet variable
      
      Inconsistent behavior of session variable max_allowed_packet (and
      net_buffer_length); only assignment to the global variable has
      any effect, without this being obvious to the user.
      
      The patch for Bug#22891 is backported to 5.0, making the two
      session variables read-only. As this is a backport to GA software,
      the error used when trying to assign to the read-only variable
      is ER_UNKNOWN_ERROR. The error message is the same as in 5.1+.
      
      The pertinent tests are updated to account for the new semantics.
     @ mysql-test/t/variables.test
        Tests are changed to account for the new semantics, and assignment
        to the read-only variables is added to test the emission of the
        correct error message.
     @ sql/set_var.cc
        Both max_allowed_packet and net_buffer_length are changed to be
        of type sys_var_thd_ulong_session_readonly. ER_UNKNOWN_ERROR is
        used to indicate an attempt to assign to an instance of a read-
        only variable.
     @ sql/set_var.h
        Class sys_var_thd_ulong_session_readonly is added.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76649

2779 Staale Smedseng	2009-06-19
      Bug #32223 SETting max_allowed_packet variable
            
      Inconsistent behavior of session variable max_allowed_packet 
      (and net_buffer_length); only assignment to the global variable 
      has any effect, without this being obvious to the user.
            
      The patch for Bug#22891 is backported to 5.0, making the two
      session variables read-only. As this is a backport to GA 
      software, the error used when trying to assign to the read-
      only variable is ER_UNKNOWN_ERROR. The error message is the 
      same as in 5.1+.
     @ mysql-test/t/variables.test
        Tests are changed to account for the new semantics, and assignment to the read-only variables is added to test 
        the emission of the correct error message.
     @ sql/set_var.cc
        Both max_allowed_packet and net_buffer_length are changed 
        to be of type sys_var_thd_ulong_session_readonly. ER_UNKNOWN_ERROR is used to indicate an attempt to assign 
        to an instance of a read-only variable.
     @ sql/set_var.h
        Class sys_var_thd_ulong_session_readonly is added.

Pushed into 5.0.84 (revid:joro@sun.com-20090707074938-ksah1ibn0vs92cem) (version source revid:staale.smedseng@sun.com-20090619092719-c3utrzbzttiy7y8a) (merge vers: 5.0.84) (pib:11)

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:sergey.glukhov@sun.com-20090619093109-bu23iwlfkkkluxwn) (merge vers: 5.1.36) (pib:11)

Pushed into 5.0.84 (revid:joro@sun.com-20090707074938-ksah1ibn0vs92cem) (version source revid:staale.smedseng@sun.com-20090619092719-c3utrzbzttiy7y8a) (merge vers: 5.0.84) (pib:11)

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:sergey.glukhov@sun.com-20090619093109-bu23iwlfkkkluxwn) (merge vers: 5.1.36) (pib:11)

Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:sergey.glukhov@sun.com-20090619093254-hciga4kfrv5h3hxe) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.0.84 changelog entry.

Setting the session value of the max_allowed_packet or
net_buffer_length system variable was allowed but had no effect. The
session value of these variables is now read only.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)