Very weird behavior in MySQLdb "execute"

John Nagle nagle at animats.com
Mon Feb 4 20:30:41 CET 2008


   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.

The table involved is:

CREATE TABLE domaincache
(
domain VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY,	
rating CHAR(1) NULL,								
rating_info ENUM 
('error','no_domain','no_website','blocked','no_location','negative_info','non_commercial','unverified') 
NULL,
special_case ENUM('normal','special'),				
rating_id BIGINT UNSIGNED NULL,						
last_update_end TIMESTAMP NOT NULL,					
version SMALLINT UNSIGNED NOT NULL,					
INDEX (rating_id)									
);

Nothing exciting there.

In the MySQL query browser, I can do either

    select * from domaincache where domain = "adwords.google.com"
or
    select * from domaincache where domain = "google.com"

and I get one record back from each, with the correct info.  That's correct.

Querying the database from Python gives different results.  The database
connection is made with:

    db = MySQLdb.connect(host="localhost",
	use_unicode = True, charset = "utf8",
	user=username, passwd=password, db=database)

When I make the same queries from Python, via IDLE, typing in commands:

    cursor.execute('SELECT * FROM domaincache WHERE domain="adwords.google.com"')

returns 0L, no rows, which is wrong. That domain is in the database, and
a SELECT from the graphical client will find it.

But

    cursor.execute('SELECT * FROM domaincache WHERE domain="google.com"')

returns 1L, one row, which is correct.

The database is InnoDB, and CHECK TABLE says the database is valid.

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?







More information about the Python-list mailing list