When attempting to import a bunch of data into mysql tables using python and mysqldb, I run into the following error '2006 – mySQL Server has gone away', and then I am unable to reconnect again within the script.
I am iniitially re-using a connection object across transactions ( delineated by conn.commit() ), then when I first encounter this exception, if I create a new connection by calling MySQLdb.connect(), this new connection also fails with the same exception.
This error does not occur immediately, I can pump a fair amount of data into the db, but then faithfully occurs after I have inserted a couple thousand records, so roughly once the db has committed a certain transaction volume, it always falls over like this.
If I rerun the script, WITHOUT restarting the db server. then it resumes where it left off, pumps in some data, then falls over again.
Before recommendations to change time-out timings, does anyone know why I am not able to establish a new connection after the initial failure ? – Even if I try a couple of times waiting a couple of seconds between each.
(btw, I'm running Windows 7, mysql server 5.1.48, mysqldb 1.2.3.gamma.1, python 2.6)
Best Answer
It might have to do with the fact that you're keeping a persistent connection to the database and that might time out.
See here: q1 and q2
Also, can you provide some code that you've tried and doesn't actually work ?
And while we're at it, database connections can be expensive, but maybe you can acquire a new one every batch (provided that you can split the data into batches)