Very weird behavior in MySQLdb "execute"

[Carsten Haese]

On Mon, 2008-02-04 at 11:30 -0800, John Nagle wrote:
> Restarting the MySQL instance changes the database.  The entry "google.com"
> disappears, and is replaced by "www.google.com".  This must indicate a hanging
> transaction that wasn't committed.
> 
> But that transaction didn't come from the Python IDLE session I've been
> making test calls from.  Those queries should match the graphical client
> exactly.
> 
> So why don't they agree?

I don't have a definitive answer, but I do have a piece of generic
advice. If two database sessions receive differing results, the cause
could be any of the following factors:

1) One session is in a transaction, but the other is not.

2) Both sessions are in a transaction, but they are at different
isolation levels.

3) Both sessions are in a transaction, but the transactions were started
at different times.

It's entirely possible that the graphical client was operating outside
of a transaction, showing you phantom rows of a transaction in progress,
whereas the Python IDLE session was inside a transaction at something
like "read last committed" (or whatever MySQL calls it) isolation level.

Any of the factors above determine which "snapshot" of the data will be
visible to the database clients. Unless you are *absolutely certain*
that both clients should have seen the exact same snapshot, it's really
not all that weird that you are seeing discrepancies, especially in
light of the fact that you had an uncommitted transaction hanging around
somewhere.

Hope this helps,

-- 
Carsten Haese
http://informixdb.sourceforge.net