#157 Wrong exception type generated on foreign key violation

[Anonymous]

Using Python 2.3 and (debian linux) mysqldb 1.2.1c2-1:

Generated a table with a foreign key constraint, and intentionally
violated the constraint during testing. Output was:

Exception in thread Thread-1:Traceback (most recent call last):
[useless stuff removed]
File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line
137, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.3/site-packages/MySQLdb/
connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1216, 'Cannot add or update a child row: a
foreign key constraint fails')

According to the Python DB API 2.0 (http://www.python.org/peps/
pep-0249.html), a foreign key constraint violation should throw an
IntegrityError, not an OperationalError. (relevant paragraphs
quoted below for convenience)

OperationalError

        Exception raised for errors that are related to the
        database's operation and not necessarily under the control
        of the programmer, e.g. an unexpected disconnect

occurs,
the data source name is not found, a transaction could
not
be processed, a memory allocation error occurred during
processing, etc. It must be a subclass of DatabaseError.

IntegrityError

        Exception raised when the relational integrity of the
        database is affected, e.g. a foreign key check fails.  It
        must be a subclass of DatabaseError.

As it is, I can't tell in my code (without examining the text string
returned in the exception) whether a commit failed because of
bogus data, or because of a database disconnect, which makes it
hard to decide how to proceed... :(

(submitter: John Martinez - 'jvm at snarkhunter dot com')

Thanks for your time.

[Andy Dustman]

Logged In: YES
user_id=71372

OK. I think this is a relatively new error. I'll update this
in the next release. OperationalError is used for anything I
didn't specifically catch for something else, i.e. there are
a couple specific codes it looks for for IntegrityError, and
this one should be added.

[Andy Dustman]

Logged In: YES
user_id=71372

Should appear in 1.2.1c4, plus some others.

[Elvis Pfutzenreuter]

Logged In: YES
user_id=393913

BTW the additional error codes that should raise
IntegrityError instead of OperationalError are:

ER_ROW_IS_REFERENCED
ER_ROW_IS_REFERENCED_2 <- I discovered the problem via this
one (error 1451)
ER_NO_REFERENCED_ROW
ER_NO_REFERENCED_ROW_2

[Andy Dustman]

Logged In: YES
user_id=71372

OK, the _2 errors did not make it in 1.2.1 as I was unaware
of them. I'll put them in _p3.

[Andy Dustman]

Logged In: YES
user_id=71372

Fixed in development version; please test 1.2.2b1 when it is
released (mysql-python-test).