mysqldb reports "out of sync" error regarding cursors

[Mike Bayer]

original description:

I'm using SA to talk to a MySQL database, but the database has a tendency to drop my connection while the queries are running.

SQLError: (OperationalError) (2013, 'Lost connection to MySQL server during query') 'SELECT sum(bgp_churn.count) \nFROM bgp_churn \nWHERE bgp_churn.tstamp > %s AND bgp_churn.type = %s \n LIMIT 1' 'U'

This isn't a particularly big deal for my application, I'm caching the results and can just retry a minute or two later. Unfortunately, this IS a big deal to SQLAlchemy. Frequently, but not always, it gets into a state where every query after this results in a 'Commands out of sync' message.

SQLError: (ProgrammingError) (2014, "Commands out of sync; you can't run this command now") 'SELECT bgp_churn.asn, sum(bgp_churn.count) AS count \nFROM bgp_churn \nWHERE bgp_churn.tstamp > %s AND bgp_churn.type = %s GROUP BY bgp_churn.asn ORDER BY count DESC \n LIMIT 4' [1158089885.0, 'U'

Here's the MySQL manual page on this error:

http://dev.mysql.com/doc/refman/4.1/en/commands-out-of-sync.html

The strange thing is that this does not occur consistently. It seems to occur less frequently when echo is on but that might be a placebo on my part.

[Mike Bayer]

so, we arent able to reproduce this. we are hypothesizing that the error corresponds to a connection being used in more than one thread, as another user reported that this error goes away when setting a thread_pool size of zero in cherrypy.

we have tried adding a cursor.close() to ResultProxy so that all cursors should get closed. however, this is not foolproof as a ResultProxy might still be hanging around when a connection gets returned and reused. the ORM should be vigilant about closing ResultProxies but this hasnt been verified, and also apps that use constructed SQL might not be as careful.

so the next thing to try will be to instrument CursorFairy to keep associated with its ConnectionFairy, and have ConnectionFairy close all cursors before being returned to the pool (or to insure a ConnectionFairy stays checked out until all cursors are closed).

[Mike Bayer]

the above diff has been committed. i have a feeling the issues can be traced to cursors hanging open, which wont happen anymore with that patch above, which might reveal other issues with how people are using their Sessions...will leave this ticket open for awhile until we get more info.

[Mike Bayer]

this ticket is a dupe of #387