MySQL Bugs: #1011: ERROR 2006 at line 31: MySQL server has gone away

Bug #1011	ERROR 2006 at line 31: MySQL server has gone away
Submitted:	7 Aug 2003 10:17	Modified:	27 Sep 2009 10:36
Reporter:	Martin Mokrejs	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.14	OS:	Linux (Linux 2.4.22-pre7)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Hi,
  the following happens to me both with 4.0.13 and 4.0.14 offcial tar.gz binaries.

$ mysql -C --max_allowed_packet=128M --set-variable=max_allowed_packet=128M \ -h127.0.0.1 Aquifex_aeolicus_VF5 < contig_data.sql
ERROR 2006 at line 31: MySQL server has gone away
$

  I found it related to the values in /etc/my.cnf.

The first hint is:

vrapenec mysql # PATH=/usr/local/mysql-debug-4.0.14-pc-linux-i686/bin:$PATH /usr/local/mysql-debug-4.0.14-pc-linux-i686/bin/mysqld
030807 20:56:10  Warning: Asked for 196608 thread stack, but got 126976

This message is not reported by mysqld from 4.0.13.
Can you tell me how to configure this bugzilla so that I get an email when someone appends message to this report? Why isn't that default like in real bugzilla software(bugzilla.gnome.org, gentoo.org etc.). Thanks.

How to repeat:
/etc/my.cnf
[mysqld]
# this happens when max_allowed_packet=1M but not when max_allowed_packet=2M,
# actually 1638400 is already fine
set-variable    = max_allowed_packet=1024000
set-variable    = key_buffer=128M
set-variable    = max_user_connections=50
set-variable    = tmp_table_size=128M
set-variable    = table_cache=480M
set-variable    = query_cache_size=32M
set-variable    = sort_buffer=2M
set-variable    = record_buffer=1M
set-variable    = myisam_sort_buffer_size=64M
set-variable    = myisam_max_sort_file_size=96M
set-variable    = back_log=100
set-variable    = interactive_timeout=60000
set-variable    = wait_timeout=60000
set-variable    = connect_timeout=60000
set-variable    = net_read_timeout=60000
set-variable    = open_files_limit=30000
set-variable    = thread_concurrency=2
set-variable    = thread_cache_size=64

$ mysql -C --max_allowed_packet=128M --set-variable=max_allowed_packet=128M -h127.0.0.1 Aquifex_aeolicus_VF5 < contig_data.sql
ERROR 2006 at line 31: MySQL server has gone away
$

This bug report does not qualify as a bug report at all, but rather as
a try to get free support.

max_allowed_packet is a server option which needs to be set in the
[mysqld] section of /etc/my.cnf, not specified to the 'mysql' client
on the command line like you're trying. But generally you're going
in the right direction - the "MySQL server has gone away" error is
caused by a query longer than the max_allowed_packet.

Bugzilla is not a MySQL product and we cannot provide support for it
in any way.

Sorry, I don't understand. You say [mysql] section may not contain max_allowed_packet variable? But why does then mysql client report is in between it's own variables? Couldn't mysqld then report the actual size of max_allowed_packet and say that client asked for more(I mean to display both values, so that users gets a clue why the connection was closed).

$ mysql --help | grep max
  --max_allowed_packet=# 
  --max_join_size=#   
max_allowed_packet                536869888
max_join_size                     1000000
$

BTW: I don't know if this bug reporting software is bugzilla or not, I wast want to get notified when you respond and close my bugreport. Am I supposed to always check for "All" bugs in descending order? :(

One note actually: if the above is the case (as Indrek Siitan claims),
why connection to mysqld works with max_allowed_packet=2M set on server even in case client uses max_allowed_packet=128M? I don't believe the discrepancy is
the reason to loose connection.

I don't understand why max_allowed_packet=1M on server side with client
having max_allowed_packet=128M results in broken connection.

I really mean in [mysqld] is max_allowed_packet=1M or 2M while client gets on a commandline request for max_allowed_packet=128M.

I verified it and it is a bug.

Martin,

I must apologize for multiple things - first, for my incomplete knowledge
of max_allowed packet, and second, for understanding you wrong about Bugzilla.

About max_allowed_packet - I must admit I wasn't aware that this could be
used as a client parameter, but after doing some research I think I'm now
competent in this. Yes, it can be set as a client side parameter, but in this
case it will only affect the server-client traffic. There is no way of
passing this variable to the server from the client, as it would defeat the
whole purpose of it - it's there to limit exhausting server memory with
large queries, but it wouldn't do it's job if anyone could just override it
with a client parameter.

I've talked with Sinisa and what he 'verified as a bug' above is that if you
send a query to the server that is larger than the max_allowed_packet, it
drops the connection without providing a good error message. Yes, that's
what's happening, but the reasoning by the developers for it is that a packet
too large is handled the same way as a bogus packet - there's no knowing if
the connection is still good or what is going on, so the easiest way is just
to drop the connection and move along.

Do you have any further comments on this issue?

About Bugzilla - the system should work so you get an e-mail each time this
bug is updated. Is that not happening for you?

