Several solutions for MySQL server has gone away

Note: ldlo860708 is translated from http://dev.mysql.com/doc/refman/5.0/en/gone-away.html. Due to my limited level, it may be worse in some places. I hope that the majority of ITer will not give me any further advice during reading. The most common cause of the "MySQL server has gone away" error is that the service times out and the connection is closed. In this case, you can usually get the following error code (this error does not depend on the operating system ). Error Code Description Cr_server_gone_error The client cannot send requests to the server. Cr_server_lost The client works normally when writing data to the server, but does not receive a complete response to the corresponding request or does not receive a response. By default, the server closes the connection eight hours after nothing happens. You can change the time limit by setting the wait_timeout variable when starting mysqld. If you use a script, you must resend the query for the client to obtain an automatic reconnection. This means that you can use automatic reconnection on the client (default for mysql command lines ). There are several other causes for the "MySQL server has gone away" error: ● The current user (or database administrator) uses the KILL command or the mysqladmin kill command to KILL the running (mysql) process. ● You try to query the server after the server connection is closed. This means that the logic error of the application should be corrected. ● Client applications running on different hosts do not have the necessary permissions to connect to the MySQL server. ● The client's TCP/IP connection times out. If you have used the mysql_options (..., MYSQL_OPT_READ_TIMEOUT) or mysql_options (..., MYSQL_OPT_WRITE_TIMEOUT,...) command, this situation may occur. In this case, adding timeout can solve this problem. ● You encounter timeout on the server and the client's automatic reconnection becomes invalid (the reconnect flag in the MySQL structure is 0 ). ● The server gave up the connection before sending the Windows Client Command (maybe this is because wait_timeout has expired ). In Windows, this problem occurs because, in some cases, MySQL does not receive an error requesting TCP/IP connection from the OS to the server, but obtains an error when the reconnection reads the corresponding information. In MySQL 5.0.19, even if the reconnenct mark in the MySQL structure is equal to 1, MySQL does not automatically reconnect and resend the request because it does not know whether the server has obtained the original query. Solution: if the last query takes a long time, you can make a mysql_ping () on the last connection, or set wait_timeout on the mysqld server to be long enough so that it will not time out. ● If you send an error or a large request to the server, you may also get this error. If mysqld receives a large or unordered package, it means that the client has an error and the connection is closed. If you need a large number of requests (for example, if you are operating on the BLOB column), you can increase the query limit by setting the max_allowed_packet variable of the server. The default value is 1 MB. You may also need to increase the maximum package length on the client. An INSERT command or REPLACE command that inserts a large number of rows can also cause a large number of errors. No matter how many lines need to be inserted, these two commands send only one request to the server. Therefore, this error is often avoided when you reduce the number of rows to be operated upon each INSERT or REPLACE operation. ● If your client is older than 4.0.8 and your server is 4.0.8 or above, if you send a 16 MB or larger package, you will lose the connection. ● If the host name fails to be searched (for example, your server or the DNS server on which the network depends is broken), you may also see this error. This is because MySQL is independent of domain name resolution on the host system, but you cannot know whether it is working-MySQL thinks this problem is no different from other network latency. If the -- skip-networking option of MySQL is started, you may see the MySQL server has gone away error. Another network problem that may cause errors is that the MySQL port is blocked by the firewall and all connections sent to the MySQL server are blocked. ● When an application generates sub-processes, these sub-processes attempt to use the same connection of the MySQL server. In this case, you may also encounter this error. ● This error occurs when the server executes the query. You can check whether the MySQL server is dead, and re-Execute mysqladmin version and check the running time of the server. If the client connection is lost due to mysqld crash and restart, you should focus on the cause of the crash. Check whether a query occurs again and the server is killed again. By starting the -- log-warnings = 2 Option of mysqld, you can get more information about Lost connections. It records the lost connection errors to the hostname. Err file. ● You can find the MySQL server death information in the server error log. ● If a specific query kills mysqld and the table is checked by check table before you run the query, can you provide a repeatable test solution? ● What is the value of wait_timeout, a system variable of the MySQL server? You tried to use the normal query record switch to run mysqld to determine whether the query problem exists in this record?