Lost connection to MySQL server during query? [duplicate]

This question already has answers here: Closed 10 years ago.

Possible Duplicate:

Lost connection to MySQL server during query

I am i开发者_开发知识库mporting 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.

---

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

---

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.

---

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.

---

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

---

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.

---

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.