1

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)

2 Answers 2

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)

1
  • 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) Sep 13, 2010 at 20:44
2

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .