up vote 0 down vote favorite
share [g+] share [fb]

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)

link|improve this question
feedback

1 Answer

up vote 0 down vote

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)

link|improve this answer
Thanks disciple of N. – david.barkhuizen Sep 13 '10 at 20:40
Turns out the issue was that I was actually first getting a first exception, and my poor python exception-handling was masking the it. So the deal is, the first exception is - 1153, 'Got a packet bigger than 'max_allowed_packet' bytes', then, when i tried to create a new connection, i was getting - 2006, 'MySQL server has gone away'. A solution was to edit the mysql service/daemon config file and increase the 'max_allowed_packet' value from its defaut value of 1 MB (1M), to something less aenemic. e.g 4MB (4M).add the following line: max_allowed_packet=4M) – david.barkhuizen Sep 13 '10 at 20:44
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.