Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 471
aehlke Avatar asked Aug 07 '12 19:08

aehlke


2 Answers

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

like image 167
supervacuo Avatar answered Nov 13 '22 00:11

supervacuo


To rule out simple stuff:

  1. Could you post your Comment model
  2. 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
like image 32
Francis Yaconiello Avatar answered Nov 12 '22 23:11

Francis Yaconiello