“MySQL server has gone away” Part 1: max_allowed_packet.
peter_laursenMost MySQL users have tried getting this rather cryptic error message: “MySQL server has gone away”. The MySQL documentation describes lots of possible reasons for this here: http://dev.mysql.com/doc/refman/5.1/en/gone-away.html
However this page is of little help for most users, I think. Dozens of reasons are listed, but except for the trivial ones (like physical connection was lost, the MySQL server or the machine where it runs has crashed etc.) there are a few reasons for this that are very common in our experience and a lot of those mentioned are not.
1)
But the documentation at
http://dev.mysql.com/doc/refman/5.1/en/error-messages-client.html
.. also lists another client error:
Error: 2020 (CR_NET_PACKET_TOO_LARGE) Message: Got packet bigger than ‘max_allowed_packet’ bytes
along with
Error: 2006 (CR_SERVER_GONE_ERROR): Message: MySQL server has gone away.
Actually I have not seen the ‘got packet bigger ..’ error myself for many years. Not since MySQL 3.23 or 4.0. I am uncertain if a recent server will sometimes still return ‘got packet bigger’ or not or if also this error message itself has ‘gone away’. If the ‘got packet bigger’ message is still relevant with recent servers it would be nice to have it specified under what conditions it occurs and when only ‘gone away’ will. If this error mesage is now ‘historical’ it should at least be removed from documentation or it should be mentioned that the error no. is reserved for this message – but not used anymore. But it would of course be much preferable to have the ‘got packet bigger’ error returned if that is the problem. It tells what the problem is – “MySQL server has gone away” does not tell anything specific. So ‘got packet bigger’ is a *much* better message than ‘gone away’. Also ‘got packet bigger’ is listed among client errors and not server errors what I would expect. So maybe some problem with my understanding of things here?
Does anybody have any idea about if and why ‘got packet bigger’ now effectively seems to have ‘gone away’ too?
And most important: why disconnect the client? There are reconnect options of course, but it does not really help here. After a reconnect and executing the same query things just repeat themselves.
2)
Basically I never understood why MySQL stick with the default 1M setting for [mysqld] when it is 16M for [mysqldump] in configuration ‘templates’ shipped with the MySQL server (I have tried to ‘hint’ them several times over the last 3-4 years). Obviously they realize that 1M is often too little for backup/restore since they use a larger setting for mysqldump. However users use all other sorts of tools for backup: other script-based tools running on the server, third-party (and MySQL) GUI clients, web-based tools (hosting control panels, phpMyAdmin), backup/restore routines shipping with or built-in applications etc. Often users do not have access to run mysqldump at all on hosted servers (at least not if they are shared servers). Further often Sysadmins are unwilling to change configuration settings and users are left with the option to generate SINGLE INSERTS – with horrible restore performance as a consequence – to ensure cross-server exports/imports (and still it fails with a well-grown MEDIUMBLOB). I deliberately use the term ‘exports/imports’ and not ‘backup/restore’ because it also applies to various tools that can connect to two or more servers at a time and copy data using various techniques without actually generating a file.
The max_allowed_packet problem as described here has been a big problem for us over time. I do not think MySQL fully realises the importance of the problem – mostly because our tools and the tools/clients shipped with the server respectively are used primarily by different segments of users (with some significant overlapping of course). We handle this problem now 100% in SQLyog (we generate the largest BULK INSERTS possible up to 16M everywhere when transferring data from one server to another with all the methods available) but we cannot prevent user - if he wants to use BULK INSERTS - to generate a SQL-DUMP on one server that will not import another because BULK INSERTS are too large. We will of course only be able to handle it if we are connected to both servers.
One solution would be to allow for max_allowed_packet as a SESSION variable. After a long time of unclarity about this – refer to http://bugs.mysql.com/bug.php?id=22891 and http://bugs.mysql.com/bug.php?id=32223
- it is now clear that it is not and will not be possible to override the GLOBAL setting for the SESSION. I regret this! It would be very nice to be able to “SET max_allowed_packet ..” on top of a SQL-script for instance.
4)
And actually – and most basically – I also do not really understand why a max_allowed_packet setting is required at all – except that it makes sense of course that a server admin should be able to restrict not-well-behaving users in bombing the server with statements containing 1 GB large WHERE-clauses! But then we are not talking about 1M but rather something like 16-64-100M as a critical threshold, I think.
Also I am not sure if the reason is that the setting is used to allocate a fixed-size memory buffer for handling the query string or if it is related to handling network packages or whatever. I just wondered for quite some time if such restriction could not be avoided and whether this implementation is a deliberate choice for some reason or rather some consequence of coding techniques used currently. I would like to get rid of it!
Comments(10)
I think I head a “packet too large”-error some days ago … had it on a large “INSERT INTO tbl () VALUES (), (), (), (), …” – Query!
We have to watch for max_allowed_packet settings in Tungsten as well as our older clustering products that move BLOB data between databases. I also don’t understand why this setting exists. Most connectivity libraries chunk large objects automatically; it would be interesting to get a MySQL dev answer on why the setting is still required.
I just ran into this today while doing a test restore on a different MySQL server. Backup file was 212MB, utilizing multi-inserts, restoring from a local .sql file. What pain to bypass…finally figured it out after an hour. With MySQL Admin it seems you have to stop the server before updating the values, then restart. Didn’t ever manage to get it working from commandline.
I get this error usually on SSH tunneling. I tried to see if the connection is the issue, max pachet and so on, but after a while I got that if I disable skip-networking in my.cnf, then will work the SSH tunneling.
When i get this error i use this and again upload the data.
set global max_allowed_packet=1000000000;
set global net_buffer_length=1000000;
Is it ok?
Hi, guys!
This problem is very common in Magento, speciallly on shared hosting servers.
Thanks for the information. I am sure we can use this to fine tune our Magento installs and avoid the problem.
@balu
I think it may depend on server version. After the latest patches has been included I understand that ‘max_allowed_packet’ will have to be defined as a startup parameter (typically in configuration file). I tried
SHOW VARIABLES LIKE ‘max_allowed_packet’; — 1048576
SET GLOBAL max_allowed_packet = 20000000; — approx = 20M
SHOW VARIABLES LIKE ‘max_allowed_packet’; — 1048576
This is 5.1.37 and it still may not have the latest patches. I think server will return an error with the patches. Also if it works for you currently to SET GLOBAL it will not work for users that do not have SUPER privilege (and it should not either of course).
I have no comment to ‘net_buffer_length’. I would have to study it first!
[...] small series on reasons for the error message: “MySQL server has gone away”. The first appeared here describing that “if a client sends a SQL-statement longer than the server max_allowed_packet [...]
Thank goodness this is the first google result. In no time, I realized I just had to shorten the 1000000+char queries to be able to import my backups with sqlyog.
And Notepad++ really helped.
Thanks!
I ran into this same error message (“MySQL server has gone away”) which I agree is not only cryptic, but extremely useless.
After some Internet research and experimentation I found the error message “went away”
after I set the following value in my.ini:
max_allowed_packet=12M
Unfortunately, I did not run across this blog article until after that configuration was tested. If what is said about the other max size is true, I suspect it should actually be 16M instead of 12M to overcome all future issues, so I will set that today before our new deployment. Thanks for the article.