MySQL error 2006: mysql server has gone away

Question

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

2006, MySQL server has gone away

I've heard about the MySQL setting, wait_timeout, but do I need to change that on the server at my office or the remote MySQL server?

possible duplicate of [ERROR 2006 (HY000): MySQL server has gone away](http://stackoverflow.com/questions/10474922/error-2006-hy000-mysql-server-has-gone-away) — Simon East, Oct 06 '14 at 00:40
For people getting here from Google: If changing the `max_allowed_packet` size or `wait_timeout` amount doesn't fix it, check your memory usage. I was getting the same error and it was being caused by my server running out of memory. I added a 1GB swap file and that fixed it. — Pikamander2, Sep 19 '16 at 23:52
Oh! So it's all lies? Mysql server actually didn't go anywhere? It's still right there in my server? Whao! :)) — Damilola Olowookere, Apr 05 '18 at 17:28
For CentOs users : if changing max_allowed_packet in ~/.my.cnf doesn't work, try that for /etc/my.cnf :) — Little Elite, Sep 18 '21 at 10:34

George · Answer 1 · 2023-04-05T17:37:04.147

472

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, it must appear as an entry underneath [mysqld]

Note: This can be set on your server as it's running but it will be lost after the mysql daemon is restarted. Use SET GLOBAL max_allowed_packet=104857600 (this sets it to 100MB)

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

edited Apr 05 '23 at 17:37

answered Feb 28 '12 at 09:48

George

4,906
2
16
21

30

Note that this can be set on your server as it's running. Use: "set global max_allowed_packet=104857600". NOTE: My value sets it to 100MB. – rickumali Mar 27 '12 at 17:03
This was the solution to my 'mysql server has gone away' errors that I KNEW weren't actually timeouts. – Collin Green Oct 02 '12 at 23:31
36

For xampp users, the my.cnf can be found at: C:\xampp\mysql\bin\ – Valentin Despa Nov 08 '12 at 11:38
Note that for Mac users, mysql looks for your my.cnf file first at /etc/my.cnf, then /etc/mysql/my.cnf. In my case on Mountain Lion there was no file there, I copied one from /usr/local/mysql/support-files. – Nick Woodhams Feb 05 '13 at 10:17
solved my 2006 error which was caused by a mysql insert of a blob that was a binary file of about 16MB in size. THANKS George! – panofish Aug 09 '13 at 15:34
3

on WAMP: C:\wamp\bin\mysql\mysql5.6.12\my.ini, set max_allowed_packet = 500M under [wampmysqld] – Elia Weiss Dec 16 '13 at 09:11
For XAMPP users on Mac - this setting can be found at /Applications/XAMPP/xamppfiles/etc/my.cf line 32 usually default is 1M – pal4life May 15 '14 at 17:36
3

Fixed my problem tooo :) – Altaf Hussain Jan 30 '15 at 04:13
Thanks - this can be a real problem when you're updating your local server and need to backup and restore all your mysql databases! had to set my to 10G – Scott Flack Jan 06 '16 at 05:03
It makes me get this: `OperationalError: (2013, "Lost connection to MySQL server at 'reading authorization packet', system error: 0")` – Aminah Nuraini Dec 04 '17 at 15:29
5

Important Note: I had to restart my mysql server for this effect to take effect. i.e. `mysql.server stop`, `mysql.server start` (Oct 2018, MySQL v5.7, MacOS) – Nitin Nain Oct 09 '18 at 19:24
For centOS 7 users, make changes in /etc/my.cnf as mentioned, then sudo systemctl restart mariadb.service and login and check in php my admin... it worked for me..thanks – Akson Feb 27 '19 at 04:47
1

for mac user the file will be present at `/usr/local/etc/my.cnf` – Siddharth Pandey Sep 07 '20 at 14:42
In my Xubuntu, I found the file to be located at `/etc/mysql/my.cnf`. – Lori Apr 14 '22 at 22:06
Thank you, that is the ONLY thing that worked for me and it worked great and the very first time I made the change. Just make sure to add you setting change into the [mysqld] section. – MMEL May 28 '22 at 03:11
It is incredible but, even Xampp 8.1.17 still comes with this variable, in mariaDB settings, set as 1M – aldemarcalazans May 17 '23 at 19:22

