Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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)

share|improve this question

2 Answers 2

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)

share|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

I've actually made a blogpost about that problem. You need to increase the max_allowed_packet size:

# The MySQL server
[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 64M   <======
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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