Error on cascade delete in MySQL 8

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

@dimitri-yatsenko

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

fixed in #1112

@dimitri-yatsenko

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?

@CBroz1

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

@samuelbray32

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

@dimitri-yatsenko

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

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.

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.

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?

@Hendriela

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

I did not clone your replication repo, but this is a minimal reproducible example covering all cases on my side:

import datajoint as dj
schema = dj.schema('test_schema', locals(), create_tables=True)

@schema
class TestAlone(dj.Manual):
    definition = """    # Standalone table
    test_pk     : int unsigned          
    ---
    """

@schema
class TestMaster(dj.Manual):
    definition = """    # Test master table
    test_id     : int unsigned          
    ---
    """

    class TestPart(dj.Part):
        definition = """ # Stores the weights
        -> master
        ---
        test_attr                      : int          
        """

@schema
class TestDownstream(dj.Manual):
    definition = """    # Downstream table
    -> TestMaster
    ---
    """

The test user

CREATE USER 'test_user'@'%' IDENTIFIED BY 'test123';
GRANT SELECT, INSERT, UPDATE, CREATE, ALTER, DELETE, DROP, REFERENCES ON `test\_schema`.* TO 'test_user'@'%';

should in my understanding have enough privileges to delete entries with downstream dependencies. However, with these "basic" privileges, the following happens:

Deleting entries from TestAlone works
Deleting entries from TestMaster works only if the entry does not have a related entry in TestPart or TestDownstream
Deleting entries from TestMaster, if the entry has a related entry in TestPart or TestDownstream, yields the error:

(test.TestMaster() & 'test_id=0').delete()

Traceback (most recent call last):
  File "...\lib\site-packages\IPython\core\interactiveshell.py", line 3577, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-25-3b727e06d7f5>", line 1, in <module>
    (test.TestMaster() & 'test_id=0').delete()
  File "...\lib\site-packages\datajoint\table.py", line 561, in delete
    delete_count = cascade(self)
  File "...\lib\site-packages\datajoint\table.py", line 479, in cascade
    delete_count = table.delete_quick(get_count=True)
  File "...\lib\site-packages\datajoint\table.py", line 453, in delete_quick
    self.connection.query(query)
  File "...\lib\site-packages\datajoint\connection.py", line 340, in query
    self._execute_query(cursor, query, args, suppress_warnings)
  File "...\lib\site-packages\datajoint\connection.py", line 296, in _execute_query
    raise translate_query_error(err, query)
  File "...\lib\site-packages\datajoint\connection.py", line 294, in _execute_query
    cursor.execute(query, args)
  File "...\lib\site-packages\pymysql\cursors.py", line 153, in execute
    result = self._query(query)
  File "...\lib\site-packages\pymysql\cursors.py", line 322, in _query
    conn.query(q)
  File "...\lib\site-packages\pymysql\connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "...\lib\site-packages\pymysql\connections.py", line 822, in _read_query_result
    result.read()
  File "...\lib\site-packages\pymysql\connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
  File "...\lib\site-packages\pymysql\connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "...\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "...\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1217, 'Cannot delete or update a parent row: a foreign key constraint fails')

Note that this test was performed with Datajoint 0.13.7, my post above with 0.14.4. The error stack looks different, but both are cryptic and do not point to the actual issue.

After granting all of these additional privileges:
GRANT CREATE VIEW, SHOW VIEW, INDEX, TRIGGER ON test_schema.* TO 'test_user'@%;
deleting entries from TestMaster, if the entry has a related entry, works as expected:

(test.TestMaster() & 'test_id=0').delete()
[2025-06-13 15:53:57,078][INFO]: Deleting 1 rows from `test_schema`.`test_downstream`
[2025-06-13 15:53:57,100][INFO]: Deleting 1 rows from `test_schema`.`test_master`
Commit deletes? [yes, No]:

Based on my investigation, these four privileges are the minimum requirements for the delete to cascade. I have not tried out permutations of the original permissions, but those are pretty obvious in their use case on their own.

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?

