Sphinx select error 2006 mysql server has gone away

I'm running a server at my office to process some files and report the results to a remote MySQL server. The files processing takes some time and the process dies halfway through with the following

I have encountered this a number of times and I’ve normally found the answer to be a very low default setting of max_allowed_packet.

Raising it in /etc/my.cnf (under [mysqld]) to 8 or 16M usually fixes it. (The default in MySql 5.7 is 4194304, which is 4MB.)

[mysqld]
max_allowed_packet=16M

Note: Just create the line if it does not exist

Note: This can be set on your server as it’s running.

Note: On Windows you may need to save your my.ini or my.cnf file with ANSI not UTF-8 encoding.

Use set global max_allowed_packet=104857600. This sets it to 100MB.

Jakob Gerstmayer's user avatar

answered Feb 28, 2012 at 9:48

George's user avatar

GeorgeGeorge

4,8252 gold badges15 silver badges21 bronze badges

15

I had the same problem but changeing max_allowed_packet in the my.ini/my.cnf file under [mysqld] made the trick.

add a line

max_allowed_packet=500M

now restart the MySQL service once you are done.

answered Oct 17, 2013 at 11:51

Sathish D's user avatar

Sathish DSathish D

4,83629 silver badges44 bronze badges

3

I used following command in MySQL command-line to restore a MySQL database which size more than 7GB, and it works.

set global max_allowed_packet=268435456;

Michał Perłakowski's user avatar

answered Feb 24, 2016 at 10:31

Geshan Ravindu's user avatar

3

It may be easier to check if the connection exists and re-establish it if needed.

See PHP:mysqli_ping for info on that.

Community's user avatar

answered Oct 29, 2011 at 23:15

Niet the Dark Absol's user avatar

3

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

jozo's user avatar

jozojozo

3,8221 gold badge24 silver badges29 bronze badges

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

Yzmir Ramirez's user avatar

In MAMP (non-pro version) I added

--max_allowed_packet=268435456

to ...MAMPbinstartMysql.sh

Credits and more details here

icedwater's user avatar

icedwater

4,6423 gold badges34 silver badges48 bronze badges

answered Feb 29, 2012 at 17:00

uwe's user avatar

uweuwe

3,87011 gold badges36 silver badges49 bronze badges

0

If you are using xampp server :

Go to xampp -> mysql -> bin -> my.ini

Change below parameter :

max_allowed_packet = 500M

innodb_log_file_size = 128M

This helped me a lot :)

answered Aug 3, 2019 at 8:32

Archana Kamath's user avatar

1

I was getting this same error on my DigitalOcean Ubuntu server.

I tried changing the max_allowed_packet and the wait_timeout settings but neither of them fixed it.

It turns out that my server was out of RAM. I added a 1GB swap file and that fixed my problem.

Check your memory with free -h to see if that’s what’s causing it.

answered Sep 19, 2016 at 23:45

Pikamander2's user avatar

Pikamander2Pikamander2

6,8643 gold badges46 silver badges66 bronze badges

1

On windows those guys using xampp should use this path xampp/mysql/bin/my.ini and change max_allowed_packet(under section[mysqld])to your choice size.
e.g

max_allowed_packet=8M

Again on php.ini(xampp/php/php.ini) change upload_max_filesize the choice size.
e.g

upload_max_filesize=8M

Gave me a headache for sometime till i discovered this. Hope it helps.

answered Oct 16, 2015 at 16:02

Kenneth mwangi's user avatar

3

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

Rehmat's user avatar

RehmatRehmat

2,0332 gold badges22 silver badges27 bronze badges

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

Fedir RYKHTIK's user avatar

Fedir RYKHTIKFedir RYKHTIK

9,7446 gold badges56 silver badges67 bronze badges

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

Memo's user avatar

MemoMemo

1421 gold badge2 silver badges10 bronze badges

If you are using the 64Bit WAMPSERVER, please search for multiple occurrences of max_allowed_packet because WAMP uses the value set under [wampmysqld64] and not the value set under [mysqldump], which for me was the issue, I was updating the wrong one. Set this to something like max_allowed_packet = 64M.

Hopefully this helps other Wampserver-users out there.

answered Mar 14, 2018 at 14:54

Enomatix24's user avatar

Enomatix24Enomatix24

1561 silver badge7 bronze badges

0

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

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

enter image description here

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

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

answered May 5, 2019 at 5:44

Hriju's user avatar

HrijuHriju

