Coupon Code: santa14
Hurry up, offer expires soon!
Blog

“MySQL server has gone away” Part 1: max_allowed_packet.

Most 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.

Here we will discuss one situation that to our experience happens very frequently for people working across multiple servers. The situation is that if a client sends a SQL-statement longer than the server max_allowed_packet setting, the server will simply disconnect the client. Next query from the same client instance will find that the ‘MySQL server has gone away’.  At least it is like that with recent server versions.

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.

3)
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!

26 thoughts on ““MySQL server has gone away” Part 1: max_allowed_packet.

  1. 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.

  2. 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.

  3. 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.

  4. 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?

  5. 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.

  6. @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!

  7. Pingback: Webyog » “MySQL server has gone away” Part 2: session timeout.

  8. 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. :D Thanks!

  9. 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.

  10. I think max_allowed_packet is a con to be honest. I’ve tried changing it, restarting both Apache and MySQL and I STILL get the same problem – Mysql gone away.

    Despite changing almost every upload related limit number (most being 8M or 10M) not even going up to 32M will let me upload a 1.4M jpg to store in a blob.

    I can’t find anyone else on google with this same problem so I’m going to assume that the space-gods have just decided to stand in my way again..

  11. I also encountered the same issue and finally got it fixed by increasing “max_allowed_packet” setting..

    Thanks.

  12. Thanks.It saves my time.i have tried for last 2 days at last i have set
    set global max_allowed_packet=1000000000;
    set global net_buffer_length=1000000;

    Thanks again

  13. Pingback: Mysql has gone away – General error: 2006 MySQL server has gone away – Magento | Spletni sistemi - Internetne rešitve in sistemske integracije

  14. #innodb_buffer_pool_size = 384M
    #innodb_additional_mem_pool_size = 20M
    # Set .._log_file_size to 25 % of buffer pool size
    #innodb_log_file_size = 100M
    #innodb_log_buffer_size = 8M
    #innodb_flush_log_at_trx_commit = 1
    #innodb_lock_wait_timeout = 600000000

    Still getting “SERVER HAS GONE AWAY”

    total database size 130MB

    Please Help me..

  15. @Manish .. this Blog is not the best place to start a discussion of an issue occuring in a specific environment. Better use our Forums. But did you try to raise max_allowed_packet?

  16. The message “Got packet bigger than ‘max_allowed_packet’ bytes” is still valid, as I today discovered. It comes in when you have set the client value for max_allowed_packet lower than the server side variable. Yes, there are two independent values! The server side value is in the SHOW VARIABLES list, and can be set via SET ... for the next session (while the current session is not affected!) as many users may know. The client max_allowed_packet variable cannot be queried at all. All you can do is set it at connection time, with a call to

    mysql_options(MyHandle, MYSQL_READ_DEFAULT_FILE, PathToIniFile)

    where PathToIniFile is a separate my.ini file with a [client] section in which you set max_allowed_packet to some value.

    The default value for the client variable is 1GB, as stated here:

    On the client side, max_allowed_packet has a default of 1GB. Some programs such as mysql and mysqldump enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file

    So, if on the client side it is 1GB, why should anyone want to change that value? I think this is the reason for why the error message “Got packet bigger than ‘max_allowed_packet’ bytes” is so rarely seen.

  17. Thanks Ansgar – but my point was that users struggle with the low 1MB default *on the server* – and don’t get .an error message they can use for resolving the problem. The server just disconnects the client.

    1 GB is both the highest possible setting (maybe this is not documented, but it is stated several times by Shane Bester from MySQL/Oracle in bugs.mysql.com) and the default value for the C-API (and clients built with same) having effect it not specified otherwise in mysql_options(). This is a fine clarification. The client has its own max_allowed_setting as well as the server has.

    For the ‘mysql’ command line client refer http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html#option_mysql_max_allowed_packet states: ” The maximum packet length to send to or receive from the server. (Default value is 16MB.)”.

    When I never see ‘got a packet loo large’ error message it is probably because I rarely use the command-line client (as I believe you also rarely do!) and use a client (guess what!) using C-API defaults and will never get a package exceeding 1 GB (then I would be a fool to write queries!).

    So I think conclusion must be, that if client setting is higher than server setting you will not get ‘got a packet loo large’ error message, but in the opposite case you may. There is some more (but not full) clarification here: http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html (quote “With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.”. But why only with *some* clients? *what* clients and *why*? It is still not clear IMO what the exact condition is)

  18. But why only with *some* clients? *what* clients and *why*

    Yes, this is indeed kind of dirty, in the documentation as well as in the mysql code, as far as I can see. But both kind of clients kill the connection anyway, so where’s the difference?

    So I think conclusion must be, that if client setting is higher than server setting you will not get ‘got a packet loo large’ error message, but in the opposite case you may.

    Exactly, this is what I encountered today, as I was on the way to implement a customizable client side max_allowed_packet variable in HeidiSQL. I had the client setting lower than the server setting, and retrieved several “Got packet bigger than ‘max_allowed_packet’ bytes” errors.

    However, this is all not very helpful, I know. I just wanted to share my experiences on this well documented but often misunderstood area of MySQL. All in all, forget the client side setting. It’s always high enough. Set the server side max_allowed_packet to a reasonable value you need, in a permanent approach, to fix problems.

  19. Very useful. Thank you. Yes, mysql site is a great resource, but sometimes it is not useful for someone in the midst of problem solving.

    I had the double problem that I wasn’t always getting ‘Mysql Server Went Away’. Sometimes I was getting the error ‘Collation_connection can not be set to null’. Somehow, they were related but register at different moments as a different error.

  20. Now only I realize that packet refers to the sql statements. I was having this issue with one of my website which part of the job is to insert a very long concatenated string.

  21. Fabio .. is this related to SQLyog (or MONyog) or not? Please understand that we support our own programs – not the MySQl server or any other program from Oracle.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>