As you can see in this example, test_user should have privileges to all tables involved, so the error is falsely raised. If a user would have only access to the upstream TestMaster, but not the downstream TestDownstream or other even more downstream tables, my instinct would say that this user should NOT be able to delete entries in TestMaster, since this would cause DELETEs in TestDownstream, which the user is not supposed to touch. So in this case the error should be raised.

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

I am not 100% if you have exactly the same problem like me, but adding the abovementioned four privileges (hopefully) resolved my pressing issue for now. The granted privileges do not seem to be too drastic, and I avoid grant ALL to everyone. This was reproducible on my side. I did not try it out with MySQL 5 though, I have only MySQL 8 DBs installed.

@Hendriela

Thanks @Hendriela!

I'm having trouble replicating the error with the schema you provided. I put together the script below to spin up a container, insert the data, and then run each of the deletes you listed.

Del #	Alone	Master	Part	Downstream	Expected Err
1.	X
2.		X
3.		X	X		X
4.		X		X	X
5.		X	X	X	X

Do you know which MySQL patch you're using?
Does the data insert below map on to what you ran?

Script

# ------------------------ Constants and Configuration ------------------------

CONTAINER_NAME = "test_priv"
MYSQL_IMAGE = "mysql:8.0.34"
PORT = 3306
ROOT_PW = "tutorial"
SCHEMA = "test_schema"
USER = "test_user"
USER_PW = "password"
DJ_LOG_LEVEL = "CRITICAL"
SHARED_CONFIG = {"database.host": "localhost", "database.port": PORT}
ROOT_CONFIG = {"user": "root", "password": ROOT_PW, "reset": True}
USER_CONFIG = {"user": USER, "password": USER_PW, "reset": True}

# --------------------- Setup MySQL Container with Docker ---------------------

import time  # noqa: E402

import docker  # noqa: E402


def remove_container(client: docker.DockerClient):
    try:
        container = client.containers.get(CONTAINER_NAME)
        print(f"Stopping/removing container: {CONTAINER_NAME}")
        container.stop()
        container.remove()
    except docker.errors.NotFound:
        print(f"No existing container named '{CONTAINER_NAME}'")


def start_mysql_container(client):
    print(f"Starting MySQL container   : {CONTAINER_NAME}")
    container = client.containers.run(
        image=MYSQL_IMAGE,
        name=CONTAINER_NAME,
        environment={"MYSQL_ROOT_PASSWORD": ROOT_PW},
        ports={"3306/tcp": PORT},
        detach=True,
    )
    return container


def wait_for_mysql(container, timeout=30):
    print(f"Waiting for container      : {CONTAINER_NAME}")
    for _ in range(timeout):
        try:
            exit_code, output = container.exec_run(
                f"mysqladmin ping -uroot -p{ROOT_PW}"
            )
            if b"mysqld is alive" in output:
                return
        except Exception:
            pass
        time.sleep(1)
    raise TimeoutError("MySQL did not become ready in time.")


def print_mysql_version(container):
    exit_code, output = container.exec_run(
        [
            "mysql",
            "-N",
            "-s",
            "-uroot",
            f"-p{ROOT_PW}",
            "-e",
            "SELECT VERSION();",
        ]
    )
    print(f"MySQL version              : {output.decode().splitlines()[-1]}")


def grant_privileges(container):
    sql = f"""
    CREATE USER IF NOT EXISTS '{USER}'@'%' IDENTIFIED BY '{USER_PW}';
    GRANT CREATE VIEW, SHOW VIEW, INDEX, TRIGGER ON {SCHEMA}.* TO '{USER}'@'%';
    FLUSH PRIVILEGES;
    """
    print(f"Granting privileges to     : {USER}")
    container.exec_run(cmd=["mysql", "-uroot", f"-p{ROOT_PW}", "-e", sql])


# === Docker Startup ===
if __name__ == "__main__":
    client = docker.from_env()
    _ = remove_container(client)
    container = start_mysql_container(client)
    _ = wait_for_mysql(container)
    _ = print_mysql_version(container)
    _ = grant_privileges(container)


# ------------------------------- Run DataJoint -------------------------------

import sys  # noqa: E402

import datajoint as dj  # noqa: E402

