Django multi-table inheritance: duplicate primary key error on edit

[louisrigot]

Description

When using multi-table inheritance in Django 3.2, editing objects triggers an OperationalError: duplicate primary key given error. The edit succeeds behind the scenes. This works with MySQL 8.0.11.

Steps to reproduce

This is a minimal test case to reproduce:

app/models.py:

class Base(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=255)


class Child(Base):
    extra_name = models.CharField(max_length=255)

Shell:

>>> from app.models import Child
>>> c = Child(name='foo', extra_name='bar')
>>> c.save()
>>> print(c)
Child object (4d552fb0-54e3-44c0-a90e-7ff4c032c348)
>>> c.name='baz'
>>> c.save()
Traceback (most recent call last):
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1105, 'duplicate primary key given: ("4d552fb054e344c0a90e7ff4c032c348")')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/models/base.py", line 726, in save
    self.save_base(using=using, force_insert=force_insert,
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/models/base.py", line 763, in save_base
    updated = self._save_table(
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/models/base.py", line 868, in _save_table
    results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/models/base.py", line 906, in _do_insert
    return manager._insert(
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1270, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1410, in execute_sql
    cursor.execute(sql, params)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/Users/lrigot/dev/dolt_project/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1105, 'duplicate primary key given: ("4d552fb054e344c0a90e7ff4c032c348")')

Offending query in Dolt log:

TRACE: received query INSERT INTO `app_child` (`base_ptr_id`, `extra_name`) VALUES ('4d552fb054e344c0a90e7ff4c032c348', 'bar')
DEBUG: executing query
TRACE: got error duplicate primary key given: ("4d552fb054e344c0a90e7ff4c032c348")
TRACE: received query rollback

This INSERT is never attempted when connected to MySQL proper, I'm guessing a result of a previous query tricks Django into attempting an INSERT.

Workaround

By setting select_on_save = True in Child.Meta, restoring pre-1.6 behavior, no error is triggered:
https://docs.djangoproject.com/en/3.2/ref/models/options/#select-on-save

app/models.py

class Base(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=255)


class Child(Base):
    extra_name = models.CharField(max_length=255)

    class Meta:
        select_on_save = True

[VinaiRachakonda]

I'm reading into this more and looks like on the second save Django should be doing an UPDATE instead of INSERT cc -> https://docs.djangoproject.com/en/3.2/ref/models/instances/#what-happens-when-you-save

I'm curious why in this case Django evaluated to doing an INSERT. We'll need to dig into this more on our end. I do believe however adding force_update=True on save would solve your problem as well.

Thank you for this bug!