Very weird behavior in MySQLdb "execute"

[John Nagle]

Paul Boddie wrote:
> On 4 Feb, 20:30, John Nagle <na... at animats.com> wrote:
>>    This has me completely mystified.  Some SELECT operations performed through
>> MySQLdb produce different results than with the MySQL graphical client.
>> This failed on a Linux server running Python 2.5, and I can reproduce it
>> on a Windows client running Python 2.4.  Both are running MySQL 2.5.
> 
> I'm not actively using MySQL at the moment, so my comments are just
> passing remarks that may or may not help.
> 
>> The table involved is:
>>
>> CREATE TABLE domaincache
>> (
>> domain VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY,
> 
> Passing remark #1: I believe that "domain" is a reserved word in SQL.

    Not since SQL-99.  DOMAIN stopped being a reserved word in SQL-2003.
See "http://developer.mimer.com/validator/sql-reserved-words.tml"
It is not a reserved word in MySQL.

>>
>>     select * from domaincache where domain = "adwords.google.com"
>> or
>>     select * from domaincache where domain = "google.com"
> 
> Passing remark #2: In SQL, double quotes are usually used to "protect"
> identifiers from being recognised as reserved words. Now, I believe
> that MySQL can be quite relaxed about this, but this could be an issue
> if some conformance mode gets set somewhere.

    The SQL standard requires single quotes, while MySQL allows both.
Actually, the real code lets the MySQLdb interface do the quoting; this
was just a manual test.

> Cheap shot: I guess this is why I'm using PostgreSQL.

    Actually, most of the above comments were totally irrelevant.  The
real problem (discussed in the Python newsgroup) was a failure to
COMMIT after a SELECT.   MySQL InnoDB databases run in "repeatable read" mode,
and if you have a long-running process and don't COMMIT after a SELECT,
the results of redoing a SELECT will not change, regardless of other
updates to the table.  So it really is necessary to COMMIT after SELECT
to see new updates to the database, even when not making changes.

				John Nagle