7201 gold badge17 silver badges27 bronze badges

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

Alex's user avatar

AlexAlex

31.3k14 gold badges100 silver badges166 bronze badges

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

Rupak Nepali's user avatar

Rupak NepaliRupak Nepali

7191 gold badge6 silver badges13 bronze badges

For Vagrant Box, make sure you allocate enough memory to the box

config.vm.provider "virtualbox" do |vb|
  vb.memory = "4096"
end

answered Dec 13, 2015 at 8:39

Shadoweb's user avatar

ShadowebShadoweb

5,5841 gold badge40 silver badges53 bronze badges

0

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

Nikunj Dhimar's user avatar

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

Ahmed's user avatar

AhmedAhmed

2,7641 gold badge24 silver badges39 bronze badges

uncomment the ligne below in your my.ini/my.cnf, this will split your large file into smaller portion

# binary logging format - mixed recommended
# binlog_format=mixed

TO

# binary logging format - mixed recommended
binlog_format=mixed

answered Sep 25, 2015 at 14:30

Nico's user avatar

1

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

um life's user avatar

I got Error 2006 message in different MySQL clients software on my Ubuntu desktop. It turned out that my JDBC driver version was too old.

answered Jun 16, 2017 at 15:58

Bo Guo's user avatar

I had the same problem in docker adding below setting in docker-compose.yml:

db:
    image: mysql:8.0
    command: --wait_timeout=800 --max_allowed_packet=256M --character-set-server=utf8 --collation-server=utf8_general_ci --default-authentication-plugin=mysql_native_password
    volumes:
      - ./docker/mysql/data:/var/lib/mysql
      - ./docker/mysql/dump:/docker-entrypoint-initdb.d
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}

answered Nov 11, 2020 at 17:14

Dmitry's user avatar

DmitryDmitry

3,7703 gold badges23 silver badges40 bronze badges

1

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

Logan Wayne's user avatar

Logan WayneLogan Wayne

5,99116 gold badges32 silver badges48 bronze badges

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.

masud_moni's user avatar

answered May 20, 2020 at 7:10

Max's user avatar

MaxMax

2,4811 gold badge22 silver badges27 bronze badges

For users using XAMPP, there are 2 max_allowed_packet parameters in C:xamppmysqlbinmy.ini.

answered Jun 26, 2018 at 12:10

Subhash's user avatar

SubhashSubhash

1581 silver badge9 bronze badges

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

Aminah Nuraini's user avatar

Aminah NurainiAminah Nuraini

17.5k8 gold badges87 silver badges106 bronze badges

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

FantomX1's user avatar

FantomX1FantomX1

1,4672 gold badges15 silver badges21 bronze badges

I have encountered this a number of times and I’ve normally found the answer to be a very low default setting of max_allowed_packet.

Raising it in /etc/my.cnf (under [mysqld]) to 8 or 16M usually fixes it. (The default in MySql 5.7 is 4194304, which is 4MB.)

[mysqld]
max_allowed_packet=16M

Note: Just create the line if it does not exist

Note: This can be set on your server as it’s running.

Note: On Windows you may need to save your my.ini or my.cnf file with ANSI not UTF-8 encoding.

Use set global max_allowed_packet=104857600. This sets it to 100MB.

Jakob Gerstmayer's user avatar

answered Feb 28, 2012 at 9:48

George's user avatar

GeorgeGeorge

4,8252 gold badges15 silver badges21 bronze badges

15

I had the same problem but changeing max_allowed_packet in the my.ini/my.cnf file under [mysqld] made the trick.

add a line

max_allowed_packet=500M

now restart the MySQL service once you are done.

answered Oct 17, 2013 at 11:51

Sathish D's user avatar

Sathish DSathish D

4,83629 silver badges44 bronze badges

3

I used following command in MySQL command-line to restore a MySQL database which size more than 7GB, and it works.

set global max_allowed_packet=268435456;

Michał Perłakowski's user avatar

answered Feb 24, 2016 at 10:31

Geshan Ravindu's user avatar

3

It may be easier to check if the connection exists and re-establish it if needed.

See PHP:mysqli_ping for info on that.

Community's user avatar

answered Oct 29, 2011 at 23:15

Niet the Dark Absol's user avatar

3

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

jozo's user avatar

jozojozo

3,8221 gold badge24 silver badges29 bronze badges

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

Yzmir Ramirez's user avatar

In MAMP (non-pro version) I added

