Odd MySQL bug

106 views
Skip to first unread message

Malcolm Tredinnick

unread,
Dec 19, 2007, 8:21:53 AM12/19/07
to django-d...@googlegroups.com
If anybody would like to look at a strange MySQL problem, I'd appreciate
some insights.

Checkout the queryset-refactor branch and run the null_queries test with
the 'mysql' (or 'mysql_old') backend.

`--> ./runtests.py --settings=settings1 null_queries

----------------------------------------------------------------------
File "/home/malcolm/BleedingEdge/Django/django.git/tests/regressiontests/null_queries/models.py", line ?, in regressiontests.null_queries.models.__test__.API_TESTS
Failed example:
Choice.objects.filter(id__exact=None)
Expected:
[]
Got:
[<Choice: Choice: Why Not? in poll Q: Why? >]


----------------------------------------------------------------------
Ran 1 test in 0.021s

FAILED (failures=1)

So the test at [1] is failing (note this is slightly different from
trunk because of ticket #2737 has been fixed/changed on the branch).

Clearly something is amiss here, since the "id" attribute is a primary
key column (not NULL and unique, by definition), so nothing should match
against the query. The generated SQL can be seen by looking at
Choices.objects.filter(id__exact=None).query.as_sql() and gives

'SELECT `null_queries_choice`.`id`, `null_queries_choice`.`poll_id`, `null_queries_choice`.`choice` FROM `null_queries_choice` WHERE `null_queries_choice`.`id` IS NULL'

which looks correct.

Now things get really weird: If you cut and paste the line that is
failing so you that you run it twice in a row, it fails the first time
(returning a row) and passes the second time (returning nothing)!

Normally I wouldn't worry too much about this and just chalk it up to
the high quality weed they're smoking over there at MySQL Headquarters,
but this behaviour might actually explain some questions we see from
time to time on django-users. Somebody using MySQL will update a model
and then try to query it immediately and the new value won't appear in
the query for a little bit. We've always managed to eliminate
transaction issues, so the problem was just odd. Now I have a repeatable
case (at least for values of "repeatable" that mean "on my reasonably
fast machine, every time, using MySQL 5.0.45").

If somebody else can repeat this and has the brains to work out what is
going on, that would be interesting knowledge. Does MySQL delay writing
to the database for a second or so, just for laughs? And then not take
that into account when reading? I've never seen this before, but every
time I've used MySQL in really big projects, writes and reads of the
same piece of data have been fairly widely separated.

[1]
http://code.djangoproject.com/browser/django/branches/queryset-refactor/tests/regressiontests/null_queries/models.py#L27

Thanks in advance,
Malcolm


--
Remember that you are unique. Just like everyone else.
http://www.pointy-stick.com/blog/

Patryk Zawadzki

unread,
Dec 19, 2007, 8:36:42 AM12/19/07
to django-d...@googlegroups.com
2007/12/19, Malcolm Tredinnick <mal...@pointy-stick.com>:

> Clearly something is amiss here, since the "id" attribute is a primary
> key column (not NULL and unique, by definition), so nothing should match
> against the query. The generated SQL can be seen by looking at
> Choices.objects.filter(id__exact=None).query.as_sql() and gives
>
> 'SELECT `null_queries_choice`.`id`, `null_queries_choice`.`poll_id`, `null_queries_choice`.`choice` FROM `null_queries_choice` WHERE `null_queries_choice`.`id` IS NULL'
>
> which looks correct.
>
> Now things get really weird: If you cut and paste the line that is
> failing so you that you run it twice in a row, it fails the first time
> (returning a row) and passes the second time (returning nothing)!

Are you sure these are not artifacts from a previous query? Maybe the
cursor was not properly freed or something?

--
Patryk Zawadzki
PLD Linux Distribution

Malcolm Tredinnick

unread,
Dec 19, 2007, 8:51:27 AM12/19/07
to django-d...@googlegroups.com

Could be that. I meant it when I said I don't know what is causing it. I
haven't ruled out killer attack bunnies from Mars, either. All options
are on the table.

That's why I'm hoping somebody familiar with MySQL and the Python
wrapper will look more deeply and write a fix if something's going wrong
in the mysql backend.

What I do know is that all the other database backends behave as
expected: the data is written and read serially and the right result is
returned. There are no errors raised by MySQL and it's not as if it
hasn't written anything at all (since data is returned). It's just
decided not to write all the data and the bit it misses is 100%
repeatable. The output is consistent with somehow retaining the data we
sent to the database (which doesn't include the 'id') and not reading
back the real result, but that would be slightly insane behaviour.

Regards,
Malcolm

--
How many of you believe in telekinesis? Raise my hand...
http://www.pointy-stick.com/blog/

Karen Tracey

unread,
Dec 19, 2007, 10:18:44 AM12/19/07
to django-d...@googlegroups.com

Looks like it is an oddball (high quality weed?) mysql setting (see http://dev.mysql.com/doc/refman/5.1/en/set-option.html):

  • SQL_AUTO_IS_NULL = {0 | 1}

    If set to 1 (the default), you can find the last inserted row for a table that contains an AUTO_INCREMENT column by using the following construct:

    WHERE auto_increment_column IS NULL

    This behavior is used by some ODBC programs, such as Access.

Karen

Malcolm Tredinnick

unread,
Dec 19, 2007, 7:25:46 PM12/19/07
to django-d...@googlegroups.com

On Wed, 2007-12-19 at 10:18 -0500, Karen Tracey wrote:
[...]

>
> Looks like it is an oddball (high quality weed?) mysql setting (see
> http://dev.mysql.com/doc/refman/5.1/en/set-option.html):
>
> * SQL_AUTO_IS_NULL = {0 | 1}
>
> If set to 1 (the default), you can find the last inserted row
> for a table that contains an AUTO_INCREMENT column by using
> the following construct:
>
> WHERE auto_increment_column IS NULL
>
> This behavior is used by some ODBC programs, such as Access.

Oh dear. :-(

Thanks for hunting that down, Karen; it hadn't occurred to me to look in
that part of the documentation.

That's just wrong.

Regards,
Malcolm

--
I intend to live forever - so far so good.
http://www.pointy-stick.com/blog/

AmanKow

unread,
Dec 20, 2007, 2:47:22 PM12/20/07
to Django developers
I don't think mysql adding a setting to turn off/on behavior that
allows integration with some odbc applications as evidence of
hallucinogen use <grin />. It is an unfortunate choice to have the
non-standard behavior as the default, however.

Other than the above test, I find it hard to imagine someone following
an insertion with auto id with a query of auto is null in a real world
django application context. I just can't seem to figure out why that
would be useful outside of the given test.

As far as the test goes, what exactly is that test designed to do? To
insure that auto fields don't actually store nulls in the given
database? Isn't that a bit of overkill? If auto increment fields
stored null in a given database, things would fall apart pretty
quickly. And if the insert failed, you would know right away with an
exception. I'm not sure that given test earns much by way of ensuring
django's correctness. I wouldn't expect django's test suite to fully
exercise all of a database's basic functionality.

Anyhow, couldn't a 'SET SQL_AUTO_IS_NULL=0' be added maybe in the
generation of the mysql specific sql for syncdb? That would avoid the
need to do anything special for mysql in the test suite, if indeed
that test is deemed useful and must stay.

Thoughts?

Wayne

AmanKow

unread,
Dec 20, 2007, 3:46:22 PM12/20/07
to Django developers
Yikes!... it's a session variable only... makes it hard to change the
default behavior...

AmanKow

unread,
Dec 20, 2007, 4:12:22 PM12/20/07
to Django developers
Ok, I'm stymied... it's a session variable only, not global and
therefore I cannot change the default value in the options file.
Also, it is not dynamic, which means that it requires a server restart
to be effective, but then it uses the default behavior, oh, my head
hurts.

Ok, I take it back... I want some of what they're smoking.

Please ignore my naive:
> Anyhow, couldn't a 'SET SQL_AUTO_IS_NULL=0' be added maybe in the
> generation of the mysql specific sql for syncdb? That would avoid the
> need to do anything special for mysql in the test suite, if indeed
> that test is deemed useful and must stay.

Any mysql gurus out there with some advice as to how to turn off this
behavior?

Collin Grady

unread,
Dec 20, 2007, 4:30:06 PM12/20/07
to django-d...@googlegroups.com
AmanKow said the following:

> Any mysql gurus out there with some advice as to how to turn off this
> behavior?

The mysql docs and several things I've found online (including code from
Rails) indicates that simply "SET SQL_AUTO_IS_NULL=0" should work,
unless they broke it :)

--
Collin Grady

The past always looks better than it was. It's only pleasant because
it isn't here.
-- Finley Peter Dunne (Mr. Dooley)

AmanKow

unread,
Dec 20, 2007, 5:20:58 PM12/20/07
to Django developers
Well, the value of this can be accessed via:
SELECT @@session.sql_auto_is_null;
which gets you the result of 1
If you then perform a
SET SESSION sql_auto_is_null=0;
and perform the above query again, the value is..... 1
As I mentioned earlier, it isn't dynamic anyway, so even if it changed
to 0 the default behavior wouldn't change.

As far as I can tell, there is no way to change this behavior without
a recompile of the mysql code.

Collin Grady

unread,
Dec 20, 2007, 6:11:55 PM12/20/07
to django-d...@googlegroups.com
AmanKow said the following:

> Well, the value of this can be accessed via:
> SELECT @@session.sql_auto_is_null;
> which gets you the result of 1
> If you then perform a
> SET SESSION sql_auto_is_null=0;
> and perform the above query again, the value is..... 1
> As I mentioned earlier, it isn't dynamic anyway, so even if it changed
> to 0 the default behavior wouldn't change.

Why are you including the "SESSION" in your SET statement?

mysql> SELECT @@SESSION.SQL_AUTO_IS_NULL;
+----------------------------+
| @@SESSION.SQL_AUTO_IS_NULL |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SET SQL_AUTO_IS_NULL=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@SESSION.SQL_AUTO_IS_NULL;
+----------------------------+
| @@SESSION.SQL_AUTO_IS_NULL |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (0.00 sec)

--
Collin Grady

(1) Never draw what you can copy.
(2) Never copy what you can trace.
(3) Never trace what you can cut out and paste down.

Malcolm Tredinnick

unread,
Dec 20, 2007, 7:27:40 PM12/20/07
to django-d...@googlegroups.com

On Thu, 2007-12-20 at 13:12 -0800, AmanKow wrote:
> Ok, I'm stymied... it's a session variable only, not global and
> therefore I cannot change the default value in the options file.
> Also, it is not dynamic, which means that it requires a server restart
> to be effective, but then it uses the default behavior, oh, my head
> hurts.
>
> Ok, I take it back... I want some of what they're smoking.

Okay, everybody (or, at least, some people) are putting way to much
thought into this. Stop now.

I've already worked around it in queryset-refactor so that we never
generate this type of query. It was used in a test and as a way to avoid
incorrect results in one other place that I've now changed. It's a
complete non-issue now that I know what the bug is.

Regards,
Malcolm

--
What if there were no hypothetical questions?
http://www.pointy-stick.com/blog/

Patryk Zawadzki

unread,
Dec 20, 2007, 9:22:54 PM12/20/07
to django-d...@googlegroups.com
2007/12/21, Malcolm Tredinnick <mal...@pointy-stick.com>:

> Okay, everybody (or, at least, some people) are putting way to much
> thought into this. Stop now.
>
> I've already worked around it in queryset-refactor so that we never
> generate this type of query. It was used in a test and as a way to avoid
> incorrect results in one other place that I've now changed. It's a
> complete non-issue now that I know what the bug is.

Malcolm, wouldn't it be reasonable to just call "SET SQL_AUTO_IS_NULL
= 0" whenever a connection is established? It would prevent people
from hitting this bug even when using custom queries (like
QuerySet.extra) and would eliminate any need of workarounds as django
itself does not depend on such quirks and oddities in SQL behaviour.

Malcolm Tredinnick

unread,
Dec 20, 2007, 11:14:27 PM12/20/07
to django-d...@googlegroups.com

On Fri, 2007-12-21 at 03:22 +0100, Patryk Zawadzki wrote:
[...]

> Malcolm, wouldn't it be reasonable to just call "SET SQL_AUTO_IS_NULL
> = 0" whenever a connection is established? It would prevent people
> from hitting this bug even when using custom queries (like
> QuerySet.extra) and would eliminate any need of workarounds as django
> itself does not depend on such quirks and oddities in SQL behaviour.

Since we establish a new connection for every front-end request, it's
worth keeping any initialisation stuff down to a minimum, so, no, I
don't think this is necessarily a good idea. I'm also a little reluctant
to take configuration control out of people's hands like that (if we
enforce it inside Django, it's difficult to turn off and that's when you
find out a couple people rely on that behaviour for some reason).

Fortunately, my thinking here isn't burning any bridges. If somebody
wanted to test that out, or use it by default, they can use the database
initialisation options via DATABASE_OPTIONS in settings (see [1]). Both
"read_default_file" and "init_command" are useful there.

I'm not saying this should be "no, forever", but right now I'm not
convinced it's worth doing this. Balanced against that is how to let
people know that it might happen... *shrug*.. I might add something to
databases.txt, for a start. I hate this sort of balancing act. :-(

[1]
http://www.djangoproject.com/documentation/databases/#connecting-to-the-database

Regards,
Malcolm

--
Honk if you love peace and quiet.
http://www.pointy-stick.com/blog/

Reply all
Reply to author
Forward
0 new messages