Odd MySQL bug

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/reg
ressiontests/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/branche
s/queryset-refactor/tests/regressiontests/null_queries/model
s.py#L27

Thanks in advance,
Malcolm

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django developers" group.
To post to this group, send email to django-developersgooglegroups.com
To unsubscribe from this group, send email to
django-developers-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en

-~----------~----~----~----~------~----~------~--~---

2007/12/19, Malcolm Tredinnick <malcolmpointy-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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django developers" group.
To post to this group, send email to django-developersgooglegroups.com
To unsubscribe from this group, send email to
django-developers-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en

-~----------~----~----~----~------~----~------~--~---

On Wed, 2007-12-19 at 14:36 +0100, Patryk Zawadzki wrote:
> 2007/12/19, Malcolm Tredinnick <malcolmpointy-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?

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-s
tick.com/blog/


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django developers" group.
To post to this group, send email to django-developersgooglegroups.com
To unsubscribe from this group, send email to
django-developers-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en

-~----------~----~----~----~------~----~------~--~---

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-s
tick.com/blog/


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django developers" group.
To post to this group, send email to django-developersgooglegroups.com
To unsubscribe from this group, send email to
django-developers-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en

-~----------~----~----~----~------~----~------~--~---

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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django developers" group.
To post to this group, send email to django-developersgooglegroups.com
To unsubscribe from this group, send email to
django-developers-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en

-~----------~----~----~----~------~----~------~--~---

Yikes!... it's a session variable only... makes it hard to
change the
default behavior...
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django developers" group.
To post to this group, send email to django-developersgooglegroups.com
To unsubscribe from this group, send email to
django-developers-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en

-~----------~----~----~----~------~----~------~--~---

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?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django developers" group.
To post to this group, send email to django-developersgooglegroups.com
To unsubscribe from this group, send email to
django-developers-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en

-~----------~----~----~----~------~----~------~--~---

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)

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django developers" group.
To post to this group, send email to django-developersgooglegroups.com
To unsubscribe from this group, send email to
django-developers-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en

-~----------~----~----~----~------~----~------~--~---

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.

On Dec 20, 4:12 pm, AmanKow <wwer...si.rr.com> 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.
>
> 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?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django developers" group.
To post to this group, send email to django-developersgooglegroups.com
To unsubscribe from this group, send email to
django-developers-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en

-~----------~----~----~----~------~----~------~--~---