print(f"Python Version             : {sys.version.split()[0]}")
print(f"DataJoint Version          : {dj.__version__}")

dj.config = SHARED_CONFIG
dj.logger.setLevel(DJ_LOG_LEVEL)
conn = dj.conn(**ROOT_CONFIG)
print(f"Declaring tables as        : {conn.get_user()}")

schema = dj.schema(SCHEMA, create_tables=True)


@schema
class TestAlone(dj.Manual):
    definition = """
    alone_pk: int unsigned
    """


@schema
class TestMaster(dj.Manual):
    definition = """
    master_id: int unsigned
    """

    class TestPart(dj.Part):
        definition = """
        -> master
        ---
        part_sk: int unsigned
        """


@schema
class TestDownstream(dj.Manual):
    definition = """
    -> TestMaster
    """


conn.close()


def insert_test_data_as_root():
    """Insert test data into the tables."""
    conn = dj.conn(**ROOT_CONFIG)
    print(f"Inserting data as          : {conn.get_user()}")
    TestAlone.insert([[0], [1]], skip_duplicates=True)
    TestMaster.insert([[4], [5], [6], [7], [8]], skip_duplicates=True)
    TestMaster.TestPart.insert([[5, 10], [7, 20]], skip_duplicates=True)
    TestDownstream.insert([[6], [7], [8]], skip_duplicates=True)
    conn.close()


def delete_test_data_as_user():
    """Delete test data as a non-root user."""

    conn = dj.conn(**USER_CONFIG)
    print(f"Deleting entries as        : {conn.get_user()}")

    print("  1. Delete from TestAlone...")
    assert len(TestAlone & "alone_pk=0") == 1
    (TestAlone & "alone_pk=0").delete(safemode=False)
    assert len(TestAlone & "alone_pk=0") == 0

    print("  2. Delete from TestMaster, not TestPart/TestDownstream...")
    assert len(TestMaster & "master_id=4") == 1
    assert len(TestMaster.TestPart & "master_id=4") == 0
    (TestMaster & "master_id=4").delete(safemode=False)
    assert len(TestMaster & "master_id=4") == 0

    print("  3. Delete from TestMaster/TestPart, not TestDownstream...")
    assert len(TestMaster.TestPart & "master_id=5") == 1
    (TestMaster & "master_id=5").delete(safemode=False)
    assert len(TestMaster.TestPart & "master_id=5") == 0

    print("  4. Delete from TestMaster/TestDownstream, not TestPart...")
    assert len(TestMaster * TestDownstream & "master_id=6") == 1
    assert len(TestMaster.TestPart & "master_id=6") == 0
    (TestMaster & "master_id=6").delete(safemode=False)
    assert len(TestDownstream & "master_id=6") == 0

    print("  5. Delete from TestMaster/TestPart, and TestDownstream...")
    assert len(TestMaster.TestPart * TestDownstream & "master_id=7") == 1
    (TestMaster & "master_id=7").delete(safemode=False)
    assert len(TestMaster & "master_id=7") == 0

    print("Passed all deletion tests!")

    conn.close()


if __name__ == "__main__":
    insert_test_data_as_root()
    delete_test_data_as_user()

Output

run pipeline-hendrik.py
Stopping/removing container: test_priv
Starting MySQL container   : test_priv
Waiting for container      : test_priv
MySQL version              : 8.0.34
Granting privileges to     : test_user
Python Version             : 3.10.16
DataJoint Version          : 0.13.7
Declaring tables as        : root@172.17.0.1
Inserting data as          : root@172.17.0.1
Deleting entries as        : test_user@172.17.0.1
  1. Delete from TestAlone...
  2. Delete from TestMaster, not TestPart/TestDownstream...
  3. Delete from TestMaster/TestPart, not TestDownstream...
  4. Delete from TestMaster/TestDownstream, not TestPart...
  5. Delete from TestMaster/TestPart, and TestDownstream...
Passed all deletion tests!

I agree that all this could be circumvented it DataJoint did not rely on parsing verbose error messages to continue the cascade. From my various forum posts, I could never get a straight answer as so why MySQL would return the less verbose error when the user had permission to the relevant table

@CBroz1

@CBroz1 What would you suggest the error should be? I am looking at your example...

My understanding is that ALL privileges are required for all descendants. There may be a lesser privilege that works, but I found that REFERENCES, SELECT, USAGE was insufficient

@CBroz1

Just dropping in again to share this bug report, maybe you have not seen it yet: https://bugs.mysql.com/bug.php?id=112589
This seems to be exactly our issue: Some programs (like DataJoint) rely on the verbose error message from ERROR 1451, but users with insufficient privileges now receive the truncated ERROR 1217.
The bug reporter also provides a complete list of the extensive privileges required to get the verbose error for a DELETE (and complains about its unnecessary extent):
SELECT, DELETE, UPDATE, TRIGGER, REFERENCES, INDEX, INSERT, CREATE VIEW, DROP, SHOW VIEW, CREATE, ALTER.
Sadly, the MySQL developers replied but did not really address this issue.

In the short-term, I can see problems like @CBroz1 describes in his earlier comment also on the horizon for our setup. We are not at a point yet where users can attach personal tables to a shared pipeline, but this will most likely be a required feature, and the only option seems to be to give people much more extensive (and potentially destructive) privileges than should be necessary.

As this behavior, which was introduced in MySQL 8, seems to not be disable-able (short of downgrading to MySQL 5.7), the ideal solution would be for DataJoint to rework its delete-cascade algorithm to not rely on parsing error messages anymore. MySQL must somehow find out which foreign key constraint is the problem to display the verbose error message, so maybe Datajoint can do this directly and not wait for MySQL to deliver the dependencies.

I realize that this may be much more complex and involving much more aspects of the software than I can see, and might not be a realistic request. However, it would solve an issue that is very hard to track-down and counter-intuitive.

Noted. That's unfortunate because the error parsing way was most streamlined. Let me first raise this to the MySQL development team. Maybe they could fix the message.

@dimitri-yatsenko

Thanks for following-up with them, @dimitri-yatsenko! I wasn't able to get very far here

This issue is stale because it has been open for 45 days with no activity.

Let me first raise this to the MySQL development team

Any luck?

My assumption was that they'd say privileges need to reflect table inheritance: a user should have child-table privileges greater than or equal to all parents. The notion of a shared upstream pipeline with private downstream components is crucial to our lab but seems incompatible with the current delete process.

Analysis: MySQL Error 1217 vs 1451 on Cascade Delete

Problem Summary

When deleting rows with foreign key dependencies, MySQL returns different error codes depending on user privileges:

Error Code	Name	Info Provided	When
1451	ROW_IS_REFERENCED_2	Full details (child table, constraint, columns)	User has sufficient privileges
1217	ROW_IS_REFERENCED	Minimal ("a foreign key constraint fails")	User lacks privileges

DataJoint's cascade delete relies on parsing the error message to identify which child table is blocking the delete. With error 1217, there's insufficient information to continue.

Root Cause

MySQL intentionally hides detailed error information from users without sufficient privileges (security feature). The required privileges appear to be:

SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, REFERENCES (basic)
Plus: CREATE VIEW, SHOW VIEW, INDEX, TRIGGER (for verbose errors)

Current Status in DataJoint 2.0

The error handling has been improved but not fully resolved:

# src/datajoint/table.py:853-858
match = foreign_key_error_regexp.match(error.args[0])
if match is None:
    raise DataJointError(
        "Cascading deletes failed because the error message is missing foreign key information."
        "Make sure you have REFERENCES privilege to all dependent tables."
    ) from None

Before: Crashed with AttributeError: 'NoneType' object has no attribute 'groupdict'
After: Clear error message pointing to privilege issue

Possible Solutions

Document required privileges (quick fix)
- Update docs to specify all required privileges for cascade delete
Use dependency graph instead of error parsing (robust fix)
- DataJoint already tracks dependencies via INFORMATION_SCHEMA
- Could pre-compute delete order from dependency graph rather than relying on error messages
- More work but would eliminate privilege dependency
Hybrid approach
- Try error-based cascade first
- On error 1217, fall back to dependency graph approach

Recommendation

