Ошибка при загрузке модуля mysql server has gone away

Рассмотрим, как исправить ошибку MySQL server has gone away (error 2006), которая появляется при обращении к сервису MySQL.

Наиболее распространение причины ошибки MySQL server has gone away:

• Слишком большой размер пакета в запросе к MySQL (по умолчанию максимальный размер — 16 Мб);
• Закончилась свободная оперативная память (RAM) на сервере MySQL (проверить свободную память в Linux можно с помощью команды free –h)
• Неактивное соединение между вашим приложением и MySQL (по умолчанию сессия разрывается через 8 часов).

General error: 2006 MySQL server has gone away

Error Code: 2013. Lost connection to MySQL server during query

PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

Чтобы увеличить таймаут для подключений к MySQL, нужно добавить следующие опции в конфигурационный файл mysqld.cnf:

sudo nano /etc/mysql/my.cnf

Найдите секцию [mysqld] и увеличьте таймаут до 24 часов:

wait_timeout = 86400
interactive_timeout = 86400

Если вы загружаете в MySQL большие файлы или BLOB объекты более 16 Мб, то MySQL с настройками по-умолчанию также может вернуть ошибку MySQL server has gone away. Нужно увеличить максимальный размер пакета в my.cnf.

Для этого в секции [mysqld] увеличьте значение параметра max_allowed_packet со стандартного 16M до 128MB:

max_allowed_packet = 128MB

После внесения изменений в файл mysqld.cnf нужно перезапустить сервис MySQL:

• В CentOS/RHEL:sudo systemctl restart mysqld
• В Ubuntu: sudo systemctl restart mysql.service

Если вы подключаетесь к MySQL из PHP, проверьте что значения таймаутов в php.ini больше, чем в MySQL

mysql.connect_timeout=86400
mysql.allow_persistent=1

In general the error:

Error: 2006 (CR_SERVER_GONE_ERROR) — MySQL server has gone away

means that the client couldn’t send a question to the server.

mysql import

In your specific case while importing the database file via mysql, this most likely mean that some of the queries in the SQL file are too large to import and they couldn’t be executed on the server, therefore client fails on the first occurred error.

So you’ve the following possibilities:

• Add force option (-f) for mysql to proceed and execute rest of the queries.

  This is useful if the database has some large queries related to cache which aren’t relevant anyway.

• Increase max_allowed_packet and wait_timeout in your server config (e.g. ~/.my.cnf).

• Dump the database using --skip-extended-insert option to break down the large queries. Then import it again.

• Try applying --max-allowed-packet option for mysql.

Common reasons

In general this error could mean several things, such as:

• a query to the server is incorrect or too large,

  Solution: Increase max_allowed_packet variable.

  • Make sure the variable is under [mysqld] section, not [mysql].

  • Don’t afraid to use large numbers for testing (like 1G).

  • Don’t forget to restart the MySQL/MariaDB server.

  • Double check the value was set properly by:

    mysql -sve "SELECT @@max_allowed_packet" # or:
    mysql -sve "SHOW VARIABLES LIKE 'max_allowed_packet'"
    

• You got a timeout from the TCP/IP connection on the client side.

  Solution: Increase wait_timeout variable.

• You tried to run a query after the connection to the server has been closed.

  Solution: A logic error in the application should be corrected.

• Host name lookups failed (e.g. DNS server issue), or server has been started with --skip-networking option.

  Another possibility is that your firewall blocks the MySQL port (e.g. 3306 by default).

• The running thread has been killed, so retry again.

• You have encountered a bug where the server died while executing the query.

• A client running on a different host does not have the necessary privileges to connect.

• And many more, so learn more at: B.5.2.9 MySQL server has gone away.

Debugging

Here are few expert-level debug ideas:

• Check the logs, e.g.

  sudo tail -f $(mysql -Nse "SELECT @@GLOBAL.log_error")
  

• Test your connection via mysql, telnet or ping functions (e.g. mysql_ping in PHP).

