Memory and Lost Connection - MySQL

A.2.7 Out of memory

If you issue a query using the mysql client program and receive an error like the following one, it means that mysql does not have enough memory to store the entire query result:

mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
ERROR 2008: MySQL client ran out of memory

To remedy the problem, first check whether your query is correct. Is it reasonable that it should return so many rows? If not, correct the query and try again. Otherwise, you can invoke mysql with the --quick option. This causes it to use the mysql_use_result() C API function to retrieve the result set, which places less of a load on the client (but more on the server).

A.2.8 MySQL server has gone away

This section also covers the related Lost connection to server during query error.

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection. In this case, you normally get one of the following error codes (which one you get is operating system-dependent):

By default, the server closes the connection after eight hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld. See Section 4.2.3, "Server System Variables."

If you have a script, you just have to issue the query again for the client to do an automatic reconnection.

You will also get an error if someone has killed the running thread with a KILL statement or a mysqladmin kill command.

Another common reason the MySQL server has gone away error occurs within an application program is that you tried to run a query after closing the connection to the server. This indicates a logic error in the application that should be corrected.

You can check whether the MySQL server died and restarted by executing mysqladmin version and examining the server's uptime. If the client connection was broken because mysqld crashed and restarted, you should concentrate on finding the reason for the crash. Start by checking whether issuing the query again kills the server again. See Section A.4.2, "What to Do If MySQL Keeps Crashing."

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section A.2.9, "Packet too large."

You will also get a lost connection if you are sending a packet 16MB or larger if your client is older than 4.0.8 and your server is 4.0.8 and above, or the other way around.

If you want to create a bug report regarding this problem, be sure that you include the following information:

• Indicate whether or not the MySQL server died. You can find information about this in the server error log. See Section A.4.2, "What to Do If MySQL Keeps Crashing."

• If a specific query kills mysqld and the tables involved were checked with CHECK TABLE before you ran the query, can you provide a reproducible test case?

• What is the value of the wait_timeout system variable in the MySQL server? (mysqladmin variables gives you the value of this variable.)

• Have you tried to run mysqld with the --log option to determine whether the problem query appears in the log?

See Section 1.7.1.2, "Asking Questions or Reporting Bugs."   

>>> More MySQL Articles          >>> More By Sams Publishing

blog comments powered by Disqus