MySQL server has gone away error cause analysis/

In peacetime and development of the communication and in the forum to answer questions or said that it will find that the frequency of the question is very high.

Program error: What does MySQL server has gone away mean? How to avoid it?

Therefore, it feels necessary to summarize the reasons for this problem. Today just see a Foreign Language blog summary of the better, translation came over

Original: http://ronaldbradford.com/blog/sqlstatehy000-general-error-2006-mysql-server-has-gone-away-2013-01-02/

Cause 1. MySQL service is down.

The way to determine if this is the reason is simple, run the following command to see how long MySQL is running

$ mysql-uroot-p-E "show global status like ' uptime ';" +---------------+-------+| variable_name | Value |+---------------+-------+| Uptime        | 68928 |+---------------+-------+1 row in Set (0.04 sec)

or check the MySQL error log to see if there is any reboot information.

$ tail/var/log/mysql/error.log130101 22:22:30 innodb:initializing buffer pool, size = 256.0m130101 22:22:30 Innodb:comp Leted initialization of buffer pool130101 22:22:30 innodb:highest supported file format is barracuda.130101 22:22:30 Inno db:1.1.8 started; Log sequence number 63444325509130101 22:22:30 [Note] Server hostname (bind-address): ' 127.0.0.1 ';  PORT:3306130101 22:22:30 [note]   -' 127.0.0.1 ' resolves to ' 127.0.0.1 '; 130101 22:22:30 [note] Server sockets created on  IP: ' 127.0.0.1 '. 130101 22:22:30 [note] Event scheduler:loaded 0 events130101 22:22:30 [note]/usr/sbin/mysqld:ready for Connections. Version: ' 5.5.28-CLL '  socket: '/var/lib/mysql/mysql.sock '  port:3306  mysql Community Server (GPL)

If the uptime value is large, it indicates that the MySQL service has been running for a long time. Indicates that the service has not been restarted recently.

If the log does not have relevant information, also the table name MySQL service has not been restarted recently, you can continue to check the following several things.

2. Connection Timeout

If the program uses a long connection, the likelihood of this situation is greater.

That is, a long connection has not been initiated for a long time, reached the server side timeout, was forcibly shut down by the server.

After this connection initiated the query, it will error server has gone away

$ mysql-uroot-p-E "show global variables like '%timeout ';" +----------------------------+----------+| Variable_name              | Value    |+----------------------------+----------+| connect_timeout            | |       | delayed_insert_timeout     | |      | innodb_lock_wait_timeout | | | |       innodb_rollback_on_timeout | OFF      | | interactive_timeout |        28800 |    | lock_wait_timeout          | 31536000 | | net_read_timeout           | 30       | | | net_write_timeout          | |       | slave_net_timeout          | 3600     | | wait_timeout               | 28800    |+----------------------------+--- -------+

Mysql> SET SESSION wait_timeout=5;## wait ten secondsmysql> SELECT now (); ERROR 2006 (HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection ID:    132361Current database: * * * NONE ***+---------------------+| Now ()               |+---------------------+| 2013-01-02 11:31:15 |+---------------------+1 row in Set (0.00 sec)

3. The process is actively killed on the server side

This is similar to Scenario 2, except that the initiator is a DBA or other job. Found that there was a long slow query to execute kill XXX caused.

$ mysql-uroot-p-E "show global status Like ' Com_kill '" +---------------+-------+| variable_name | Value |+---------------+-------+| Com_kill      | 0     |+---------------+-------+

4. Your SQL statement was too large.

This error can also occur when the result set of the query exceeds Max_allowed_packet. The positioning method is to play the relevant error statement.

Export to a file using select * into outfile to see if the file size exceeds max_allowed_packet, and if it is exceeded, you need to adjust the parameters or refine the statement.

Mysql> show global variables like ' max_allowed_packet '; +--------------------+---------+| Variable_name      | Value   |+--------------------+---------+| max_allowed_packet | 1048576 |+--------------------+---------+1 row in Set (0.00 sec)

Modify Parameters:

Mysql> set global max_allowed_packet=1024*1024*16;mysql> show global variables like ' max_allowed_packet '; +------ --------------+----------+| Variable_name      | Value    |+--------------------+----------+| max_allowed_packet | 16777216 |+--------------------+----------+1 row In Set (0.00 sec)

MySQL server has gone away error cause analysis/