Opened 13 years ago

Closed 13 years ago

Last modified 13 years ago

#16809 closed Bug (fixed)

MySQL is not SQL standards compliant regarding IS NULL criteria on newly-inserted auto-incremented fields

Reported by: jamesp Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords: mysql, sql standards, auto_increment, count
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

MySQL has a setting which allows an auto-increment column to be returned on a newly-inserted row when tested for NULL. This is enabled by default prior to MySQL 5.6.

This is an edge case in Django, which caused the following tests for #15316 to fail:

  • test_ticket15316_exclude_false
  • test_ticket15316_one2one_exclude_false

To correct this behavior, the following statement must be issued, once per connection:

SET SQL_AUTO_IS_NULL = 0;

Re: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_auto_is_null

Caveats:

This setting was introduced in MySQL 4.1 and is disabled in MySQL 5.6. This patch does not check the version of the database, and the documentation currently indicates that MySQL 3.23 may be used with Django. Please advise if version checking ought to be rolled into the provided patch.

Attachments (1)

mysql-set-sql_auto_is_null.patch (2.6 KB ) - added by jamesp 13 years ago.

Download all attachments as: .zip

Change History (5)

by jamesp, 13 years ago

comment:1 by jamesp, 13 years ago

Clarification on status of setting in MySQL 5.6. The setting is available but it is disabled by default. Execution will not cause an error.

Test suite executed in MySQL 5.1.41, 116 tests passed successfully with 1 expected failure.

comment:2 by jamesp, 13 years ago

Ignore what I said about the versions caveat as this setting is available in 3.23, 4.0, 4.1, and following.

comment:3 by Russell Keith-Magee, 13 years ago

Triage Stage: UnreviewedReady for checkin

comment:4 by Russell Keith-Magee, 13 years ago

Resolution: fixed
Status: newclosed

In [16785]:

Fixed #16809 -- Forced MySQL to behave like a database. This avoids a problem where queries that do IS NONE checks can return the wrong result the first time they are executed if there is a recently inserted row. Thanks to James Pyrich for the debug work and patch.

Note: See TracTickets for help on using tickets.
Back to Top