Forums

I'm trying to load data into my MySQL database and I've wrriten a script to do this. The script does 2 main things:

1. loads data from the csv files into a set of staging tables
2. 'migrates' the data from the staging tables into the operational tables

The script to do this which works fine on my local web2py installation.

However when I run it from a PA console, the first step succeeds but then at the second step, the script exits with the error:

_mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

The query in question is a large one pulling back a lot of data.....is there some MySQL limit set by PA that my query is exceeding?

Had a look on StackOverflow and found the following Lost connection to MySQL server during query.

I checked the value of max_allowed_packet on PA and it is set to 16M. I set my local MySQL instance to the same but could not recreate the error :(

Any wisdom from the crowd?

You're right that we've set max_allowed_packet to a very high value, so that's probably not it. I'll do a little digging into whether there's some kind of time limit...

In the meantime, you could investigate re-writing your script so that it uses lots of smaller queries, rather than one big query?

I'm not aware of any timeout in MySQL that explicitly limits the time of an actively running query, although there's nothing to stop the database administrators implementing their own. For connections which appear idle, the wait_timeout (or interactive_timeout for interactive connections) setting can cause your connection to close. If you're pulling back a lot of data, however, I wouldn't expect the connection to have been classed as idle.

I guess it could also be some firewall somewhere not liking the long-running connection, but that's always going to be hard to diagnose.

In general I agree with Harry - if at all possible, try and use WHERE and LIMIT clauses to split your query up into manageable chunks. This often has extra benefits too, like keeping your application more responsive to input from other sources.

Looking at the traceback, the 'lost connection' error occurs when my script does a SELECT COUNT(*) query. I wouldn't have thought such a query would have caused this error.

I've isolated the SQL query to a standalone script and it runs ok on PA.

So a bit baffled by this.

Oh, wait a sec... Does your MySQL connection go idle for some time before this query? If so, it's possible the connection is being idled out, but MySQL only actually notices that it's gone when you try and perform your next query.

In your script where you see the problem, you could try calling the ping() method on the connection object (not the cursor) just prior to performing the query - if the connection is lost, that should either automatically reconnect (problem solved) or it should raise an OperationalError in which case you'll have to create a new connection.

Apologies for the vagueness, but I'm dredging this up from when I implemented a MySQL connection pool in Python many moons ago and I can't quite recall whether MySQLdb has automatic reconnection enabled or not. In any case, I did discover that one should never rely on long-running transactions in MySQL - if it's going to take more than a few seconds then don't bother. If you're only reading, of course, then transactions don't really matter so things are a lot less critical.

EDIT: The docstring for ping() indicates that if you call conn.ping(True) then it sets the automatic reconnect flag persistently for that connection, so perhaps you can just do that once when you first connect and not worry about it.

Just be aware that any disconnect (including due to idling) implicitly rolls back any outstanding transaction, as I mentioned earlier, which is why I tend to prefer manual reconnection because then if a transaction was pending you can raise an exception in Python so your code doesn't push the DB into an inconsistent state. Automatic reconnection seems more convenient, but if you're using transactions then it can make issues very much harder to track down.

Face slap.

Just parsed through my script and saw that I was purging the web2py database metadata as part of a db reset. This caused MySQL to fall over with the 'lost connection' error.

Apologies.

Just an FYI.

I've recreated this issue and my original explanation (that it was caused by accidentally purging the web2py db metadata) is wrong.

As described, the issue occurs when I run a long-running build script (creating schema, loading from csv files, moving data into new tables etc.)

The fix is to chunk the script in smaller more discrete components as suggested by Harry above.

Thanks for letting us know, hurlingstats. How long is "long-running"? Perhaps there's something we can tweak.