20

Ive got to import 1go of sql data, i raised up the max_allowed_packet to 1100M to be sure.

So i use :

My query 

mysql -u root -p -D mainbase < GeoPC_WO.sql

But 1 minute later it stops during the process and i get this error : 

**ERROR 2013 (HY000) at line 23: Lost connection to MySQL server during query
**Lost connection to MySQL server during query****
Neil
  • 54,642
  • 8
  • 60
  • 72
SoCkEt7
  • 2,237
  • 5
  • 22
  • 30
  • The sql file contains no error, its a world database geolocalisation table – SoCkEt7 May 18 '12 at 03:12
  • Did you try this ? http://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query – Joand Aug 23 '12 at 15:45
  • Good explanation and solution is provided here http://stackoverflow.com/questions/12425287/mysql-server-has-gone-away-when-importing-large-sql-file – seven Jun 01 '13 at 00:35

6 Answers6

28

Possible that you have some large insert statements that are bigger than you max size. Check your /etc/mysql/my.cnf file or wherever it is. Cannot remember what the default is - but setting it to something large like below may help.

This is one option

[mysqld]
max_allowed_packet = 16M

And maybe the other way

[mysqldump]
max_allowed_packet = 16M
Mahdi Bagheri Varnosfatherani
  • 97
  • 1
  • 9
Adrian Cornish
  • 23,227
  • 13
  • 61
  • 77
  • I set : [mysqld] max_allowed_packet = 1000M And maybe the other way [mysqldump] max_allowed_packet = 1000M And got this error ERROR 2013 (HY000) at line 23: Lost connection to MySQL server during query And it crash the sql server, ive got to restart it. Any ideas ? – SoCkEt7 May 18 '12 at 17:57
  • 1Gb for a packet is a little extreme :-) there are other reasons the connection can go away - ie timeouts – Adrian Cornish May 19 '12 at 02:55
  • There are no time out for sql in my config :-( – SoCkEt7 May 19 '12 at 13:27
  • You will have to look them up in the documentation and add them. Try this query. show variables like '%time%'; – Adrian Cornish May 20 '12 at 15:10
  • 2
    Worked for me, changed it to 1000M - don't forget to restart the server after you're done (if you're using Workbench, just applying the setting doesn't take effect, you need to restart it from Server > Startup/Shutdown) – Eternal21 Oct 02 '17 at 20:27
  • don't forget to restart mysql when you set this setting. – Danyal Sandeelo Dec 16 '21 at 13:27
14

I had exactly the same problem. After 1 hour of struggling I resolved this by setting

net_write_timeout

to a higher value (in my situation it's 300)

bakytn
  • 344
  • 5
  • 17
  • 1
    This was the only solution working in my case: a pipe like "mysqldump | mysql", the "mysql" command sending data to another host and being too slow, and causing a timeout (dumping directly to a file worked fine, piping to mysql failed) – golimar Oct 24 '13 at 17:15
  • 1
    Thank you! Worked for me too. – Vasili Pascal May 25 '17 at 15:23
2

In my case the problem ("Lost connection to MySQL Server during query") was in a corrupted dump file or in the misbehaving HDDs:

First, I made a dump on the main server and then copied that dump to the replication server. But it seems the replication server had some problems with its HDDs and the dump became corrupted, i.e. MD5 of the original dump file on the main server was different from MD5 of the dump copy on the replication server.

nightcoder
  • 13,149
  • 16
  • 64
  • 72
0

You can try with this:

First:

sudo /etc/init.d/mysql stop

Then you should edit this file:

sudo vi /etc/mysql/my.cnf

Add the following line to the [mysqld] section:

innodb_force_recovery = 4

Finally:

sudo /etc/init.d/mysql start

(innodb_force_recovery force the InnoDB storage engine to start. The value 4 mean your data files can be corrupted. For more information you can visit: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html)

Greetings.

Gustavo Magallanes-Guijón
  • 36
  • 3
0

In my case it was because of the lack of RAM, I tried to import a 90MB zipped sql file to a 1GB RAM vps server and the error 2013 keep occured until I switched down the httpd service to release some memory and run the import command again and it was successful then.

xManh
  • 67
  • 3
0

I am getting same error when import SQL file:

ERROR 2013 (HY000) at line 23: Lost connection to MySQL server during query Lost connection to MySQL server during query

I have solved by using this solution:

First of all, stop the XAMPP/Wamp and then kindly open this file xampp\mysql\bin\my.ini

Increase size as per your requirement

max_allowed_packet=60M

And then restart your XAMPP/Wamp.

NOTE: For Windows, you can find the file in the C:\xampp\mysql\bin\my.ini Folder (Windows) or in the etc-Folder (within the xampp-Folder).

Very important Note: "Close your Command Prompt And Restart Again" (It's very important because if you didn't restart your command prompt then changes will not be reflected.)

Udhav Sarvaiya
  • 9,380
  • 13
  • 53
  • 64