Paul Boddie
2/4/2008 8:07:00 PM
On 4 Feb, 20:30, John Nagle <na...@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.
Of course, this is MySQL we're talking about, so you might get away
with this, but bizarre side-effects are sometimes a hallmark of MySQL,
too.
> 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"
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.
> 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.
Try using parameters instead of attempting to put the desired value of
domain directly into the query...
> But
>
> cursor.execute('SELECT * FROM domaincache WHERE domain="google.com"')
....or try using single quotes when delimiting the desired value of
domain.
> 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.
Cheap shot: I guess this is why I'm using PostgreSQL.
Seriously, though, MySQL 5.x can be quite usable provided that you
switch on conformance to standards and do as much inside explicit
transaction blocks as possible. Indeed, this latter point might point
to a solution: the MySQLdb module might automatically put statements
inside transactions (like many other DB-API modules), whereas other
clients might not use transactions unless you use BEGIN and ROLLBACK/
COMMIT - a recommended practice given that I've seen MySQL abort
inserts and leave them half finished.
> 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?
Transactions?
Paul