MySQLDB multiple cursor question

[Brian Kelley]

I am trying to use threads and mysqldb to retrieve data from multiple
asynchronous queries.

My basic strategy is as follows, create two cursors, attach them to the
appropriate databases and then spawn worker functions to execute sql
queries and process the results.

This works occasionally, but fails a lot taking python down with it.
Sometimes it also loses connection to the database. Sometimes I get an
error, "Commands out of sync; You can't run this command now" which
makes me suspicious. Of course, I could be doing things completely
wrong. If I can't have multiple cursors by the way, that's just fine
with me. I just thought that I could ;)

I only have one thread or no threads at all it works just fine. I have
tried using thread safe Queues to bundle results and also lists with the
same results.

Can anyone notice anything in the toy code I have attached that would
cause this effect? Thanks for any input.

import MySQLdb, thread, time

def cursoriterate(cursor, buffer=100):
res = cursor.fetchmany(buffer)
while res:
for record in res:
yield record
res = cursor.fetchmany(buffer)

def worker(cursor, sql, result):
try:
print "executing", sql
cursor.execute(sql)
output = []
for record in cursoriterate(cursor):
output.append(cursor)

result.append(output)
print "done"
except:
# just for testing
result.append(None)
raise

for i in range(100):
sql = "select target, result, evalue from BLAST_RESULT where evalue
< 0.001"
db = MySQLdb.connect(user="mergedgraph", host="localhost")
cursor = db.cursor()
cursor.execute("USE HPYLORI_YEAST")
cursor2 = db.cursor()
cursor2.execute("USE HPYLORI_YEAST")

result = []

thread.start_new_thread(worker, (cursor, sql, result))
thread.start_new_thread(worker, (cursor2, sql, result))

while len(result)< 2:
time.sleep(1)

print "results are full"
res = result.pop()
res2 = result.pop()

if res: print len(res)
if res2: print len(res2)
cursor.close()
cursor2.close()
db.close()

[Brian Kelley]

Brian Kelley wrote:

I am trying to use threads and mysqldb to retrieve data from multiple
asynchronous queries.

My basic strategy is as follows, create two cursors, attach them to the
appropriate databases and then spawn worker functions to execute sql
queries and process the results.

The problem goes away if I have only one cursor per connection and just
use multiple connections. This seems like a bug but I don't know for sure.

Brian

[Dennis Lee Bieber]

Brian Kelley fed this fish to the penguins on Thursday 08 January 2004
07:58 am:

The problem goes away if I have only one cursor per connection and
just
use multiple connections. This seems like a bug but I don't know for
sure.
f The DB-API specifies a common method for accessing data -- this means
"cursors".

MySQL itself does not implement that type of cursor.

Therefore, MySQLdb has to emulate cursors locally. That emulation may
be tied to one per connection (or, at least, one active per connection
-- maybe doing a conn.commit()?) [This is all hypothesis at this time]

-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Bestiaria Home Page: http://www.beastie.dm.net/ <
Home Page: http://www.dm.net/~wulfraed/ <

[Brian Kelley]

Dennis Lee Bieber wrote:

f The DB-API specifies a common method for accessing data -- this means
"cursors".

MySQL itself does not implement that type of cursor.

Therefore, MySQLdb has to emulate cursors locally. That emulation may
be tied to one per connection (or, at least, one active per connection
-- maybe doing a conn.commit()?) [This is all hypothesis at this time]

Guess I'll have to crack open the mysqldb source code and fire up a
debugger. The main problem with using multiple connections is that I
have to cache the user's password in order to repoen the connection
which makes me feel very queasy.

The error is very reproducible but that fact that it works sometimes and
not others means that it is probably a bug in mysqldb.

Brian

[AdSR]

Brian Kelley <bk*****@wi.mit.edu> wrote:

Brian Kelley wrote:

I am trying to use threads and mysqldb to retrieve data from multiple
asynchronous queries.

My basic strategy is as follows, create two cursors, attach them to the
appropriate databases and then spawn worker functions to execute sql
queries and process the results.

The problem goes away if I have only one cursor per connection and just
use multiple connections. This seems like a bug but I don't know for sure.

Brian

See PEP 249, read about the "threadsafety" global variable.

HTH,

AdSR

[Brian Kelley]

AdSR wrote:

See PEP 249, read about the "threadsafety" global variable.
There you have it. MySQLdb has a threadsafety level of 1 which means
that connections can't be shared but the module can.

I guess I'm doing it the right way now :)

HTH,

AdSR

[Dennis Lee Bieber]

Brian Kelley fed this fish to the penguins on Thursday 08 January 2004
16:28 pm:

There you have it. MySQLdb has a threadsafety level of 1 which means
that connections can't be shared but the module can.
I'd run into a reference to that attribute in the Nutshell, but the
section on DB-API only mentioned that 0 meant not-thread-safe; no
explanation of what different positive values might mean (and I didn't
have time this morning to try to find it via google).
-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Bestiaria Home Page: http://www.beastie.dm.net/ <
Home Page: http://www.dm.net/~wulfraed/ <

[Brian Kelley]

Dennis Lee Bieber wrote:

Brian Kelley fed this fish to the penguins on Thursday 08 January 2004
16:28 pm:

There you have it. MySQLdb has a threadsafety level of 1 which means
that connections can't be shared but the module can.

I'd run into a reference to that attribute in the Nutshell, but the
section on DB-API only mentioned that 0 meant not-thread-safe; no
explanation of what different positive values might mean (and I didn't
have time this morning to try to find it via google).

If you google for PEP 249 you'll find the description.

Brian