Error on cascade delete in MySQL 8

samuelbray32 commented 1 year ago

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

lfrank commented 1 year ago

@dimitri-yatsenko Just checking in on this, as it's a bit of a challenge for users not to be able to delete entries from our comment tables. Any ideas?

dimitri-yatsenko commented 1 year ago

fixed in #1112

CBroz1 commented 11 months ago

Unfortunately, errors persist for users with insufficient privileges. I did not see the error when testing with the first account listed below, but I do see it with the second.

mysql> show grants for 'cbroz';
+------------------------------------------------------------+
| Grants for cbroz@%                                         |
+------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON `%`.* TO `cbroz`@`%`               |
...
mysql> show grants for 'cbroz-basic';
+------------------------------------------------------------+
| Grants for cbroz-basic@%                                   |
+------------------------------------------------------------+
| GRANT SELECT ON `%`.* TO `cbroz-basic`@`%`                 |

When DataJoint catches MySQL 8 error 1217, MySQL is not giving sufficient information to continue the cascade:

Cell In[4], line 1
----> 1 from spyglass.common import IntervalList; (IntervalList & 'nwb_file_name LIKE "SC3820230606_.nwb%"').delete()

File ~/wrk/datajoint-python/datajoint/table.py:586, in Table.delete(self, transaction, safemode, force_parts)
    584 # Cascading delete
    585 try:
--> 586     delete_count = cascade(self)
    587 except:
    588     if transaction:

File ~/wrk/datajoint-python/datajoint/table.py:506, in Table.delete.<locals>.cascade(table)
    504     delete_count = table.delete_quick(get_count=True)
    505 except IntegrityError as error:
--> 506     match = foreign_key_error_regexp.match(error.args[0]).groupdict()
    507     if "`.`" not in match["child"]:  # if schema name missing, use table
    508         match["child"] = "{}.{}".format(
    509             table.full_table_name.split(".")[0], match["child"]
    510         )

AttributeError: 'NoneType' object has no attribute 'groupdict'

What would you suggest @dimitri-yatsenko? Is there a way to continue the cascade without granting additional privileges for the user?

dimitri-yatsenko commented 1 month ago

@CBroz1 Thanks for your patience. Is this still a problem. I am in a good place to fix this.

dimitri-yatsenko commented 1 month ago

@samuelbray32 Thanks for your patience. Is this still a problem? I am in a good place to fix this.

CBroz1 commented 1 month ago

Thanks for your attention to this @dimitri-yatsenko. Yes, we've unfortunately had to elevate the permissions of many users as a result of this issue. When I last tested, I was not able to replicate in a mysql 8 docker container, so I suspect it is related to the upgrade process for 5 -> 8

I've posted to multiple forums without solid leads

datajoint / datajoint-python