MySQLserverhasgoneaway error cause analysis _ MySQL

Error cause analysis of MySQLserverhasgoneaway

During the communication with developers and in the forum, you will find that this question is frequently asked.

An error is reported in the program: What does MySQL server has gone away mean? How to avoid it?

Therefore, it is necessary to summarize the cause of this problem. Today, I just saw a summary of a foreign blog, and I translated it.

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

Cause 1. MySQL service is down

The method for determining whether this is the cause is simple. run the following command to check the mysql running duration:

$ 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 restart information.

$ tail /var/log/mysql/error.log130101 22:22:30 InnoDB: Initializing buffer pool, size = 256.0M130101 22:22:30 InnoDB: Completed initialization of buffer pool130101 22:22:30 InnoDB: highest supported file format is Barracuda.130101 22:22:30 InnoDB: 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 socket 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. This indicates that the service has not been restarted recently.

If the log does not have relevant information and the table name mysql service has not been restarted recently, you can continue to check the following items.

2. connection timeout

If the program uses persistent connections, this situation is more likely.

That is, a long connection has not initiated a new request for a long time, reaching the server timeout, and is forcibly disabled by the server.

When you initiate a query through this connection, the following error occurs: server has gone away

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

mysql> SET SESSION wait_timeout=5;## Wait 10 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.

This is similar to case 2, but the initiator is DBA or another job. It is found that a long slow query execution of kill xxx is 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 also occurs when the query result set exceeds max_allowed_packet. The troubleshooting method is to generate related error statements.

Use select * into outfile to export to the file and check whether the file size exceeds max_allowed_packet. if the file size exceeds, adjust the parameters or optimize 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)

BitsCN.com