Bug #22891 session level max_allowed_packet can be set but is ignored
Submitted: 2 Oct 2006 13:07 Modified: 18 Jun 2010 1:06
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4,1,5.0,5.1 OS:Any (*)
Assigned to: Staale Smedseng
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[2 Oct 2006 13:07] Shane Bester
Description:
it seems the server ignores the session value of max_allowed_packet.
output from the testcase:

Variable_name   Value
max_allowed_packet      15360
Variable_name   Value
max_allowed_packet      15360
Variable_name   Value
max_allowed_packet      32768
Variable_name   Value
max_allowed_packet      15360
ERROR 2013 (HY000) at line 9: Lost connection to MySQL server during query

How to repeat:
start mysqld with --max_allowed_packet=16k and import the attached .sql file.

Suggested fix:
.
[2 Oct 2006 13:08] Shane Bester
testcase to import via commandline

Attachment: small_testcase.sql (text/x-delimtext), 16.60 KiB.

[3 Oct 2006 15:57] Shane Bester
set as verified. either this is a server bug or it could be addressed via the  documentation.
[5 Oct 2006 18:45] Shane Bester
Simply the problem is this;

1) open a connection to mysql
2) the session's max_allowed_packet is now the same as the global value
3) set session max_allowed_packet=<some larger value>
4) insert into <table> values <long row>

The packet length in 4) must fit into the global max_allowed_packet, even if you have set a much larger session value.  

So, what is the purpose of this syntax:
"SET SESSION max_allowed_packet="? 
It doesn't allow increasing the value above the global value.  I'm not sure if it could do that at all, hence I thought it may be a documentation issue.
[16 Mar 2007 8:32] Andrei Elkin
At least as a temporary fix avoiding confusion of the reporter we'd better to turn @@session.var, max_allowed_packet, net_buffer_length
to read-only.
[23 Mar 2007 1:31] Lachlan Mulcahy
I agree.
[21 Nov 2007 22:00] Guilhem Bichot
Got bitten by this today.
I understand the discussion, but the current state is that we have a variable which the doc says has session and global scope, i.e. SET SESSION is expected to work and not do nothing silently. So either we make SET SESSION effective, or we make it throw an error message.
[2 Feb 2008 2:18] Kolbe Kegel
Is there any plan (or possibility) to do anything about this in 5.1 or 6.0?

I would like to see this variable simply made read-only in 5.1 if we have no intention of fixing the server to properly handle per-session max_allowed_packet.
[18 Mar 2008 8:51] Valerii Kravchuk
Bug #35381 was marked as a duplicate of this one.
[3 Sep 2008 13:15] 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/53160

2705 staale.smedseng@sun.com	2008-09-03
      A fix for Bug#22891 "session level max_allowed_packet can be set 
      but is ignored". 
      
      This patch makes @@session.max_allowed_packed read-only as suggested 
      in the bug report. The user will have to use SET GLOBAL (and reconnect)
      to alter the value of max_allowed_packet. A new error code 
      ER_SESSION_COUNTERPART_IS_READONLY is introduced.
      
      The implementation introduces a new class sys_var_max_allowed_packet,
      inherited from sys_var_thd. Tests are modified accordingly.
[31 Oct 2008 10:10] 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/57539

2773 Staale Smedseng	2008-10-31
      A fix for Bug#22891 "session level max_allowed_packet can be
      set but is ignored".
                                  
      This patch makes @@session.max_allowed_packed and
      @@session.net_buffer_length read-only as suggested in the bug
      report. The user will have to use SET GLOBAL (and reconnect)
      to alter the session values of these variables.
                            
      The error string ER_VARIABLE_IS_READONLY is introduced.
                            
      Tests are modified accordingly.
[20 Nov 2008 7:59] 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/59317

2937 Staale Smedseng	2008-11-20
      A fix for Bug#22891 "session level max_allowed_packet can be
      set but is ignored".
                                        
      This patch makes @@session.max_allowed_packed and
      @@session.net_buffer_length read-only as suggested in the bug
      report. The user will have to use SET GLOBAL (and reconnect)
      to alter the session values of these variables.
                                  
      The error string ER_VARIABLE_IS_READONLY is introduced.
                                  
      Tests are modified accordingly.
[20 Nov 2008 8:08] 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/59319

2719 Staale Smedseng	2008-11-20
      A fix for Bug#22891 "session level max_allowed_packet can be
      set but is ignored".
                                        
      This patch makes @@session.max_allowed_packed and
      @@session.net_buffer_length read-only as suggested in the bug
      report. The user will have to use SET GLOBAL (and reconnect)
      to alter the session values of these variables.
                                  
      The error string ER_VARIABLE_IS_READONLY is introduced.
                                  
      Tests are modified accordingly.
[20 Nov 2008 12:56] 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/59371

2938 Staale Smedseng	2008-11-20 [merge]
      A fix for Bug#22891 "session level max_allowed_packet can be
      set but is ignored".
                                        
      This patch makes @@session.max_allowed_packed and
      @@session.net_buffer_length read-only as suggested in the bug
      report. The user will have to use SET GLOBAL (and reconnect)
      to alter the session values of these variables.
                                  
      The error string ER_VARIABLE_IS_READONLY is introduced.
                                  
      Tests are modified accordingly.
      
      Merge into mysql-6.0-bugteam.

-- 
MySQL Code Commits Mailing List
For list archives: http://lists.mysql.com/commits
To unsubscribe:    http://lists.mysql.com/commits?unsub=commits@bugs.mysql.com
[26 Nov 2008 14:28] Staale Smedseng
Queued to 5.1-bugteam, 6.0-bugteam
[1 Dec 2008 14:30] 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/60280

2766 Georgi Kodinov	2008-12-01
      Addendum to bug #22891.
      Moved the setting of max_allowed_packet to a .opt file.
[8 Dec 2008 10:21] Bugs System
Pushed into 5.1.31  (revid:staale.smedseng@sun.com-20081120075148-urm1h6txon4sakzl) (version source revid:patrick.crews@sun.com-20081126180318-v685u61mpgoc176x) (pib:5)
[8 Dec 2008 11:32] Bugs System
Pushed into 6.0.9-alpha  (revid:kgeorge@mysql.com-20081201142945-si9qmy79kyzhfx7u) (version source revid:kgeorge@mysql.com-20081201142945-si9qmy79kyzhfx7u) (pib:5)
[13 Dec 2008 17:55] Paul Dubois
Noted in 5.1.31, 6.0.9 changelogs.

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.
[15 Dec 2008 10:08] Bugs System
Pushed into 6.0.9-alpha  (revid:guilhem@mysql.com-20081210143052-75of89nnkpr61c3u) (version source revid:guilhem@mysql.com-20081213204800-0nubni3t4ihn4hv9) (pib:5)
[19 Jan 2009 11:31] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:08] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:14] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[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:21] 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)
[26 Aug 2009 13:46] 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:33] 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)
[5 May 2010 15:23] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 2:27] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:58] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:26] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:54] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 21:42] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[15 Jun 2010 8:21] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:38] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 12:00] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:41] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:27] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)