--max_allowed_packet=268435456

to ...MAMPbinstartMysql.sh

Credits and more details here

icedwater's user avatar

icedwater

4,6423 gold badges34 silver badges48 bronze badges

answered Feb 29, 2012 at 17:00

uwe's user avatar

uweuwe

3,87011 gold badges36 silver badges49 bronze badges

0

If you are using xampp server :

Go to xampp -> mysql -> bin -> my.ini

Change below parameter :

max_allowed_packet = 500M

innodb_log_file_size = 128M

This helped me a lot :)

answered Aug 3, 2019 at 8:32

Archana Kamath's user avatar

1

I was getting this same error on my DigitalOcean Ubuntu server.

I tried changing the max_allowed_packet and the wait_timeout settings but neither of them fixed it.

It turns out that my server was out of RAM. I added a 1GB swap file and that fixed my problem.

Check your memory with free -h to see if that’s what’s causing it.

answered Sep 19, 2016 at 23:45

Pikamander2's user avatar

Pikamander2Pikamander2

6,8643 gold badges46 silver badges66 bronze badges

1

On windows those guys using xampp should use this path xampp/mysql/bin/my.ini and change max_allowed_packet(under section[mysqld])to your choice size.
e.g

max_allowed_packet=8M

Again on php.ini(xampp/php/php.ini) change upload_max_filesize the choice size.
e.g

upload_max_filesize=8M

Gave me a headache for sometime till i discovered this. Hope it helps.

answered Oct 16, 2015 at 16:02

Kenneth mwangi's user avatar

3

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

Rehmat's user avatar

RehmatRehmat

2,0332 gold badges22 silver badges27 bronze badges

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

Fedir RYKHTIK's user avatar

Fedir RYKHTIKFedir RYKHTIK

9,7446 gold badges56 silver badges67 bronze badges

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

Memo's user avatar

MemoMemo

1421 gold badge2 silver badges10 bronze badges

If you are using the 64Bit WAMPSERVER, please search for multiple occurrences of max_allowed_packet because WAMP uses the value set under [wampmysqld64] and not the value set under [mysqldump], which for me was the issue, I was updating the wrong one. Set this to something like max_allowed_packet = 64M.

Hopefully this helps other Wampserver-users out there.

answered Mar 14, 2018 at 14:54

Enomatix24's user avatar

Enomatix24Enomatix24

1561 silver badge7 bronze badges

0

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

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

enter image description here

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

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

answered May 5, 2019 at 5:44

Hriju's user avatar

HrijuHriju

7201 gold badge17 silver badges27 bronze badges

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

Alex's user avatar

AlexAlex

31.3k14 gold badges100 silver badges166 bronze badges

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

Rupak Nepali's user avatar

Rupak NepaliRupak Nepali

7191 gold badge6 silver badges13 bronze badges

For Vagrant Box, make sure you allocate enough memory to the box

config.vm.provider "virtualbox" do |vb|
  vb.memory = "4096"
end

answered Dec 13, 2015 at 8:39

Shadoweb's user avatar

ShadowebShadoweb

5,5841 gold badge40 silver badges53 bronze badges

0

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

Nikunj Dhimar's user avatar

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

Ahmed's user avatar

AhmedAhmed

2,7641 gold badge24 silver badges39 bronze badges

uncomment the ligne below in your my.ini/my.cnf, this will split your large file into smaller portion

# binary logging format - mixed recommended
# binlog_format=mixed

TO

# binary logging format - mixed recommended
binlog_format=mixed

answered Sep 25, 2015 at 14:30

Nico's user avatar

1

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

um life's user avatar

I got Error 2006 message in different MySQL clients software on my Ubuntu desktop. It turned out that my JDBC driver version was too old.

answered Jun 16, 2017 at 15:58

Bo Guo's user avatar

I had the same problem in docker adding below setting in docker-compose.yml:

db:
    image: mysql:8.0
    command: --wait_timeout=800 --max_allowed_packet=256M --character-set-server=utf8 --collation-server=utf8_general_ci --default-authentication-plugin=mysql_native_password
    volumes:
      - ./docker/mysql/data:/var/lib/mysql
      - ./docker/mysql/dump:/docker-entrypoint-initdb.d
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}

answered Nov 11, 2020 at 17:14

Dmitry's user avatar

DmitryDmitry

3,7703 gold badges23 silver badges40 bronze badges

1

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

Logan Wayne's user avatar

