Home arrow MySQL arrow Page 4 - Troubleshooting Problems with MySQL Programs

Memory and Lost Connection - MySQL

This appendix from MySQL Administrator's Guide (by MySQL AB, Sams, ISBN: 0672326345) lists some common problems and error messages that you may encounter when running MySQL programs. It describes how to determine the causes of the problems and what to do to solve them.

TABLE OF CONTENTS:
  1. Troubleshooting Problems with MySQL Programs
  2. Common Errors When Using MySQL Programs
  3. Authentication Protocol
  4. Memory and Lost Connection
  5. Packet too Large
  6. Table Full
  7. File Not Found
  8. Installation-Related Issues
  9. Administration-Related Issues
  10. How to Deal with MySQL if it Crashes
  11. How MySQL Handles a Full Disk, Temp Files, Socket Files and Time Zones
By: Sams Publishing
Rating: starstarstarstarstar / 28
September 29, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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):

Error CodeDescription
CR_SERVER_GONE_ERRORThe client couldn't send a question to the server.
CR_SERVER_LOSTThe client didn't get an error when writing to the server, but it didn't get a full answer (or any answer) to the question.

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

SamsThis chapter is from MySQL Administrator's Guide, by MySQL AB. (Sams, 2004, ISBN: 0672326345). Check it out at your favorite bookstore today. Buy this book now.



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: