Error on cascade delete in MySQL 8

[samuelbray32]

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]

@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]

fixed in #1112

[CBroz1]

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]

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

[dimitri-yatsenko]

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

[CBroz1]

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

• Stack Exchange
• Stack Overflow
• MySQL bugs forum

[Hendriela]

Hi,
I think I have encountered the same problem. I noticed that delete() calls for entries that have dependencies (master-part or other downstream tables) would fail with this error traceback:

(test_schema.TestMaster() & dict(test_id=0)).delete()

Traceback (most recent call last):
  File "...\Lib\site-packages\datajoint\table.py", line 521, in cascade
    delete_count = table.delete_quick(get_count=True)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "..\Lib\site-packages\datajoint\table.py", line 476, in delete_quick
    self.connection.query(query)
  File "...\Lib\site-packages\datajoint\connection.py", line 350, in query
    self._execute_query(cursor, query, args, suppress_warnings)
  File "...\Lib\site-packages\datajoint\connection.py", line 306, in _execute_query
    raise translate_query_error(err, query)
datajoint.errors.IntegrityError: Cannot delete or update a parent row: a foreign key constraint fails

During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "...\Lib\site-packages\IPython\core\interactiveshell.py", line 3549, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-55-160acee45352>", line 1, in <module>
    (alm_test.TestMaster() & dict(test_id=0)).delete()
  File "...\Lib\site-packages\datajoint\table.py", line 625, in delete
    delete_count = cascade(self)
                   ^^^^^^^^^^^^^
  File "...\Lib\site-packages\datajoint\table.py", line 523, in cascade
    match = foreign_key_error_regexp.match(error.args[0]).groupdict()
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'NoneType' object has no attribute 'groupdict'

Especially the last part pointed towards Datajoint using the MySQL error message to resolve cascades.
Deleting with the root user worked, as well as granting ALL PRIVILEGES, but deleting with a lower-privilege account adm did not, these privileges were not enough:

GRANT SELECT, INSERT, UPDATE, CREATE, ALTER, DELETE, DROP, REFERENCES ON `test\_%`.* TO 'adm'@'%'

So I went through all possible MySQL privileges, revoking them one-by-one, to find that these four additional privileges are required for the delete() to properly cascade: CREATE VIEW, SHOW VIEW, INDEX, TRIGGER
As far as I understand, these privileges should be unrelated to the delete(), but removing each of those makes the delete() fail with the abovementioned error.

I did not go through the code completely, but it may be related to the fact that MySQL restricted the verbosity of error messages shown to users with insufficient privileges for security reasons. If Datajoint would be using the error messages to resolve dependencies, it may explain why seemingly unrelated privileges are necessary for the delete() to cascade.

Anyway, this is just an idea of mine. But the error was very opaque and hard to track down, so maybe either the delete cascade process or error message could be improved.

System:
MySQL hosted on Ubuntu 24.04
MySQL 8.0.21 (from Docker image datajoint/mysql:8.0)
Python 3.12.9
Datajoint 0.14.4

Feel free to reach out again for more details/minimal reproducible example.

[dimitri-yatsenko]

Thank you. We have improved the error message in the upcoming release but the problems remains related to insufficient privileges and how MySQL handles them.

[Hendriela]

Hi Dimitri,
thanks for replying so quickly, and good to hear that this issue is in your view.
An improved error message would surely already help. It took a few days for me to figure out that the problem was privilege-related and not schema structure related (as the error seemingly suggested, mentioning foreign key constraints), and then some hours to exactly find the missing/necessary privileges, since they have no obvious relation to the delete() (and I did not want to grant ALL PRIVILEGES to anyone needing to delete entries).

I am wondering if I am correct in my assumption that Datajoint does use the MySQL error message to resolve dependencies during delete cascades? Could this problem be circumvented by tracking/resolving foreign keys for deletes differently, where it does not rely on parsing error messages?

[CBroz1]

Feel free to reach out again for more details/minimal reproducible example.

Hi @Hendriela - I was starting to worry our database was the only one to experience this issue! I tried on and off for some time, but I wasn't able to create a minimal reproduction that included a low-privilege user (a) attempting to delete an entry on a schema for which they had privileges and (b) getting the low-verbosity error. Specifically, I've seen this error when the user had privileges to the blocking table, but not other downstream dependencies. Is that something you're able to replicate?

Even in my attempted replication repo, results were inconsistent - appearing on some runs but not others. We have had to resort to granting ALL to most users for our production database