Logan WayneLogan Wayne

5,99116 gold badges32 silver badges48 bronze badges

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.

masud_moni's user avatar

answered May 20, 2020 at 7:10

Max's user avatar

MaxMax

2,4811 gold badge22 silver badges27 bronze badges

For users using XAMPP, there are 2 max_allowed_packet parameters in C:xamppmysqlbinmy.ini.

answered Jun 26, 2018 at 12:10

Subhash's user avatar

SubhashSubhash

1581 silver badge9 bronze badges

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

Aminah Nuraini's user avatar

Aminah NurainiAminah Nuraini

17.5k8 gold badges87 silver badges106 bronze badges

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

FantomX1's user avatar

FantomX1FantomX1

1,4672 gold badges15 silver badges21 bronze badges

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

В этой небольшой статье мы рассмотрим более подробно, почему возникает ошибка 2006: MySQL server has gone away, а также — как её исправить.

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

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

Как я уже писал выше, одной из причин может быть таймаут ожидания соединения. Возможно, сервер баз данных перегружен и не успевает обрабатывать все соединения. Вы можете подключиться к серверу с помощью консольного клиента, если вам это удастся, и попытаться выполнить какой-либо запрос, чтобы понять, действительно ли запросы выполняются слишком долго. Если это так, можно оптимизировать производительность MySQL с помощью скрипта MySQLTuner.

В большинстве случаев надо увеличить размер пула движка InnoDB с помощью параметра innodb_buffer_pool_size. Какое значение лучше поставить, можно узнать с помощью указанного выше скрипта. Например, 800 мегабайт:

sudo vi /etc/mysql/my.cnf

innodb_buffer_pool_size=800M

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

wait_timeout=600

После любых изменений не забудьте перезапустить MySQL сервер:

sudo systemctl restart mysql

или:

sudo systemctl restart mariadb

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

Если ваш клиент MySQL создаёт слишком большие пакеты с запросами к серверу, это тоже может стать причиной такой ошибки. Максимально доступный размер пакета можно увеличить с помощью параметра max_allowed_packet. Например:

sudo vi /etc/mysql/my.cnf

max_allowed_packet=128M

Обратите внимание, что если вы из своей программы отправляете большие пакеты, то, скорее всего, вы делаете что-то не так. Не надо генерировать запросы к MySQL с помощью циклов for. SQL — это отдельный язык программирования, который многое может сделать сам, без необходимости писать очень длинные запросы.

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

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

Вам нужно полностью удалить данные контейнера с базой данных. Например, с помощью docker-compose:

docker-compose down

или вручную:

docker rm mysql-container

Здесь mysql-container — это имя контейнера с базой данных. А затем надо удалить хранилище (volume) с некорректно проинициализированной базой. Сначала посмотрите список всех хранилищ:

docker volume ls

Затем удалите нужное:

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

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

Выводы

В этой небольшой статье мы рассмотрели, что значит ошибка MySQL Server has gone away, а также как её исправить на сервере или в контейнере Docker. Вы знаете ещё другие причины и решения этой проблемы? Пишите в комментариях!

Creative Commons License

Статья распространяется под лицензией Creative Commons ShareAlike 4.0 при копировании материала ссылка на источник обязательна .

Об авторе

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

mysql

Две наиболее распространенные причины получения ошибки 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)

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

Автор:
| Рейтинг: 5/5 |
Теги:


The MySQL server has gone away error, which means that the MySQL server (mysqld) timed out and closed the connection. By default, MySQL will close connections after eight hours (28800 seconds) if nothing happens. However, in some cases, your web host, DBA, or app developer may have decreased this timeout setting, as discussed below.

MySQL server has gone away, can be a frustrating error to solve. This is partly because, to solve this error, sometimes the solution involves multiple layers, application, or service config changes. This article includes solutions I’ve seen for this MySQL server general error. If you’ve found a solution not listed or linked to on this page, please send me a note or leave a comment.

MySQL server has gone away

MySQL server has gone away error log examples.

Keep in mind that this error can be logged in a few ways, as listed below. In addition, at times, the error is only an indication of a deeper underlying issue. Meaning the error could be due to a problem or bug in your connecting application or remote service. In this case, you need to check ALL related error logs with the same timestamp to determine whether another issue may be to blame. Application Performance Monitoring solutions and PHP Stack trace tools can be of help. With this in mind, here are error log examples of the MySQL server has gone away error:

