Mysql reports General error 2006 MySQL server has gone away... solution

Frequent errors are reported when a scheduled script select table is used. Specific errors include :...... general error: 2006 MySQL server has gone away. the SQL statement executed was: SELECT * FROM ......, after checking, it is found that this is a select query in a loop, and the interval between two select queries exceeds the wait_timeout time defined by the database, that is to say, a mysql persistent connection does not initiate a new request for a long time. After the value of wait_timeout on the server is reached, it is forcibly disabled by the server, that is, the MySQL connection times out. After some tests, the solution is as follows:

Use the mysql_ping () function to Ping a server connection. If a connection exists, true is returned. If it fails, false is returned. If there is no connection, reconnect.

<? Php
Class MySQL (){
Private $ link;
......
Function connect (){
$ This-> link = mysql_connect ('localhost', 'root', '123 ');
    }
......
Function ping (){
If (! Mysql_ping ($ this-> link )){
Mysql_close ($ this-> link); // Note: you must first disable the database. this is the key.
$ This-> connect (); // connect to MySQL. Of course, this method is modified based on your own situation.
        }
    }
......
}

For details about timeout settings of the mysql server, run the following query command:

Show global variables like '% timeout ';

The result may be as follows:

+ ----------------------------- + ---------- +
| Variable_name | Value |
+ ----------------------------- + ---------- +
| Connect_timeout | 10 |
| Delayed_insert_timeout | 300 |
| Innodb_flush_log_at_timeout | 1 |
| Innodb_lock_wait_timeout | 60 |
| Innodb_rollback_on_timeout | OFF |
| Interactive_timeout | 30 |
| Lock_wait_timeout | 31536000 |
| Net_read_timeout | 30 |
| Net_write_timeout | 60 |
| Rpl_stop_slave_timeout | 31536000 |
| Slave_net_timeout | 3600 |
| Wait_timeout | 30 |
+ ----------------------------- + ---------- +

Of course, there may be other situations that will cause MySQL to report the MySQL server has gone away error. I will not explain it here.

MySQL server has gone away error cause analysis and solution

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. log
130101 22:22:30 InnoDB: Initializing buffer pool, size = 256.0 M
130101 22:22:30 InnoDB: Completed initialization of buffer pool
130101 22:22:30 InnoDB: highest supported file format is Barracuda.
130101 22:22:30 InnoDB: 1.1.8 started; log sequence number 63444325509
130101 22:22:30 [Note] Server hostname (bind-address): '2017. 0.0.1 '; port: 127
130101 22:22:30 [Note]-'2017. 0.0.1 'resolves to '2017. 0.0.1 ';
130101 22:22:30 [Note] Server socket created on IP: '2017. 0.0.1 '.
130101 22:22:30 [Note] Event schedents: Loaded 0 events
130101 22:22:30 [Note]/usr/sbin/mysqld: ready for connections.
Version: '5. 5.28-cll' socket: '/var/lib/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 seconds
 
Mysql> select now ();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 132361
Current database: *** NONE ***
 
+ --------------------- +
| NOW () |
+ --------------------- +
| 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 |
+ -------------------- + ---------- +