Lost connection to MySQL server during query?

Question

Possible Duplicate:
Lost connection to MySQL server during query

I am importing some data from a large csv to a mysql table. I am losing the connection to the server during the process of importing the file to the table.

What is going wrong?

The error code is 2013: Lost connection to the mySql server during the query.

I am running these queries from a ubuntu machine remotely on a windows server.

score 46 · Answer 1 · edited May 06 '14 at 11:23

46

Try the following 2 things...

1) Add this to your my.cnf / my.ini in the [mysqld] section

max_allowed_packet=32M

(you might have to set this value higher based on your existing database).

2) If the import still does not work, try it like this as well...

mysql -u <user> --password=<password> <database name> <file_to_import

edited May 06 '14 at 11:23

Blizz

8,082
2
33
53

answered Aug 10 '11 at 05:28

Ashwin A

3,779
23
28

1

Temp changing to max_allowed_packet=4096M worked for import for me. – Hayden Mar 17 '13 at 21:54
Should the my.cnf be modified for the server sending the data or the server receiving the data? – thealexbaron Aug 15 '14 at 21:10
this is not a good solution, I have a HUGE amount of data to import, I shouldn't have to guess what the right number is only to find out that I am wrong after the queries have been running for days. down voting, please provide reliable solution. – user3338098 Nov 20 '15 at 15:26
In my case this was caused by a bad foreign key reference to another database when creating a table, `REFERENCES \`Meta.Users\``, changed to `REFERENCES \`Meta\`.\`Users\`` and it worked. – BurnsBA Jan 08 '16 at 17:42
where do I get my.cnf file – Nasif Imtiaz Ohi Dec 31 '17 at 14:18

score 22 · Answer 2 · answered Jun 29 '11 at 07:15

22

Usually that happens when you exhaust one resource for the db session, such as memory, and mysql closes the connection.

Can you break the CSV file into smaller ones and process them? or do commit every 100 rows? The idea is that the transaction you're running shouldn't try to insert a large amount of data.

I forgot to add, this error is related to the configuration property max_allowed_packet, but I can't remember the details of what to change.

answered Jun 29 '11 at 07:15

Augusto

28,839
5
58
88

4

Is there any way to find out if that is the case, that one resource is exhausted? And in that case, is it possible to find out which resource? – Simon Forsberg May 16 '12 at 17:14
dotcloud scale db:memory=512M <-- larger number should set this straight – ddavtian Dec 24 '12 at 09:41

Shyam Natraj Kanagasabapathy · Accepted Answer · 2011-08-10T08:53:52.433

9

The easiest solution I found to this problem was to downgrade the MySql from MySQL Workbench to MySQL Version 1.2.17. I had browsed some MySQL Forums, where it was said that the timeout time in MySQL Workbech has been hard coded to 600 and some suggested methods to change it didn't work for me. If someone is facing the same problem with workbench you could try downgrading too.

edited Aug 10 '11 at 08:53

answered Aug 10 '11 at 04:29

Shyam Natraj Kanagasabapathy

1,833
4
19
25

My workbench output does show that the query duration was 600.511 sec so my problem probably is related to this. One thing i am confused about however, is that my query used to take only 15 seconds and after adding 4 more where conditions, it has shot up to >600 seconds. – agent provocateur Sep 12 '14 at 20:25
I feel like you're getting warm with this... What version did you downgrade to? – Gus Crawford Dec 23 '14 at 20:48

score 7 · Answer 4 · edited May 23 '17 at 11:55

7

1) you may have to increase the timeout on your connection.

2)You can get more information about the lost connections by starting mysqld with the --log-warnings=2 option.

This logs some of the disconnected errors in the hostname.err file

You can use that for further investigation

3) if you are trying to send the data to BLOB columns, check server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in following link, “Packet too large”.

4) you can check the following url link

5) you should check your available disk space is bigger than the table you're trying to update link

edited May 23 '17 at 11:55

Community

1
1

answered Jun 29 '11 at 07:20

gmhk

15,598
27
89
112

As mentioned by Augusto, you can break the csv into files and try that, if the files size is too large – gmhk Jun 29 '11 at 07:24
how do I increase the timout of my connection? – Shyam Natraj Kanagasabapathy Jun 29 '11 at 07:28
Where is this mysterious hostname.err file on windows? – Vincent May 27 '18 at 16:52
The "Packet too large" link is still helpful. It points out, critically, that you may need to increase max_allowed_packet for both the client and the server. – user697473 Apr 03 '20 at 16:56

score 3 · Answer 5 · answered Jun 29 '11 at 09:06

You might like to read this - http://dev.mysql.com/doc/refman/5.0/en/gone-away.html - that very well explains the reasons and fixes for "lost connection during query" scenarios.

In your case, it might be because of the max allowed packet size as pointed by Augusto. Or if you've verified it isn't the case, then it might be the connection wait timeout setting due to which the client is losing connection. However, I do not think latter is true here because it's a CSV file and not containing queries.

score -3 · Answer 6 · answered Apr 09 '12 at 09:44

-3

I think you can use mysql_ping() function.

This function checks for connection to the server alive or not. if it fails then you can reconnect and proceed with your query.

answered Apr 09 '12 at 09:44

vkGunasekaran

6,668
7
50
59

This is specific to PHP, the asker is not using PHP – phenicie Jan 22 '15 at 16:29

Lost connection to MySQL server during query?

6 Answers6

Linked