MySQLdb autocommit and SELECT problems

[Jesse Lehrman]

I'm experiencing strange behavior using MySQLdb.

If I turn off autocommit for my connection, I get stale data when I perform multiple and identical SELECT's. If autocommit is enabled, I follow my SELECT statement with a COMMIT statement or I create a new connection I don't get this behavior.

Example:

TABLE EMPLOYEES (InnoDB)
|| ID | NAME ||
|| 1  | bob   ||

import MySQLdbdb = MySQLdb.connect(host=host, user=user, passwd=passwd, db=schema)cursor = db.cursor()cursor.execute("SELECT @@autocommit")autocommit = cursor.fetchall()cursor.execute("SELECT name FROM EMPLOYEES WHERE id=1")firstResult = print cursor.fetchall()cursor.close()# I now go and change 'bob' to 'dave' using some other MySQL client. I also confirm that the data was changed with that client.cursor = db.cursor()
cursor.execute("SELECT name FROM EMPLOYEES WHERE id=1")secondResult = cursor.fetchall()
cursor.close()# I append a COMMIT statement to the SELECT
cursor = db.cursor()
cursor.execute("SELECT name FROM EMPLOYEES WHERE id=1; COMMIT;")thirdResult = cursor.fetchall()
cursor.close()

print autocommit
print firstResult
print secondResult
print thirdResult>>((0),)>>((bob),)>>((bob),)>>((dave),)

It doesn't make sense to me that a COMMIT should affect the behavior of a SELECT statement at all. I tried replicating this using the MySQL Query browser but that gives me the expected result (ie: I see the change). I know that the second SELECT was successful because MySQL increments its SELECT counter.

I realize the easy fix is to just add a COMMIT to all my SELECT statements but I'm trying to understand why it's doing this.

Python v2.4.3 (WinXP 64-bit)
MySQLdb v 1.2.2 (also tried v1.2.1)
MySQL 4.1.12 and 5.0.45 (Linux)

thanks in advance!

Jesse
j_lehrman at hotmail.com

_________________________________________________________________
Send a smile, make someone laugh, have some fun! Start now!
http://www.freemessengeremoticons.ca/?icid=EMENCA122
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20071129/4edf8eaf/attachment.html>