south migration: "database backend does not accept 0 as a value for AutoField" (mysql)

Question

I'm new to django and trying to have a Foreign key back to users for an assignee and reporter. But when i'm trying to apply the change with South i get the error

ValueError: The database backend does not accept 0 as a value for AutoField.

My Model Code:

class Ticket(models.Model):
    title = models.CharField(max_length=80)
    text = models.TextField(blank=True)
    prioritys = models.ForeignKey(Prioritys)
    ticket_created = models.DateTimeField(auto_now_add=True)
    ticket_updated = models.DateTimeField(auto_now=True)
    assignee = models.ForeignKey(User, null=True, related_name='assignee')
    reporter = models.ForeignKey(User, null=True, related_name='reporter')

    def escaped_text(self):
        return markdown.markdown(self.text)

    def __unicode__(self):
        return self.text

Full StackTrace, please... + which ver. of django, south and mysql do U use? — akaRem, Dec 02 '13 at 13:34
@Adrián: I am having the same problem. Have you found a solution? — Magda, Mar 18 '14 at 16:06

Craynic Cai · Answer 1 · 2017-09-25T10:17:17.733

11

If this happens when you run manage.py migrate (or manage.py syncdb in old versions), the reason maybe is that you have tried to add a foreign key to a model which uses AutoField as its primary key, and use 0 as the default value. Edit the migration file and remove the argument default=0 in AddField operations. It works for me in Django 1.10.

edited Sep 25 '17 at 10:17

answered Sep 25 '17 at 10:10

Craynic Cai

368
3
11

score 3 · Answer 2 · answered Mar 18 '14 at 17:21

I wasn't using South, but I recently upgraded from Django 1.4 to 1.6 (with MySQL as db backend for both), and was getting the same ValueError when trying to save some models. I tracked it down to a field that was a recursive ForeignKey. So I had:

class Foo(models.Model):
    ...
    duplicate = models.ForeignKey('self', blank=True, null=True)
    ...

Somewhere along the line—unfortunately I'm not sure where—many of my objects had gotten the value of 0 for duplicate_id.

>>> Foo.objects.filter(duplicate_id=0).count()
2078

This didn't occur for any of my other ForeignKey fields, only the self-referential one. So I set the values of that field back to None, and this fixed the error.

>>> Foo.objects.filter(duplicate_id=0).update(duplicate=None)
2078L

Because this particular error doesn't point you to a specific field that's causing problems, in general you can check if a ForeignKey field fieldname has any 0 values:

>>> Foo.objects.filter(fieldname_id=0).count()

If this gives a non-zero result, that field probably needs to be fixed.

Yep. Exactly my problem as well. Very odd and who knows how long that has been there. — Rob Osborne, Nov 29 '14 at 17:48

score 1 · Answer 3 · answered Dec 02 '13 at 13:46

A long-long time ago there was a problem with Autofield.

https://code.djangoproject.com/ticket/17653

an interesting quotes:

A:

It seems you are trying to save 0 to a ForeignKey which points to an AutoField. But, this is illegal, as the AutoField will not accept that value and so the ForeignKey can not hold that value either.

B:

So this fix creates another problem when you need to be able to accept a value of 0 (or if you are working on a DB that already has a value of 0!) in an autofield. In my case, I need to accept an ID of zero so that my foreign key can point to zero so that a unique constraint can work properly.

It looks like you have 0 in "user"."user_id".
But again... Full StackTrace, please...

score 0 · Answer 4 · answered Feb 24 '15 at 13:19

I got the same error after upgrading to django 1.7.1 it was caused when saving a model that had a foreign key to another model that for some reason had an id starting auto increment with 0, i guess this was allowed in previous django versions, but not now.

south migration: "database backend does not accept 0 as a value for AutoField" (mysql)

4 Answers4

Linked