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

Question

I have a model:

class Tour(models.Model):
    INACTIVE = 0
    ACTIVE = 1
    ARCHIVE = 2

    STATUS = (
        (INACTIVE, "Inactive"),
        (ACTIVE, "Active"),
        (ARCHIVE, "Archive"),
    )

    AIR_TOUR = 0
    GROUND_TOUR = 1
    SEA_TOUR = 2

    T_TYPES = (
        (AIR_TOUR, "Air_tour"),
        (GROUND_TOUR, "Ground_tour"),
        (SEA_TOUR, "Sea_tour"),
    )

    title = models.CharField(max_length=200)
    tour_from = models.ForeignKey(Airport, related_name='from_location')
    tour_to = models.ForeignKey(Airport, related_name='to_location')
    duration_day = models.IntegerField(default=1, blank=True, null=True)
    duration_night = models.IntegerField(default=1, blank=True, null=True)
    transport_type = models.IntegerField(default=AIR_TOUR, choices=T_TYPES, blank=True, null=True)
    documents = models.TextField(default='', blank=True, null=True)
    description = models.TextField(blank=True, null=True, default='')
    services = models.TextField(blank=True, null=True, default='')

    airline = models.ForeignKey(Airline, null=True, blank=True)
    train = models.ForeignKey(Train, null=True, blank=True)
    bus = models.ForeignKey(Bus, null=True, blank=True)

    user = models.ForeignKey(User, related_name='user')
    creator = models.ForeignKey(User, related_name='creator')
    status = models.IntegerField(default=INACTIVE, choices=STATUS, blank=True, null=True)
    create_at = models.DateTimeField(default='2000-10-10')
    update_at = models.DateTimeField(default='2000-10-10')

I try to change status to ARCHIVE by this code:

test = Tour.objects.get(id=self.kwargs['pk'])
test.status = ARCHIVE
test.save()

and get this error:

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

status field is not AutoField. Why this error happend?

UPDATE:

full stack trace:

  Django version 1.11, using settings 'project.settings_local'
Starting development server at http://0.0.0.0:8888/
Quit the server with CONTROL-C.
Internal Server Error: /dashboard/tour/delete/1/
Traceback (most recent call last):
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/core/handlers/exception.py", line 41, in inner
    response = get_response(request)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/core/handlers/base.py", line 187, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/core/handlers/base.py", line 185, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/contrib/auth/decorators.py", line 23, in _wrapped_view
    return view_func(request, *args, **kwargs)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/views/generic/base.py", line 68, in view
    return self.dispatch(request, *args, **kwargs)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/views/generic/base.py", line 88, in dispatch
    return handler(request, *args, **kwargs)
  File "/home/py/project/dashboard/views_tour.py", line 196, in post
    tour.save()
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/base.py", line 806, in save
    force_update=force_update, update_fields=update_fields)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/base.py", line 836, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/base.py", line 903, in _save_table
    forced_update)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/base.py", line 953, in _do_update
    return filtered._update(values) > 0
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/query.py", line 661, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1191, in execute_sql
    cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 863, in execute_sql
    sql, params = self.as_sql()
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1157, in as_sql
    val = field.get_db_prep_save(val, connection=self.connection)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/fields/related.py", line 963, in get_db_prep_save
    return self.target_field.get_db_prep_save(value, connection=connection)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/fields/__init__.py", line 766, in get_db_prep_save
    prepared=False)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/fields/__init__.py", line 955, in get_db_prep_value
    value = connection.ops.validate_autopk_value(value)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/backends/mysql/operations.py", line 155, in validate_autopk_value
    raise ValueError('The database backend does not accept 0 as a '
ValueError: The database backend does not accept 0 as a value for AutoField.
Internal Server Error: /dashboard/tour/delete/1/
Traceback (most recent call last):
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/core/handlers/exception.py", line 41, in inner
    response = get_response(request)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/core/handlers/base.py", line 187, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/core/handlers/base.py", line 185, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/contrib/auth/decorators.py", line 23, in _wrapped_view
    return view_func(request, *args, **kwargs)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/views/generic/base.py", line 68, in view
    return self.dispatch(request, *args, **kwargs)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/views/generic/base.py", line 88, in dispatch
    return handler(request, *args, **kwargs)
  File "/home/py/project/dashboard/views_tour.py", line 196, in post
    tour.save()
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/base.py", line 806, in save
    force_update=force_update, update_fields=update_fields)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/base.py", line 836, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/base.py", line 903, in _save_table
    forced_update)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/base.py", line 953, in _do_update
    return filtered._update(values) > 0
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/query.py", line 661, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1191, in execute_sql
    cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 863, in execute_sql
    sql, params = self.as_sql()
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1157, in as_sql
    val = field.get_db_prep_save(val, connection=self.connection)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/fields/related.py", line 963, in get_db_prep_save
    return self.target_field.get_db_prep_save(value, connection=connection)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/fields/__init__.py", line 766, in get_db_prep_save
    prepared=False)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/models/fields/__init__.py", line 955, in get_db_prep_value
    value = connection.ops.validate_autopk_value(value)
  File "/home/.virtualenvs/project/lib/python3.6/site-packages/django/db/backends/mysql/operations.py", line 155, in validate_autopk_value
    raise ValueError('The database backend does not accept 0 as a '
ValueError: The database backend does not accept 0 as a value for AutoField.

Answer 1

Check your django_content_type table, look for 0's in the id column. Django logs database operations after each call, and it writes the ID of the content type.

I found that I had several entries with a 0 id. Updating these with unique numbers after the end of the sequence and restarting Django fixed this for me.

Honestly, I have no idea how it got that way. I'm using Django 2.2.

Answer 2

You likely have a foreign key field that contains a 0 (instead of null, or a valid value). If you don't have the proper foreign key constraints at the DB level (or you turned off foreign key checks when you added the constraints), there is nothing preventing invalid foreign key references from existing in that row.

Check the data in the all your foreign key fields for a 0: tour_from, tour_to, airline, train, bus, user, creator.

Answer 3

What you're doing should work. Since the object is already in the database, it's strange that updating it with the same fields leads to a 0 somewhere (probably one of the foreign keys).

You should debug this and there are two ways to go at it:

• Inspect your database using raw SQL. manage.py dbshell opens a SQL shell where you can do SELECT * FROM my_app_tour WHERE id = 1. Look at the particular row, maybe you can spot the issue.
• Try test.save(update_fields=['status']). If that works, add more fields to update one by one, e.g. test.save(update_fields=['status', 'tour_from'], then test.save(update_fields['status', 'tour_from', 'tour_to'])... until the error is thrown again.

I suspect a database corruption, but there might be some other more serious issue in the way you create the objects. That would cause problems down the line.