Bug Report

Description

• Some users have been getting an IntegrityError 1217 when calling delete on an entry in a table with populated children
• The same user is able to manually delete each child table entry, and then delete the original entry once all children are removed
• The issue comes from the IntegrityError from delete_quick not being caught in the cascade function in delete, which is why all the children need deleted before can delete a table.
• The reason it's not getting caught is that datajoint is expecting the error code for ROW_IS_REFERENCED_2 (1451) but is instead getting the error code ROW_IS_REFERENCED (1217). The ROW_IS_REFERENCED error also returns less information about the failing table

Reproducibility

• Linux

• Python Version: 3.9.16

• MySQL Version: 8.0.34

• MySQL Deployment Strategy: remote

• DataJoint Version: 0.14.1

• Minimum number of steps to reliably reproduce the issue:
  Error only occurs for some users. Occurs when calling TableName.delete({restriction}) on a table with child tables populated for the restriction

• Example error stack

IntegrityError Traceback (most recent call last)
Cell In[2], line 1
----> 1 Session().delete({"nwb_file_name":nwb_file_name})

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/datajoint/table.py:588, in Table.delete(self, transaction, safemode, force_parts)
586 # Cascading delete
587 try:
--> 588 delete_count = cascade(self)
589 except:
590 if transaction:

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/datajoint/table.py:506, in Table.delete..cascade(table)
504 for _ in range(max_attempts):
505 try:
--> 506 delete_count = table.delete_quick(get_count=True)
507 except IntegrityError as error:
508 match = foreign_key_error_regexp.match(error.args[0]).groupdict()

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/datajoint/table.py:465, in Table.delete_quick(self, get_count)
460 """
461 Deletes the table without cascading and without user prompt.
462 If this table has populated dependent tables, this will fail.
463 """
464 query = "DELETE FROM " + self.full_table_name + self.where_clause()
--> 465 self.connection.query(query)
466 count = (
467 self.connection.query("SELECT ROW_COUNT()").fetchone()[0]
468 if get_count
469 else None
470 )
471 self._log(query[:255])

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/datajoint/connection.py:346, in Connection.query(self, query, args, as_dict, suppress_warnings, reconnect)
344 cursor = self._conn.cursor(cursor=cursor_class)
345 try:
--> 346 self._execute_query(cursor, query, args, suppress_warnings)
347 except errors.LostConnectionError:
348 if not reconnect:

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/datajoint/connection.py:302, in Connection._execute_query(cursor, query, args, suppress_warnings)
300 cursor.execute(query, args)
301 except client.err.Error as err:
--> 302 raise translate_query_error(err, query)

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/datajoint/connection.py:300, in Connection._execute_query(cursor, query, args, suppress_warnings)
297 if suppress_warnings:
298 # suppress all warnings arising from underlying SQL library
299 warnings.simplefilter("ignore")
--> 300 cursor.execute(query, args)
301 except client.err.Error as err:
302 raise translate_query_error(err, query)

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/pymysql/cursors.py:158, in Cursor.execute(self, query, args)
154 pass
156 query = self.mogrify(query, args)
--> 158 result = self._query(query)
159 self._executed = query
160 return result

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/pymysql/cursors.py:325, in Cursor._query(self, q)
323 conn = self._get_db()
324 self._clear_result()
--> 325 conn.query(q)
326 self._do_get_result()
327 return self.rowcount

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/pymysql/connections.py:549, in Connection.query(self, sql, unbuffered)
547 sql = sql.encode(self.encoding, "surrogateescape")
548 self._execute_command(COMMAND.COM_QUERY, sql)
--> 549 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
550 return self._affected_rows

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/pymysql/connections.py:779, in Connection._read_query_result(self, unbuffered)
777 else:
778 result = MySQLResult(self)
--> 779 result.read()
780 self._result = result
781 if result.server_status is not None:

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/pymysql/connections.py:1157, in MySQLResult.read(self)
1155 def read(self):
1156 try:
-> 1157 first_packet = self.connection._read_packet()
1159 if first_packet.is_ok_packet():
1160 self._read_ok_packet(first_packet)

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/pymysql/connections.py:729, in Connection._read_packet(self, packet_type)
727 if self._result is not None and self._result.unbuffered_active is True:
728 self._result.unbuffered_active = False
--> 729 packet.raise_for_error()
730 return packet

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/pymysql/protocol.py:221, in MysqlPacket.raise_for_error(self)
219 if DEBUG:
220 print("errno =", errno)
--> 221 err.raise_mysql_exception(self._data)

File ~/mambaforge-pypy3/envs/spyglass/lib/python3.9/site-packages/pymysql/err.py:143, in raise_mysql_exception(data)
141 if errorclass is None:
142 errorclass = InternalError if errno < 1000 else OperationalError
--> 143 raise errorclass(errno, errval)

IntegrityError: (1217, 'Cannot delete or update a parent row: a foreign key constraint fails')

Expected Behavior

A deletion of the row entry and all dependent entries in child tables

Additional Research and Context

I'm not entirely sure where the change is in MySQL, but this seems related to this post here