mysqlclient raises OperationalError instead of IntegrityError for all Constraint Violations

[mkmoisen]

Describe the bug

mysqlclient is raising OperationalError instead of IntegrityError for all constraint violations, such as not null, unique, foreign key, and check.

This is a bug because it violates the Python DB API PEP 0249. In addition, all other python drivers like mysql-connector-python, PyMySQL, cx_Oracle, psycopg2, and etc. will correctly raise IntegrityError in these cases. It seems like only mysqlclient raises OperationalError.

The Python DB API pep is clear that an IntegrityError should be raised in this case, not OperationalError. Please see the PEP here:

https://peps.python.org/pep-0249/#integrityerror

The PEP states that OperationalError is used for totally different errors not under the control of the programmer, such as running out of memory. Please read the description for OperationalError here: https://peps.python.org/pep-0249/#operationalerror

To Reproduce

import MySQLdb

db = MySQLdb.connect(user='mysql', passwd='mysql', db='mysql')

cur = db.cursor()

cur.execute('drop table foo')

cur.execute('create table foo (c1 int primary key)')

# This raises an OperationalError. It should raise an IntegrityError instead.
cur.execute('insert into foo values (null)')

cur.execute('insert into foo values (1)')
# This raises an OperationalError. It should raise an IntegrityError instead.
cur.execute('insert into foo values (1)')

Environment

MySQL Server

• Server OS (e.g. Windows 10, Ubuntu 20.04): Windows 10
• Server Version (e.g. MariaDB 10.3.16): 8.0.28

MySQL Client

• OS (e.g. Windows 10, Ubuntu 20.04): Windows 10

• Python (e.g. Homebrew Python 3.7.5): Python 3.9

• Connector/C (e.g. Homebrew mysql-client 8.0.18): pip install mysqlclient==2.1.0

[sustefil]

+1

If one wants to do retries for OperationalErrors for resilience, they must look for application/integrity errors inside OperationalError manually (like forementioned constraint violations which are not retryable)

[methane]

cur.execute('insert into foo values (1)')
# This raises an OperationalError. It should raise an IntegrityError instead.
cur.execute('insert into foo values (1)')

I see IntegrityError for this query.

See test code:
https://github.com/PyMySQL/mysqlclient/pull/579/files#diff-6e5030d96839d3fe377cf209b6cab5b0d50ae900b458248d8c24b61774117170R44-R56

And its result:
https://github.com/PyMySQL/mysqlclient/actions/runs/4402624365/jobs/7709988526

[mkmoisen]

Thanks @methane