Sathish D · Answer 2 · 2015-01-12T06:00:23.423

59

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.

edited Jan 12 '15 at 06:00

answered Oct 17 '13 at 11:51

Sathish D

4,854
31
44

7

The other guy wrote 16M, you writing 500M, whats the significance of this setting? – pal4life May 15 '14 at 17:32
2

@pal4life It is max size of the insert statements allowed. What if your insert statement is more than 16M( If the statement consists of longblob columns or so) To be on a safer side make it huge like 500M if you are inserting huge amount of data. – Sathish D May 16 '14 at 05:48
This worked for me but I don't know why. The default value was 1M but when I changed it to 100M the error went away. The problem started when I set wait_timeout = 30 in an attempt to reduce the number of idle threads on my server. – Vincent Apr 11 '18 at 00:27

score 45 · Answer 3 · edited Feb 24 '16 at 21:24

45

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;

edited Feb 24 '16 at 21:24

Michał Perłakowski

88,409
26
156
177

answered Feb 24 '16 at 10:31

Geshan Ravindu

451
4
5

wonder why was this downvoted? makes sense for the error to be related to packet size... – FlorinelChis Mar 25 '16 at 16:47
This solved my problem, it's not related to the question in direct way but should help people having this other issue. – Migerusantte Aug 15 '16 at 16:11
2

To check if changed or view the current value one can use `show variables like 'max_allowed_packet';` – Marcin Jan 22 '19 at 07:19

score 36 · Accepted Answer · edited Aug 24 '22 at 07:41

36

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

See PHP:mysqli_ping for info on that.

edited Aug 24 '22 at 07:41

Community

1
1

answered Oct 29 '11 at 23:15

Niet the Dark Absol

320,036
81
464
592

Good point, if you have a process that is intermittent then its better to release your connection so you don't used up all the connections. Rebuilding the connection is generally cheap. +1 – Yzmir Ramirez Nov 03 '11 at 00:48
2

in 2018: mysqli_ping is depricated – f b Sep 20 '18 at 06:53
1

@fb what is used to do that with PDO ? – beppe9000 Dec 05 '19 at 00:26

score 26 · Answer 5 · answered Feb 21 '19 at 09:48

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

Yzmir Ramirez · Answer 6 · 2011-10-31T23:49:51.370

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.

score 17 · Answer 7 · edited Feb 25 '14 at 07:09

17

In MAMP (non-pro version) I added

--max_allowed_packet=268435456

to ...\MAMP\bin\startMysql.sh

Credits and more details here

edited Feb 25 '14 at 07:09

icedwater

4,701
3
35
50

answered Feb 29 '12 at 17:00

uwe

3,938
11
37
50

score 16 · Answer 8 · answered Aug 03 '19 at 08:32

16

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 03 '19 at 08:32

Archana Kamath

426
4
4

That fixed the problem. Thanks! – Andrei Mar 30 '21 at 03:35

score 12 · Answer 9 · edited Jun 20 '20 at 09:12

12

This error is occur due to expire of wait_timeout .

Just go to mysql server check its wait_timeout :

mysql> SHOW VARIABLES LIKE 'wait_timeout'

mysql> set global wait_timeout = 600 # 10 minute or maximum wait time out you need

http://sggoyal.blogspot.in/2015/01/2006-mysql-server-has-gone-away.html

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 16 '15 at 04:26

Saurabh Goyal

151
1
4

I don't understand why anyone would downvote this answer?! Was helpful to me. – Basil Musa Jan 19 '16 at 21:30

Pikamander2 · Answer 10 · 2021-07-21T07:08:06.220

12

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.

edited Jul 21 '21 at 07:08

answered Sep 19 '16 at 23:45

Pikamander2

7,332
3
48
69

1

Thank you so much! I had the same problem on my DigitalOcean and this solution worked! I ran many instances of my script but after some threads it suddenly stopped and killed all existing connections. Now it's all good. – Stalinko Sep 05 '17 at 11:12

score 11 · Answer 11 · answered Oct 16 '15 at 16:02

11

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 '15 at 16:02

Kenneth mwangi

805
7
13

