Битрикс mysql query error 2006 mysql server has gone away 400

There are several causes for this error.

MySQL/MariaDB related:

• wait_timeout — Time in seconds that the server waits for a connection to become active before closing it.
• interactive_timeout — Time in seconds that the server waits for an interactive connection.
• max_allowed_packet — Maximum size in bytes of a packet or a generated/intermediate string. Set as large as the largest BLOB, in multiples of 1024.

Example of my.cnf:

[mysqld]
# 8 hours
wait_timeout = 28800
# 8 hours
interactive_timeout = 28800
max_allowed_packet = 256M

Server related:

• Your server has full memory — check info about RAM with free -h

Framework related:

• Check settings of your framework. Django for example use CONN_MAX_AGE (see docs)

How to debug it:

• Check values of MySQL/MariaDB variables.
  • with sql: SHOW VARIABLES LIKE '%time%';
  • command line: mysqladmin variables
• Turn on verbosity for errors:
  • MariaDB: log_warnings = 4
  • MySQL: log_error_verbosity = 3
• Check docs for more info about the error

answered Feb 21, 2019 at 9:48

Error: 2006 (CR_SERVER_GONE_ERROR)

Message: MySQL server has gone away

Generally you can retry connecting and then doing the query again to solve this problem — try like 3-4 times before completely giving up.

I’ll assuming you are using PDO. If so then you would catch the PDO Exception, increment a counter and then try again if the counter is under a threshold.

If you have a query that is causing a timeout you can set this variable by executing:

SET @@GLOBAL.wait_timeout=300;
SET @@LOCAL.wait_timeout=300;  -- OR current session only

Where 300 is the number of seconds you think the maximum time the query could take.

Further information on how to deal with Mysql connection issues.

EDIT: Two other settings you may want to also use is net_write_timeout and net_read_timeout.

answered Oct 29, 2011 at 23:22

It was RAM problem for me.

I was having the same problem even on a server with 12 CPU cores and 32 GB RAM. I researched more and tried to free up RAM. Here is the command I used on Ubuntu 14.04 to free up RAM:

sync && echo 3 | sudo tee /proc/sys/vm/drop_caches

And, it fixed everything. I have set it under cron to run every hour.

crontab -e

0 * * * * bash /root/ram.sh;

And, you can use this command to check how much free RAM available:

free -h

And, you will get something like this:

             total       used       free     shared    buffers     cached
Mem:           31G        12G        18G        59M       1.9G       973M
-/+ buffers/cache:       9.9G        21G
Swap:         8.0G       368M       7.6G

answered Mar 26, 2017 at 5:38

In my case it was low value of open_files_limit variable, which blocked the access of mysqld to data files.

I checked it with :

mysql> SHOW VARIABLES LIKE 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1185  |
+------------------+-------+
1 row in set (0.00 sec)

After I changed the variable to big value, our server was alive again :

[mysqld]
open_files_limit = 100000

answered Sep 8, 2016 at 14:31

This generally indicates MySQL server connectivity issues or timeouts.
Can generally be solved by changing wait_timeout and max_allowed_packet in my.cnf or similar.

I would suggest these values:

wait_timeout = 28800

max_allowed_packet = 8M

answered Jun 29, 2018 at 14:35

There is an easier way if you are using XAMPP.
Open the XAMPP control panel, and click on the config button in mysql section.

Now click on the my.ini and it will open in the editor. Update the max_allowed_packet to your required size.

Then restart the mysql service. Click on stop on the Mysql service click start again. Wait for a few minutes.

Then try to run your Mysql query again. Hope it will work.

answered May 5, 2019 at 5:44

It’s always a good idea to check the logs of the Mysql server, for the reason why it went away.

It will tell you.

answered Jun 30, 2018 at 18:06

MAMP 5.3, you will not find my.cnf and adding them does not work as that max_allowed_packet is stored in variables.

One solution can be:

1. Go to http://localhost/phpmyadmin
2. Go to SQL tab
3. Run SHOW VARIABLES and check the values, if it is small then run with big values

