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
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[9 Nov 2007 11:39] Peter Laursen
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!
[9 Nov 2007 11:42] Shane Bester
session max_allowed_packet cannot be set for current session anyway. related Bug #22891
[11 Nov 2007 12:21] Valeriy Kravchuk
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.
[18 Jun 2009 18:47] Bugs System
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.
[19 Jun 2009 9:31] Bugs System
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.
[7 Jul 2009 7:52] Bugs System
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)
[8 Jul 2009 13:30] Bugs System
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)
[9 Jul 2009 7:35] Bugs System
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)
[9 Jul 2009 7:36] Bugs System
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)
[10 Jul 2009 11:20] Bugs System
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)
[16 Jul 2009 20:13] Paul Dubois
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.
[26 Aug 2009 13:45] Bugs System
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)
[26 Aug 2009 13:46] Bugs System
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)
[26 Aug 2009 13:48] Bugs System
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)
[27 Aug 2009 16:32] Bugs System
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)