Foo.objects.get(id=None) returns Foo instance, sometimes

Question

I have this code: ``` try: parent_comment = models.Comment.all_objects.get(id=parent_comment_id) except models.Comment.DoesNotExist: parent_comment = None if parent_comment is not None and parent_comment_id is None: raise Exception("WTF django/mysql") ``` ... and sometimes, the exception somehow gets raised. How could this ever happen? Once in a while, several times a day, it returns seemingly random Comment instances. Usually it behaves as expected and returns None. This is the id field of the Comment table: `id int(11) NOT NULL AUTO_INCREMENT` so it's not that it's nullable. It's an InnoDB table. As for Comment.all_objects, this is its definition: `all_objects = Manager()` and it's the first line in that class. We're on Django 1.2.7. Update Added logging to the exception to get the SQL that's generated when the exception is raised. Here it is: ``` SELECT `canvas_comment`.`id`, `canvas_comment`.`visibility`, `canvas_comment`.`parent_content_id`, `canvas_comment`.`parent_comment_id`,…

Accepted Answer

This behaviour is caused by deeply strange (in this coder's humble opinion) MySQL behaviour, controlled by the `SQL_AUTO_IS_NULL` variable (which is `1` by default in MySQL < 5.5.3):

If this variable is set to 1, then after a statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form:

```
SELECT * FROM tbl_name WHERE auto_col IS NULL
```

If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID() function

There is a Django bug (closed: wontfix) describing similar confusion caused by this "feature", in which a core developer states

If you don't want that behaviour, you should configure your database to do the right thing for your preferences

The solution, then, is to disable the `SQL_AUTO_IS_NULL` option of your MySQL database using the `SET` statement. You can do this in `settings.py` with something like:

```
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        # ... your DB options
        'OPTIONS': {
            'init_command': 'SET SQL_AUTO_IS_NULL=0;'
        },
    }
}
```

Longer-term, you can try and drum-beat on the django-developers list to get them to reconsider their earlier position:

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 ... 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. :-(

Foo.objects.get(id=None) returns Foo instance, sometimes

Solution

Problem