Hi Indrek and Sinisa,
  thank you for response. I really would like to get any message back from the client. Do you want to say server just drop the connection, without actually explaining the client why? If is it so, will you update the documentation for max_allowed_packet and also where the error messages like "Connection dropped" are explained with possible causes? Actually, I'm not satisfied if you just improve documentation. I really want to get a message from the client that the connection was dropped specifically because it has sent too large packet to the server. If the protocol doesn't support such communication, please file this as Feature request. ;)

   I have another report regarding max_allowed_packet and I was told there's loose-max_allowed_packet option available. That should cause clients which do NOT uderstand max_allowed_packet not to die on an unknown variable. I'd say then they must be pretty old, but 4.0.14 dies.

   I don't know where to complain about your bug tracking system. I believe you do great job developing mysql, but please, for me to figure out what's the status with my bugreports, I either have to memorize the bug ID's, or always have to get displayed "All" bugs in descending order and try to find out, which I've reported. THat's how I came to your last response. Please, please, do something with that! Why cannot I receive an email that response to bug XY has been stored?! I've lost track of my old bug reports, and that's bad!!!

No, no, while thinking again what was the original report, why does the connection get dropped when the max_allowed_packet on client is 1M, but doesn't get dropped when the max_allowed_packet is 2M? It is dropped by the client, right! So the user-friendly client should be that kind and say that user asked for max_allowed_packet=1M and that server has sent packet of size 3,5M and therefore it drops the connection.

BTW: The related bug is #1012. I don't believe it is not reproducible.

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

Sorry about the last bug entry, but it was not true.  Sinisa had a patch pending but I didn't approve of it as it didn't solve this problem for all cases.

This problem is already documented in the manual section
'MySQL server has gone away Error':

-----
You can also get these errors if you send a query to the server that is
incorrect or too large. If mysqld gets a packet that is too large.
or out of order, it assumes that something has gone wrong with the client and
closes the connection.
-----

We fixed this problem in 4.0 for not compressed packets but for
compressed packets it's VERY hard to fix it without having to allocate
a buffer bigger than max_allowed_packet, which would defeat the
purpose of this flag.  Ane problem is that to be able to skip the
packet we have to decompress all packets in the stream and this would
make it easy for someone to force the server to allocate a lot of big
packets even if the MySQL administrator has forbidden this.

I looked into fixing this but didn't come up with a good way to to fix
it without a major amount of work (8-16 hours).

As we have more important things on our todo (this is not a serious
bug) we have to put this on hold for now and look at fixing this in
4.1 or 5.0.

The easy way to avoid this problem is to ensure that max_allowed_packet is set bigger in the mysqld server than in the client and that all clients uses the same value for max_allowed_packet.

Regards,
Monty

Monty, I think I'd be happy even with a partial fix. Definitely, leave it please on the wishlist. I think I'm going to disable compression. Can you explain where else compression might be a problem?

I've tested the patch from Sinisa:

the patch helps that I get from mysql(1) client:

ERROR 1153 at line 31: Got a packet bigger than 'max_allowed_packet'

instead of

ERROR 2006 at line 31: MySQL server has gone away

I'm only curious why mysqld from BK tree does NOT log anymore those closed connections after applying that patch, you probably know why:

vrapenec mysql-4.0 # /usr/local/mysql/bin/mysqld --defaults-extra-file=/etc/mysql/my.cnf
--basedir=/usr/local/mysql --datadir=/var/lib/mysql --user=mysql --skip-locking
--open-files-limit=30000
Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the
command line
Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the
command line
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.14-debug-debug'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306
030828 15:38:43  Aborted connection 1 to db: 'Aquifex_aeolicus_VF5' user: 'pedant' host:
`localhost' (Got a packet bigger than 'max_allowed_packet')

030828 15:39:28  Aborted connection 2 to db: 'Aquifex_aeolicus_VF5' user: 'pedant' host:
`localhost' (Got a packet bigger than 'max_allowed_packet')
030828 15:39:56  /usr/local/mysql/bin/mysqld: Normal shutdown

030828 15:39:56  /usr/local/mysql/bin/mysqld: Shutdown Complete

vrapenec mysql-4.0 #
vrapenec mysql-4.0 # /usr/local/mysql-bk/libexec/mysqld
--defaults-extra-file=/etc/mysql/my.cnf --basedir=/usr/local/mysql --datadir=/var/lib/mysql
--user=mysql --skip-locking --open-files-limit=30000
Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the
command line
Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the
command line
/usr/local/mysql-bk/libexec/mysqld: ready for connections.
Version: '4.0.15-debug'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306

Monty,
  as this already been fixed in 5.0 or 5.1 series? ;-)
Thanks for info.

I believe this has been fixed in 5.0 and now we print an error message:
kostja@bodhi:~$ mysql  --max_allowed_packet=18M < foo.sql         
ERROR 1153 (08S01) at line 1: Got a packet bigger than 'max_allowed_packet' bytes

The compressed protocol is still closing the connection without an error message, but the compressed protocol is hardly being used nowadays.

I recieve the same error message :(
What was wrong?

ohh and I use 5.1.36 version.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Please report a separate bug if you experience the described behavior in the newest GA release. The bug will be re-verified as a new report.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.