4. Run the following query, it set max_allowed_packet to 7gb:

   set global max_allowed_packet=268435456;

For some, you may need to increase the following values as well:

set global wait_timeout = 600;
set innodb_log_file_size =268435456;

answered May 9, 2019 at 20:47

This might be a problem of your .sql file size.

If you are using xampp. Go to the xampp control panel -> Click MySql config -> Open my.ini.

Increase the packet size.

max_allowed_packet = 2M -> 10M

answered Jan 10, 2018 at 11:43

The unlikely scenario is you have a firewall between the client and the server that forces TCP reset into the connection.

I had that issue, and I found our corporate F5 firewall was configured to terminate inactive sessions that are idle for more than 5 mins.

Once again, this is the unlikely scenario.

answered May 23, 2017 at 18:36

I found the solution to «#2006 — MySQL server has gone away» this error.
Solution is just you have to check two files

1. config.inc.php
2. config.sample.inc.php

Path of these files in windows is

C:wamp64appsphpmyadmin4.6.4

In these two files the value of this:

$cfg['Servers'][$i]['host']must be 'localhost' .

In my case it was:

$cfg['Servers'][$i]['host'] = '127.0.0.1';

change it to:

"$cfg['Servers'][$i]['host']" = 'localhost';

Make sure in both:

1. config.inc.php
2. config.sample.inc.php files it must be ‘localhost’.

And last set:

$cfg['Servers'][$i]['AllowNoPassword'] = true;

Then restart Wampserver.

To change phpmyadmin user name and password

You can directly change the user name and password of phpmyadmin through config.inc.php file

These two lines

$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';

Here you can give new user name and password.
After changes save the file and restart WAMP server.

answered Jun 10, 2017 at 8:18

answered Jun 16, 2017 at 15:58

I also encountered this error. But even with the increased max_allowed_packet or any increase of value in the my.cnf, the error still persists.

What I did is I troubleshoot my database:

• I checked the tables where the error persists
• Then I checked each row
• There are rows that are okay to fetch and there are rows where the error only shows up
• It seems that there are value in these rows that is causing this error
• But even by selecting only the primary column, the error still shows up (SELECT primary_id FROM table)

The solution that I thought of is to reimport the database. Good thing is I have a backup of this database. But I only dropped the problematic table, then import my backup of this table. That solved my problem.

My takeaway of this problem:

• Always have a backup of your database. Either manually or thru CRON job
• I noticed that there are special characters in the affected rows. So when I recovered the table, I immediately changed the collation of this table from latin1_swedish_ci to utf8_general_ci
• My database was working fine before then my system suddenly encountered this problem. Maybe it also has something to do with the upgrade of the MySQL database by our hosting provider. So frequent backup is a must!

answered May 3, 2021 at 1:49

Just in case this helps anyone:

I got this error when I opened and closed connections in a function which would be called from several parts of the application.
We got too many connections so we thought it might be a good idea to reuse the existing connection or throw it away and make a new one like so:

public static function getConnection($database, $host, $user, $password){
    if (!self::$instance) {
        return self::newConnection($database, $host, $user, $password);
    } elseif ($database . $host . $user != self::$connectionDetails) {
        self::$instance->query('KILL CONNECTION_ID()');
        self::$instance = null;
        return self::newConnection($database, $host, $user, $password);
    }
    return self::$instance;
}

Well turns out we’ve been a little too thorough with the killing and so the processes doing important things on the old connection could never finish their business.
So we dropped these lines

self::$instance->query('KILL CONNECTION_ID()');
self::$instance = null;

and as the hardware and setup of the machine allows it we increased the number of allowed connections on the server by adding

max_connections = 500

to our configuration file. This fixed our problem for now and we learned something about killing mysql connections.

answered Jun 26, 2018 at 12:10

This error happens basically for two reasons.

1. You have a too low RAM.
2. The database connection is closed when you try to connect.

You can try this code below.