General error: 2006 MySQL server has gone away
Error Code: 2013. Lost connection to MySQL server during query
Warning: Error while sending QUERY packet
PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

Sponsored: Datadog – View query metrics and explain plans from all of your databases in a single place.
Datadog database metrics
Datadog is a unified monitoring, analytics, and security platform that offers end-to-end monitoring for all of your databases, including MySQL, PostgreSQL, and more! Quickly pinpoint costly, slow queries and troubleshoot performance issues faster with key database metrics and patterns in one place. Easily search, compare, and filter your queries on execution plans to quickly identify areas for performance and cost improvements. Utilize our integrations for MySQL, PostgreSQL, SQL Server, and more to visualize key system performance metrics on an out-of-the-box dashboard alongside query and host-level metrics. Datadog Database Monitoring is closely integrated with the rest of the Datadog platform; further improve your Database performance with other key features such as SLO tracking, monitors & alerts, Security Monitoring, and more!

Cost: Free plan, or starting at $70.

MySQL wait_timeout

The reason for MySQL server has gone away error is often because MySQL’s wait_timeout was exceeded. MySQL wait_timeout is the number of seconds the server waits for activity on a non-interactive connection before closing it. You should make sure the wait_timeout is not set too low. The default for MySQL wait_timeout is 28800 seconds. Often, it gets lowered arbitrarily. That said, the lower you can set wait_timeout without affecting database connections, can be a good sign of MySQL database efficiency. Also, check the variables: net_read_timeoutnet_write_timeout and interactive_timeout. Adjust or add the following lines in my.cnf to meet your requirements:

wait_timeout=90
net_read_timeout=90
net_write_timeout=90
interactive_timeout=300
connect_timeout=90

MySQL connect timeout in PHP config.

Have a look at your php.ini config file. You’ll find MySQL configuration options. Make sure the mysql.connect_timeout setting isn’t set lower than MySQL wait_timeout, discussed above. The PHP option mysql.connect_timeout is not only used for connect timeout. It’s also when waiting for the first response from the MySQL server. Try increasing mysql.connect_timeout to match or exceed your MySQL wait_timeout and make sure that mysql.allow_persistent is on (default = enabled).

mysql.connect_timeout=90
mysql.allow_persistent=1

IMPORTANT: Read first about PHP Persistent Database Connections to understand the benefits and caveats.

Also, adjust PHP’s default_socket_timeout. For example, a PHP script could be running a slow query. Creating a wait that utilizes the default_socket_timeout. Eventually, it quits with the “MySQL server has gone away” error. Before you send hate mail, please read here first. Here’s an excerpt:

“PHP, by default, sets a read timeout of 60s for streams. This is set via php.ini, default_socket_timeout. This default applies to all streams that set no other timeout value. mysqlnd does not set any other value and therefore connections of long running queries can be disconnected after default_socket_timeout seconds resulting in an error message 2006 – MySQL Server has gone away.”

default_socket_timeout=90

To be throughout, also adjust max_execution_time and max_input_time still in php.ini, if necessary. If PHP’s execution time is longer than max_execution_time, then MySQL server might disconnect.

max_execution_time = 90
max_input_time = 90

MySQL max_allowed_packet

max_allowed_packet is the maximum size of one packet. The default size of 4MB helps the MySQL server catch large (possibly incorrect) packets. As of MySQL 8, the default has been increased to 16MB. If mysqld receives a packet that is too large, it assumes that something is wrong and closes the connection. To fix this, you should increase the max_allowed_packet in my.cnf, then restart MySQL. The max for this setting is 1GB. For example:

max_allowed_packet = 512M

MySQL innodb_log_file_size

You may need to increase the innodb_log_file_size MySQL variable in your my.cnf configuration. MySQL’s innodb_log_file_size should be 25% of innodb_buffer_pool_size (if possible, no less than 20%). Remember that the larger this value, the longer it will take to recover from a database crash. (Source: Phpmyadmin Advisor)

This means for example: if your buffer pool size is set to innodb_buffer_pool_size=16G and your innodb_log_files_in_group setting is still set to the recommended default of 2 files (innodb_log_files_in_group=2), then your innodb_log_file_size should be set to 2G. This will create two (2) log files at 2GB each, which equals 25% of innodb_buffer_pool_size=16G.

WARNING: You must stop MySQL server in order to change innodb_log_file_size or innodb_log_files_in_group. If you don’t, you risk catastrophe! (Read: MySQL Log Redo instructions.)

