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

I'm using a python script with the python library MySQLdb to upload a large amount of data to a local MySQL database. When I used a sample of the data everything ran fine and uploaded. Now I am using all of my data (it's about 300000 lines split into 12000 text files) and I receive the following error:

"OperationalError: (2003, ""Can't connect to MySQL server on 'localhost' (10055)"")"    

At the point where it crashes it has uploaded 17231 rows of information. I'm using Python 2.7, Win7 64 and MySQL 5.1.53 on a desktop machine. I used a WAMP installation and view the data using PhpMyAdmin. An example of the function I am using to upload the data is as follows:

# upload data
def updateDB(db, table, values):

    db = MySQLdb.connect (host = 'localhost', user = 'root', passwd = '', db = db)
    cursor = db.cursor()
    print str(values)
    cursor.execute(makeSQLHeader(table, values), values)     
    db.commit()
    db.close()

This works fine for the test data, and the text file structure is identical for all of my data. What would be a good strategy to address this problem?

share|improve this question
    
I'm inclined to think this is timeout issue. How long does your real test take approximately? –  DTest Jul 29 '11 at 17:06
1  
Found this, not sure how valid it is since I haven't worked with python stackoverflow.com/questions/207981/… –  DTest Jul 29 '11 at 17:07
    
Not very long, about a fraction of a second. –  celenius Jul 29 '11 at 17:08
1  
Are you opening a connection per file (and are they running in parallel)? –  DTest Jul 29 '11 at 17:13
1  
although I'm assuming the loop is serial, I think you might be better off only opening one connection for the duration. –  DTest Jul 29 '11 at 17:24
show 1 more comment

2 Answers

up vote 4 down vote accepted

My two thoughts on this are that either it's taking a long time to load and you're disconnecting with a timeout. There are some ways to autoreconnect in python found here Here are the relevant timeout variables in mysql: wait_timeout and interactive_timeout

Second idea, and from discussion in comments it looks like the right one. You're probably hitting the mysql connection limit by opening a connection per file.

Try opening a single connection and running the test (when you do this, you might hit the timeout, depending on how long it takes).

share|improve this answer
    
Thanks - that fixed it. –  celenius Jul 29 '11 at 18:47
add comment

It could be that you run into the limits of maximum allowed packets. You could set something like this in my.cnf:

max_allowed_packet=12M

For more information see this article in the MySQL reference guide.

share|improve this answer
add comment

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.