Ошибка sql 2006 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;
}

Ошибка 2006 под названием MySQL Sever has gone away означает отказ сервера в соединении даже при условии, что он запущен. Известно всего три причины, почему ошибка появляется. Первая причина – сервер перегружен. Время ожидания истекло. Вторая причина – клиент отправил слишком больной пакет. Третья – сервер не был до конца проинициализирован. Дальше подробно рассмотрим, по каким причинам появляется ошибка и как с ней бороться.

Как исправить ошибку

Обычно ошибка появляется при попытке подключиться к базе данных при помощи PHP, консольного клиента, а также в случае использования PhpMyAdmin:

Давайте дальше рассмотрим каждую ситуацию в отдельности.

Истекло время ожидания

Как было сказано в начале статьи, одна из возможных причин – истечение времени ожидания. Может быть так, что сервер был перегружен и не справляется с нагрузкой – обработкой всех соединений. Чтобы понять, насколько долго выполняются серверные запросы, можно воспользоваться любым консольным клиентом и подключиться к серверу. Если вам удастся это сделать, выполните любой запрос. Если на обработку запросов уходит слишком много времени, оптимизировать  MySQL можно при помощи специального скрипта MySQLTuner. Обычно увеличивается размер пула движка InnoDB путем установки параметра innodb_buffer_pool_size. Оптимальное значение определяется при помои приведенного выше скрипта.

Если это 800 мегабайт (может быть и другой размер), прописываем:

$ sudo vi /etc/mysql/my.cnf

innodb_buffer_pool_size=800M

Существует и другой способ решения проблемы. Для этого увеличивают время ответа от сервера. Чтобы выполнить эту задачу, необходимо изменить параметр wait_timeout. Это время в секундах, на протяжении которого надо ждать ответа от сервера.

Например:

wait_timeout=500

Внося изменения, не забываем дальше перезагрузить сервер:

$ sudo systemctl restart mysql

или:

$ sudo systemctl restart mariadb

Слишком большой пакет

Когда клиент пользователя создает слишком большое количество пакетов, сервер выдаст именно эту ошибку. Доступный размер пакета (максимальное значение) можно увеличить с помощью параметра max_allowed_packet.

Например:

$ sudo vi /etc/mysql/my.cnf

max_allowed_packet=128M

Отдельно обратите внимание на клиент, ведь если он посылает много запросов, то вы явно что-то делает не так. Как минимум не стоит генерировать запросы к MySQL с помощью циклов for.

Сервер неверно проинициализирован

Если вы решите развернуть MySQL или MariaDB в Docker, то будьте готовы столкнуться с подобной ошибкой. Первоначальная инициализация контейнера требует чуть больше свободного времени. Если не дать контейнеру завершить инициализацию, сперва остановив его и запустив, то база данных будет всегда возвращать такую ошибку. Решение – нужно полностью удалить данные контейнера с базой данных.

Делается это так:

$ docker-compose down

или:

$ docker rm mysql-container

Дальше надо удалить хранилище (volume) с некорректно проинициализированной базой. Но в начале просмотрите список всех хранилищ:

$ docker volume ls

После удаляем:

$ docker volume rm имя_хранилища

Теперь можете запустить инициализацию приложения, только дождитесь, пока сервер баз данных сообщит, что он готов, и вы сможете к нему подключиться.

При работе с базами данных могут встречаться ошибки. Ниже перечислены частые ошибки и меры по их диагностике и устранению.

• Недоступность базы данных
• Повреждены таблицы БД (Table is marked as crashed)
• Ошибка 2006: MySQL server has gone away
• Ошибка 1040: Too many connections
• Ошибка 1292: Incorrect date value

Недоступность базы данных

Необходимо подключиться к серверу по SSH и выполнить следующие проверки:

1. Проверить, запущена ли служба MySQL:

service mysql status

Пример вывода для запущенной службы:

Если в выводе отсутствует слово running, служба не запущена. В этом случае необходимо попытаться ее запустить:

service mysql start

После этого надо проверить работу сайта и сделать следующую проверку, если ошибка сохраняется.

2. Проверить состояние дискового пространства.

Просмотреть общий и занятый объем на диске командой:

df -h

Доступное пространство должно быть на основном разделе. Если свободное пространство закончилось, необходимо освободить место или перейти на тариф выше. Для работы с дисковым пространством можно использовать утилиты ncdu или du.

Если на диске достаточно свободного места, но ошибка сохраняется, надо проверить состояние inodes.

Если не удается решить ошибку самостоятельно, то нужно обратиться в техническую поддержку.

Повреждены таблицы БД (Table is marked as crashed)