• Use tcpdump to sniff the MySQL communication (won’t work for socket connection), e.g.:

  sudo tcpdump -i lo0 -s 1500 -nl -w- port mysql | strings
  

• On Linux, use strace. On BSD/Mac use dtrace/dtruss, e.g.

  sudo dtruss -a -fn mysqld 2>&1
  

  See: Getting started with DTracing MySQL

Learn more how to debug MySQL server or client at: 26.5 Debugging and Porting MySQL.

For reference, check the source code in sql-common/client.c file responsible for throwing the CR_SERVER_GONE_ERROR error for the client command.

MYSQL_TRACE(SEND_COMMAND, mysql, (command, header_length, arg_length, header, arg));
if (net_write_command(net,(uchar) command, header, header_length,
          arg, arg_length))
{
  set_mysql_error(mysql, CR_SERVER_GONE_ERROR, unknown_sqlstate);
  goto end;
}

We all like when error messages are descriptive and give a clear idea about what is happening; however, there are some cases when a few possible reasons lay behind one error message. “MySQL server has gone away” is one of them. Most of the cases when the error occurs are described in MySQL documentation, but it can get tricky. And here, I’d like to talk about “tricky”.

There are only a few major cases when this happens:

1. MySQL Thread Was Killed by an Administrator or a Utility Such as pt-kill

The manual intervention is likely to be intermittent and, as it is a one-time thing in certain situations (e.g., a bad long-running query), probably would be known to a DBA. Pt-kill might be less noticeable, as it is often left running as a workaround to prevent those bad long queries from taxing system resources. Checking the system processlist should bring the commands to the surface:

A very convenient way is to use the audit plugin available for Percona Server for MySQL to determine where the kill command came from:

It shows the hostname, user, and time when the connection got killed.

2. Big Data Chunk Transfer

For example, when using BLOB fields to store binary data in a table or there is an INSERT statement containing a lot of rows. It may happen when using the MySQL CLI client (one of the cases being loading an SQL dump), or it can happen within an application when it tries to store the BLOB data (for example, from a file upload).

There is a limit MySQL imposes on the amount of data that can be transmitted per query, and the max_allowed_packet variable defines it.

So, in both cases, we need to determine which table the data is being written to, for instance, grepping the SQL file for INSERT INTO statements and implementing logging on the application end. This way, the statement will be stored along with the error that prevented it from completing. A partial statement can be captured (as BLOBs could be a burden to log), but as long as there is a table name, it is possible to check the table structure and see if it does contain binary data.

Example of an INSERT statement with binary data (truncated):

To allow for a larger query execution, the variable needs to be adjusted:

The variable can be set per session or globally, depending on the use case.

3. The Connection Was Closed by Timeout

It is trivial, but applications can be reusing already-established connections. During the time of inactivity or lower traffic, it is possible some connections will not be used for a while and closed on the MySQL end. It is traced best with the application logging; if there is an event that happened in the evening followed by a period of inactivity and then the error in the morning, it is very likely that MySQL closed the connection.

Wait for 5 seconds:

Usually, the connection is re-established, and the application continues normal operations; yet it is always possible to extend the timeout in MySQL configuration:

The default value for the variable is 28800 seconds (8 hours), which is enough in most cases.

Also, closing connections cleanly from an application end, after a period of inactivity, eliminates this problem.

4. MySQL Server Has Actually Gone Away

This one is probably the worst possible scenario when MySQL crashes on a query or due to some other reason, e.g., the OOM killer killed the process. However, it can be caused by a clean restart, too.

In this case, one should check MySQL uptime and the logs, MySQL error log, and syslog. Those should indicate whether the server restart occurred and if there was an error leading to the restart.

In case the server did crash, it is time to find the actual cause. Check the bug tracker, as the issue might have been reported and possibly fixed already; upgrade MySQL if needed. In case it was a clean restart, check if auto-updates are enabled or if someone else restarted the service interactively (yes, lack of communication is a thing too).