Other causes of MySQL server has gone away

Remote MySQL connections

Remember earlier I mentioned that the error, at times, is only an indication of a deeper underlying issue. For example, remote MySQL connections to 3rd party services. Using a 3rd party payment processing plugin for osCommerce, Magento, etc.

MySQL database charset and collation

Changing default database charset to latin1 and default collation to latin1_general_ci seemed to have solved MySQL server has gone away for some.

Exceeding MySQL max_connections setting

Max_connections set the maximum permitted number of simultaneous client connections. Be careful with this setting!! Exhaustion of memory and other resources can occur when set too large and scheduling overhead also increases. As a guide, set max_connections to approximately double the previous number of maximum simultaneous client connections. E.g., if after a month of uptime, the maximum simultaneous client connections were 114, then set to max_connections=250. Before you go crazy with this setting, please read: How MySQL Handles Client Connections.

Still unresolved? See MySQL’s help page.

Oracle has put together a nice self-help page for MySQL server has gone away errors. On that page, they also suggest that you make sure MySQL didn’t stop/restart during the query. Excerpt:

“You can check whether the MySQL server died and restarted by executing mysqladmin version and examining the server’s uptime. If the client connection was broken because mysqld crashed and restarted, you should concentrate on finding the reason for the crash.”

# mysqladmin version
mysqladmin Ver 9.1 Distrib 10.1.40-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version 10.1.40-MariaDB
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 20 days 11 hours 49 min 40 sec

Threads: 5 Questions: 1030744326 Slow queries: 3343 Opens: 3585 Flush tables: 1 Open tables: 2564 Queries per second avg: 582.150
# mysqladmin status
Uptime: 1770590 Threads: 4 Questions: 1030752268 Slow queries: 3343 Opens: 3585 Flush tables: 1 Open tables: 2564 Queries per second avg: 582.151

I hope this helps!


Related articles:

  • MySQL Performance Tuning: Tips, Scripts and Tools
  • Tuning MySQL: my.cnf, avoid this common pitfall!
  • MySQL Performance: Stop hoarding. Drop unused MySQL databases

Published: June 7th, 2019 | Last updated: Nov 10th, 2022

Tags: apm, linux, mariadb, mysql, performance, server, sysadmins

MySQL Server Has Gone AwayWe 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:

$ ps xauf | grep ptkill

taras 6069 0.1 0.1 111416 29452 pts/29 S+ 10:57 0:00 | | _ perl /usr/bin/ptkill interval 1s busytime 5s matchinfo (SELECT) h=127.0.0.1 print kill

taras 6913 0.0 0.0 21532 1112 pts/30 S+ 11:00 0:00 | _ grep color=auto ptkill

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

<AUDIT_RECORD>

  <NAME>Query</NAME>

  <RECORD>624484743_20200630T17:38:14</RECORD>

  <TIMESTAMP>20200630T17:57:35 UTC</TIMESTAMP>

  <COMMAND_CLASS>kill</COMMAND_CLASS>

  <CONNECTION_ID>17</CONNECTION_ID>

  <STATUS>0</STATUS>

  <SQLTEXT>KILL QUERY 16</SQLTEXT>

  <USER>taras[taras] @ localhost []</USER>

  <HOST>localhost</HOST>

  <OS_USER></OS_USER>

  <IP></IP>

  <DB></DB>

</AUDIT_RECORD>

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):

INSERT INTO t1 VALUES (1, x89504….82)

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

SET GLOBAL max_allowed_packet = 128M ;

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.

mysql> SET SESSION wait_timeout = 5 ;

Query OK, 0 rows affected (0.00 sec)

Wait for 5 seconds:

mysql> select 1 ;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 16

Current database: *** NONE ***

+—+

| 1 |

+—+

| 1 |

+—+

1 row in set (0.01 sec)

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

SET GLOBAL wait_timeout = 57600 ;

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).

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

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

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

PhpMyAdmin

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

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

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

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

$ sudo vi /etc/mysql/my.cnf

innodb_buffer_pool_size=800M

https://pq.hosting/help/myfiles/mysqlhasgoneavay-991x576.png

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

Например:

wait_timeout=500

параметр wait_timeout

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

$ 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 имя_хранилища

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

Понравилась статья? Поделить с друзьями:
  • Spore как изменить уровень сложности
  • Spindle oil cooler error
  • Spore как изменить мод
  • Spindle gear error перевод
  • Spore как изменить голос