При возникновении ошибок вида «Warning: Table … is marked as crashed» необходимо выполнить восстановление таблиц.

Если на сервере установлен phpMyAdmin, можно выполнить восстановление с его помощью. Для этого необходимо:

1. перейти в интерфейс PMA,
2. выбрать нужную базу данных в меню слева,
3. отметить в списке таблицы, которые нужно восстановить — то есть таблицы, имена которых фигурируют в ошибках,
4. в самом низу страницы нажать на выпадающее меню «С отмеченными» и выбрать вариант «Восстановить».

Без phpMyAdmin можно выполнить необходимые действия при подключении по SSH. Для восстановления одной таблицы нужно выполнить команду:

mysqlcheck -r имя_базы имя_таблицы -uroot -p

Для восстановления всех таблиц в базе используется команда:

mysqlcheck -r имя_базы -uroot -p

Также можно выполнить проверку всех таблиц в базе с помощью команды:

mysqlcheck -r -A -uroot -p

Ошибка 2006: MySQL server has gone away

Ошибка MySQL server has gone away означает, что сервер закрыл соединение. Это происходит, как правило, в двух случаях: превышение таймаута ожидания или получение сервером слишком большого пакета.

В обоих случаях для устранения ошибки потребуется внести правки в конфигурационный файл MySQL. Это делается при подключении к серверу по SSH или с помощью веб-консоли в панели управления.

Конфигурационный файл может располагаться по различным путям, например:

/etc/my.cnf
/etc/mysql/my.cnf
/etc/mysql/mysql.conf.d/mysqld.cnf

Чтобы определить, в какой файл необходимо вносить изменения, можно использовать команду вида:

grep -Rl ‘имя_параметра’ /etc/*

Например:
grep -Rl ‘wait_timeout’ /etc/*

или:
grep -Rl ‘max_allowed_packet’ /etc/*

С ее помощью можно выяснить, в каких файлах прописан нужный параметр, и изменить в них его значение.

Таймаут

Чтобы увеличить таймаут ожидания, необходимо скорректировать значение параметра wait_timeout

Нужно открыть конфигурационный файл с помощью редактора, обязательно указав корректный путь к файлу:

nano /etc/mysql/my.cnf

Далее нужно изменить значение параметра wait_timeout на более высокое. Значение указывается в секундах: чтобы увеличить время ожидания до 10 минут, необходимо указать значение 600:

wait_timeout = 600

После перезапустить службу MySQL:

service mysql restart

Размер пакетов

Скорректировать максимально допустимый размер пакетов можно увеличением параметра max_allowed_packet.

Нужно открыть конфигурационный файл с помощью редактора, обязательно указав корректный путь к файлу:

nano /etc/mysql/my.cnf

Дале нужно изменить значение параметра max_allowed_packet на более высокое (значение указывается в мегабайтах):

max_allowed_packet = 64M

После перезапустить службу MySQL:

service mysql restart

Ошибка 1040: Too many connections

Ошибка «Too many connections» означает, что исчерпан лимит подключений к базе данных. Ошибка связана с медленными запросами, которые выполняются слишком долго (в этом случае требуется оптимизация кода) либо в числе одновременных подключений. В этом случае можно попробовать решить проблему увеличением лимита подключений (параметр max_connections) в конфигурационном файле MySQL.

В пункте выше было описано, как определить расположение файла my.cnf.

Следует открыть конфигурационный файл с помощью редактора, обязательно указав корректный путь к файлу:

nano /etc/mysql/my.cnf

И заменить значение параметра на более высокое, например:

max_connections = 200

После перезапустить службу MySQL:

service mysql restart

Ошибка 1292: Incorrect date value

При попытке добавить данные в таблицу MySQL без указания даты может выдаваться ошибка:

ERROR 1292 (22007): Incorrect date value: ‘0000-00-00’ for column ‘columnname’ at row 1

Из-за этой ошибки может нарушаться работа импорта в 1С.

Для исправления ошибки необходимо:

1.Открыть файл /etc/mysql/my.cnf:

nano /etc/mysql/my.cnf

2. В строке, начинающейся с sql-mode=, удалить следующие значения:

NO_ZERO_IN_DATE

NO_ZERO_DATE

STRICT_ALL_TABLES

3. Выполнить перезагрузку mysql-сервера:

sudo service mysql restart

Примечание:

Если строка вида sql-mode= отсутствует, необходимо:

1. В файл /etc/mysql/my.cnf после параметра [mysqld] добавить строку:

sql-mode=»ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION»

2. Выполнить перезагрузку mysql-сервера:

sudo service mysql restart