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`, `canvas_comment`.`timestamp`, `canvas_comment`.`reply_content_id`, `canvas_comment`.`reply_text`, `canvas_comment`.`replied_comment_id`, `canvas_comment`.`category_id`, `canvas_comment`.`author_id`, `canvas_comment`.`title`, `canvas_comment`.`ip`, `canvas_comment`.`anonymous`, `canvas_comment`.`score`, `canvas_comment`.`judged`, `canvas_comment`.`ot_hidden` FROM `canvas_comment` WHERE `canvas_comment`.`id` IS NULL

Where did you define the parent_comment_id on the 5th line? I can't find it in your code. — zs2020, Aug 07 '12 at 20:41
@sza it's irrelevant - I'm asking about how the exception is able to be raised, which happens when parent_comment_id is None. — aehlke, Aug 07 '12 at 20:42
Ok. But if you haven't defined it or assigned it with a value before, it is definitely None. Can you paste the code relating the parent_comment_id assignment? Thanks! — zs2020, Aug 07 '12 at 20:47
@sza python doesn't work that way - if it hasn't been defined before, it would raise a NameError exception. — aehlke, Aug 07 '12 at 20:50
Perhaps you could boil it down to the simplest test-case: `assertRaises(models.Comment.DoesNotExist, models.Comment.all_objects.get, id=None)` to see if the problem still occurs? — supervacuo, Aug 10 '12 at 17:51
@supervacuo I don't see how that will help. That assert will fail whenever this exception gets raised, because that's equivalent to what my code is doing. — aehlke, Aug 10 '12 at 19:27
@aehlke I just wanted to make sure that was *actually* what it was doing. Which (according to your SQL) it definitely is. Hmm. — supervacuo, Aug 10 '12 at 19:31

supervacuo · Accepted Answer · 2012-08-10T20:27:55.657

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

score 1 · Answer 2 · answered Aug 10 '12 at 17:55

To rule out simple stuff:

Could you post your Comment model
Could you run the following query against your DB

SELECT COUNT(id) FROM <your_comment_table> WHERE id <= 0 OR id ='' OR id IS NULL

Your code logically should work barring something wonky in your model code, which leads me to believe that it must be data related.

EDIT

another idea, see what django's ORM is querying (this should tell us whats up):

parent_comment = models.Comment.all_objects.get(id=parent_comment_id)
raise Exception(parent_comment.query) # should display out the query thats being generated

Your query returns 0 rows. Added the generated SQL to the question. — aehlke, Aug 10 '12 at 19:23

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

2 Answers2

EDIT