# Simplification to execute an SQL string of getting a data from the database
def get(self, sql_string, sql_vars=(), debug_sql=0):
    try:            
        self.cursor.execute(sql_string, sql_vars)
        return self.cursor.fetchall()
    except (AttributeError, MySQLdb.OperationalError):
        self.__init__()
        self.cursor.execute(sql_string, sql_vars)
        return self.cursor.fetchall()

It mitigates the error whatever the reason behind it, especially for the second reason.

If it’s caused by low RAM, you either have to raise database connection efficiency from the code, from the database configuration, or simply raise the RAM.

answered Sep 21, 2018 at 10:54

For me it helped to fix one’s innodb table’s corrupted index tree. I localized such a table by this command

mysqlcheck -uroot --databases databaseName 

result

mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ...

as followed I was able to see only from the mysqld logs /var/log/mysqld.log which table was causing troubles.

FIL_PAGE_PREV links 2021-08-25T14:05:22.182328Z 2 [ERROR] InnoDB: Corruption of an index tree: table `database`.`tableName` index `PRIMARY`, father ptr page no 1592, child page no 1234'

The mysqlcheck command did not fix it, but helped to unveil it.
Ultimately I fixed it as followed by a regular mysql command from a mysql cli

OPTIMIZE table theCorruptedTableNameMentionedAboveInTheMysqld.log

answered Aug 25, 2021 at 16:04

Основатель и администратор сайта losst.ru, увлекаюсь открытым программным обеспечением и операционной системой Linux. В качестве основной ОС сейчас использую Ubuntu. Кроме Linux, интересуюсь всем, что связано с информационными технологиями и современной наукой.

Две наиболее распространенные причины получения ошибки MySQL server has gone away (error 2006) это..

1. Сервер закрыл соединение по таймауту.
   
   Исправить можно так: проверить чтобы значение переменной wait_timeout в конфиг файле MySql — my.cnf было достаточным для выполнения скрипта.
   
   На Debian: нужно выполнить

   sudo nano /etc/mysql/my.cnf

   и установить wait_timeout = 600 ( значение задается в секундах, если ошибка не пропадет поиграйтесь с этим значением, чтобы найти оптимальное), после этого нужно рестартануть MySQL:

   sudo /etc/init.d/mysql restart

   Я не проверял, но значение по-умолчанию для wait_timeout можно установить вплоть до 28800 секунд (8 часов).

2. Сервер сбрасывает (отклоняет) неправильные или слишком большие пакеты. Если mysqld получает пакет данных, который слишком большой или не корректный, он думает что что-то пошло не так или с клиентом случилась какая-то беда и закрывает соединение. Часто такая ошибка возникает при импорте дампов содержащих большие тексты.
   

   Так же такое происходит, когда у Вас слишком большой запрос. Например, вы хотите в поле типа longtext записать какую-нибудь книгу, в которой текста на 20 мб. Либо хотите сохранить большой файл (например картинку) в поле с типом blob. В итоге у вас получается запрос по типу

   UPDATE books SET text=«сууупер..длинный..текст» WHERE id=1

   Если это Ваш случай, то подумайте действительно ли Вам нужно сохранять такой текст/файл  в базу, обычная практика в таких случаях, сохранить его в файл на диск, а в базу сохранить имя этого файла. Типа того

   file_put_content(‘book.txt’, ‘сууупер..длинный..текст’); ... UPDATE books SET filename=«book.txt» WHERE id=1

   
   Исправить можно так: вы можете увеличить максимальный размер пакета увеличив значение max_allowed_packet в файле my.cnf.
   
   На Debian нужно выполнить:

   sudo nano /etc/mysql/my.cnf

   и установить max_allowed_packet = 64M (если ошибка не пропадет поиграйтесь с этим значением, чтобы найти оптимальное), после этого нужно рестартануть MySQL

   sudo /etc/init.d/mysql restart

Про max_allowed_packet я так же писал здесь: ERROR 2006 (HY000) — MySQL server has gone away

Если Вы получаете ошибку MySQL server has gone away (error 2006) при использовании драйвера MySQL ODBC – можете попробовать это решение.

Оригинал исходной статьи (на англ): How to fix “MySQL server has gone away” (error 2006)

Похожие статьи