this should be the chosen answer – bysanchy Oct 26 '15 at 11:16
I can't find `upload_max_filesize` variable. It always get unrecognized in my mysql – Aminah Nuraini Sep 16 '18 at 14:38
@AminahNuraini because it isnt the mysql but the `php.ini` setting option. – Muhammad Omer Aslam Sep 11 '20 at 14:09

score 10 · Answer 12 · answered Mar 26 '17 at 05:38

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

score 7 · Answer 13 · answered Sep 08 '16 at 14:31

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

score 7 · Answer 14 · answered Jun 29 '18 at 14:35

7

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 '18 at 14:35

Memo

91
1
2
12

score 6 · Answer 15 · answered Mar 14 '18 at 14:54

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.

score 6 · Answer 16 · answered May 05 '19 at 05:44

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.

score 5 · Answer 17 · answered Jun 30 '18 at 18:06

5

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 '18 at 18:06

Alex

32,506
16
106
171

score 5 · Answer 18 · answered May 09 '19 at 20:47

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:

Go to http://localhost/phpmyadmin
Go to SQL tab
Run SHOW VARIABLES and check the values, if it is small then run with big values
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;

score 4 · Answer 19 · answered Dec 13 '15 at 08:39

4

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 '15 at 08:39

Shadoweb

5,812
1
42
55

Ahmed · Answer 20 · 2021-08-11T22:46:04.147

4

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.

edited Aug 11 '21 at 22:46

answered May 23 '17 at 18:36

Ahmed

2,825
1
25
39

score 4 · Answer 21 · answered Jan 10 '18 at 11:43

4

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 '18 at 11:43

Nikunj Dhimar

2,296
19
24

score 3 · Answer 22 · answered Sep 25 '15 at 14:30

3

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 '15 at 14:30

Nico

25
1

If you are using REPLICATION, current best practice is binlog_format=ROW for reliability of replication activities. – Wilson Hauck May 20 '22 at 14:16

score 3 · Answer 23 · edited Jun 10 '17 at 12:01

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

config.inc.php
config.sample.inc.php

Path of these files in windows is

C:\wamp64\apps\phpmyadmin4.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:

config.inc.php
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.

score 2 · Answer 24 · answered Jun 16 '17 at 15:58

2

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 '17 at 15:58

Bo Guo

101
3

Dmitry Leiko · Answer 25 · 2020-11-12T08:03:43.507

2

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}

edited Nov 12 '20 at 08:03

answered Nov 11 '20 at 17:14

Dmitry Leiko

3,970
3
25
42

Isn't it `--wait-timeout=800` instead of `--wait_timeout=800`? – LuisEnMarroquin Jan 11 '22 at 01:36

score 1 · Answer 26 · edited Mar 28 '22 at 16:44

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.

score 1 · Answer 27 · answered May 03 '21 at 01:49

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!

score 0 · Answer 28 · answered Jun 26 '18 at 12:10

0

For users using XAMPP, there are 2 max_allowed_packet parameters in C:\xampp\mysql\bin\my.ini.

answered Jun 26 '18 at 12:10

Subhash

178
1
9

score 0 · Answer 29 · answered Sep 21 '18 at 10:54

This error happens basically for two reasons.

You have a too low RAM.
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.

Just to clarify, when you say the database connection is closed when you try to connect, do you mean a database connection was never created? Or do you mean, the connection closed while trying to use it? — Vincent, Aug 02 '23 at 01:57

score 0 · Answer 30 · answered Aug 25 '21 at 16:04

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

score 0 · Answer 31 · answered May 09 '22 at 13:12

0

My issue was that I had added SSL configuration to connection for production which broke my local environment. Simple but might help others realize their problem.

answered May 09 '22 at 13:12

vdidxho

165
1
14

score -7 · Answer 32 · answered Oct 30 '11 at 00:18

-7

If you know you're going offline for a while, you can close your connection, do your processing, reconnect and write your reports.

answered Oct 30 '11 at 00:18

Joshua Martell

7,074
2
30
37

2

This is actually a viable answer, since MySQL closes idle connection after eight hours. – Bojan Hrnkas Apr 11 '16 at 13:28

MySQL error 2006: mysql server has gone away

32 Answers32

MySQL/MariaDB related:

Server related:

Framework related:

How to debug it:

Linked

Related