MySQL server has gone away (when executing SQL, inexplicable error)

Original: Https://cenalulu.github.io/mysql/mysql-has-gone-away/MySQL Server has gone away error causes summary analysis

• Cause 1. MySQL service is down.
• Cause 2. Connection timed out
• Cause 3. The process is actively killed on the server side
• Cause 4. Your SQL statement was too large.

This article summarizes and summarizes the causes of errors such as MySQL Server has gone away

Background: 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: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 ' 130101 22:22:30 [note- ' 127.0.0.1 ' resolves to  ' 127.0.0.1 ' 130101 22:22:30 [note130101 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, it also indicates that the MySQL service has not been restarted recently, you can continue to check the following several things.

Cause 2. Connection timed out

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|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 Ten secondsmysql> Select Now () 2006  (hy000| Now () |+---------------------+| 2013-01-02 11:31:15 |+---------------------+1 row in set  (0.00 sec< Span class= "O" >)              

Cause 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 |+---------------+-------+

Cause 4. Your SQL statement was too large.

This error can also occur when the result set of the query is exceeded max_allowed_packet . The positioning method is to play the relevant error statement. You can select * into outfile export to a file, see if the file size is exceeded max_allowed_packet , or if you want 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 | Span class= "M" >16777216 |+--------------------+----------+1 row in set  (0.00 Sec)          

MySQL server has gone away (in the execution of SQL, inexplicable error)