The issue is partially addressed in 2.0 (better error message) but the underlying problem remains for users with restricted privileges. For full resolution, consider implementing dependency-graph-based cascade delete as an alternative path when error parsing fails.

docs: design for restricted diagrams (#865, #1110)

Proposed solution: Restricted Diagrams

We're addressing this issue as part of a broader design that also covers #865 (applying restrictions to a Diagram). See PR #1407 and docs/design/restricted-diagram.md for the full design.

Root cause

The current cascade delete works by trial-and-error: attempt DELETE, catch the FK IntegrityError, parse the MySQL error message to discover which child table is blocking, then recurse. MySQL 8 returns error 1217 (less verbose) instead of 1451 when the user lacks certain privileges (CREATE VIEW, SHOW VIEW, INDEX, TRIGGER), causing the regex parse to fail.

Solution: Graph-driven cascade

Replace error-message parsing with dependency graph traversal — the same approach Table.drop() already uses successfully.

dj.Diagram gains two restriction operators:

diagram.cascade(expr) — propagates restriction downstream through FK attr_map. At convergence (child with multiple restricted ancestors), restrictions combine with OR. Used for delete.
diagram.restrict(expr) — same propagation but AND at convergence. Used for export/backup (Provide a backup and restore utility for DataJoint pipelines #864, Dataset Export #560).

Table.delete() internally becomes:

dj.Diagram(self).cascade(self_with_restriction).delete()

Deletes proceed in reverse topological order (leaves first → root last). FK constraints are never violated, so no IntegrityError is triggered, no error messages need parsing, and no SAVEPOINT/ROLLBACK overhead is needed on PostgreSQL.

Error-message parsing is retained only as a diagnostic fallback for unloaded schemas — producing an actionable error ("activate schema X") instead of the current 'NoneType' has no attribute 'groupdict' crash.

feat: implement graph-driven cascade delete and restrict on Diagram

Resolved by PR #1407 (graph-driven cascade delete on Diagram), which replaces the error-driven cascade approach entirely. The earlier fix in PR #1112 added the MySQL 8 error code (1217) as a stopgap. Both are now merged into master.

Navigation Menu

Repository navigation

Error on cascade delete in MySQL 8 #1110

Bug Report

Description

Reproducibility

Expected Behavior

Additional Research and Context

13 remaining items

Analysis: MySQL Error 1217 vs 1451 on Cascade Delete

Problem Summary

Root Cause

Current Status in DataJoint 2.0

Possible Solutions

Recommendation

Proposed solution: Restricted Diagrams

Root cause

Solution: Graph-driven cascade

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Error on cascade delete in MySQL 8 #1110

Description

Bug Report

Description

Reproducibility

Expected Behavior

Additional Research and Context

Activity

lfrank commented on Oct 6, 2023

dimitri-yatsenko commented on Nov 7, 2023

CBroz1 commented on Nov 16, 2023

dimitri-yatsenko commented on Sep 13, 2024

dimitri-yatsenko commented on Sep 13, 2024

CBroz1 commented on Sep 16, 2024

Hendriela commented on Jun 13, 2025

dimitri-yatsenko commented on Jun 13, 2025

Hendriela commented on Jun 13, 2025

CBroz1 commented on Jun 13, 2025

Hendriela commented on Jun 13, 2025

CBroz1 commented on Jun 13, 2025

13 remaining items

dimitri-yatsenko commented on Oct 8, 2025

CBroz1 commented on Oct 8, 2025

Hendriela commented on Oct 10, 2025

dimitri-yatsenko commented on Oct 10, 2025

CBroz1 commented on Oct 10, 2025

github-actions commented on Nov 25, 2025

CBroz1 commented on Nov 25, 2025

dimitri-yatsenko commented on Jan 9, 2026

Analysis: MySQL Error 1217 vs 1451 on Cascade Delete

Problem Summary

Root Cause

Current Status in DataJoint 2.0

Possible Solutions

Recommendation

dimitri-yatsenko commented on Feb 21, 2026

Proposed solution: Restricted Diagrams

Root cause

Solution: Graph-driven cascade

dimitri-yatsenko commented on Apr 1, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions