MySQL error code 1062 (duplicate entry for key) raises MySQLdb.IntegrityError, not django.db.IntegrityError

[Simon Willison]

If you have a UNIQUE KEY on a MySQL table and you attempt to create a duplicate row, Django raises an IntegrityError... but it's a MySQLdb.IntegrityError, not a django.db.IntegrityError.

For example, consider a user model with an other_system_id column that is created as a unique key - a model that looks something like this:

class User(models.Model):
    other_system_id = models.CharField(max_length=32, unique=True)

If you create a row with other_system_id="142" and then try to create a duplicate, this happens:

In [4]: try:
   ...:     u = User.objects.create(other_system_id="142")
   ...: except Exception as e:
   ...:     print(e, e.__class__)
   ...:     
(IntegrityError(1062, "Duplicate entry '142' for key 'other_system_id='"), <class '_mysql_exceptions.IntegrityError'>)

Note that this is NOT a django.db.IntegrityError - it's a MySQLdb.IntegrityError. This is confusing (we just spent a while debugging this, since as far as we could tell an IntegrityError was being raised but not caught).

It looks to me like the fix for this would be to add code 1062 ("Duplicate entry for key") to the codes_for_integrityerror set in the MySQLdb backend: ​https://github.com/django/django/blob/dd82f3327124fd2762cf6df2ac8c6380772bf127/django/db/backends/mysql/base.py#L60-L63

Until 11 months ago that set contained just 1048 ("Column cannot be null") - then in ​https://github.com/django/django/commit/dd82f3327124fd2762cf6df2ac8c6380772bf127 we added 1690, ("BIGINT UNSIGNED value is out of range") to fix #27979

Is there any reason we shouldn't also catch 1062 ("Duplicate entry for key") and convert that into a django.db.IntegrityError exception?

[Simon Willison]

Note that this affects Django 1.11x (and previous versions), not just Django 2.x - but since fixing this would be a backwards incompatible change for everyone who is currently catching MySQLdb.IntegrityError presumably it wouldn't make sense to fix this in 1.11x

[Claude Paroz]

IntegrityError test

[Claude Paroz]

I wasn't able to reproduce with the above test. Could you check on your system?

[Tim Graham]

I also can't reproduce. The code to convert _mysql_exceptions.IntegrityError to django.db.IntegrityError is in ​django/db/utils.py.