Cannot delete or update a parent row when deleting tables that have downstream merge tables

[xlsun79]

When trying to either delete the Nwbfile() table (so as to re-insert data for a certain session) or PositionOutput table, it raises the error "'Cannot delete or update a parent row: a foreign key constraint fails'".

To Reproduce
Steps to reproduce the behavior:

trodes_key = {
    "nwb_file_name": 'ShyLu20240321_.nwb',
    "interval_list_name":'pos 2 valid times',
    "trodes_pos_params_name": 'default',
}
PositionOutput.TrodesPosV1().delete_downstream_merge(trodes_key,dry_run=False)

Error Stack

[00:06:29][INFO] Spyglass: Building merge cache for position_output__trodes_pos_v1.
	Found 2 downstream merge tables
---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
Cell In [90], line 1
----> 1 PositionOutput.TrodesPosV1().delete_downstream_merge(trodes_key,dry_run=False)

File ~/code/spyglass/src/spyglass/utils/dj_mixin.py:263, in SpyglassMixin.delete_downstream_merge(self, restriction, dry_run, reload_cache, disable_warning, return_parts, **kwargs)
    260 if dry_run:
    261     return merge_join_dict.values() if return_parts else merge_join_dict
--> 263 self._commit_merge_deletes(merge_join_dict, **kwargs)

File ~/code/spyglass/src/spyglass/utils/dj_mixin.py:208, in SpyglassMixin._commit_merge_deletes(self, merge_join_dict, **kwargs)
    206 table = self._merge_tables[table_name]
    207 keys = [part.fetch(MERGE_PK, as_dict=True) for part in part_restr]
--> 208 (table & keys).delete(**kwargs)

File ~/anaconda3/envs/spyglass/lib/python3.9/site-packages/datajoint/table.py:561, in Table.delete(self, transaction, safemode, force_parts)
    559 # Cascading delete
    560 try:
--> 561     delete_count = cascade(self)
    562 except:
    563     if transaction:

File ~/anaconda3/envs/spyglass/lib/python3.9/site-packages/datajoint/table.py:479, in Table.delete.<locals>.cascade(table)
    477 for _ in range(max_attempts):
    478     try:
--> 479         delete_count = table.delete_quick(get_count=True)
    480     except IntegrityError as error:
    481         match = foreign_key_error_regexp.match(error.args[0]).groupdict()

File ~/anaconda3/envs/spyglass/lib/python3.9/site-packages/datajoint/table.py:453, in Table.delete_quick(self, get_count)
    448 """
    449 Deletes the table without cascading and without user prompt.
    450 If this table has populated dependent tables, this will fail.
    451 """
    452 query = "DELETE FROM " + self.full_table_name + self.where_clause()
--> 453 self.connection.query(query)
    454 count = (
    455     self.connection.query("SELECT ROW_COUNT()").fetchone()[0]
    456     if get_count
    457     else None
    458 )
    459 self._log(query[:255])

File ~/anaconda3/envs/spyglass/lib/python3.9/site-packages/datajoint/connection.py:340, in Connection.query(self, query, args, as_dict, suppress_warnings, reconnect)
    338 cursor = self._conn.cursor(cursor=cursor_class)
    339 try:
--> 340     self._execute_query(cursor, query, args, suppress_warnings)
    341 except errors.LostConnectionError:
    342     if not reconnect:

File ~/anaconda3/envs/spyglass/lib/python3.9/site-packages/datajoint/connection.py:296, in Connection._execute_query(cursor, query, args, suppress_warnings)
    294         cursor.execute(query, args)
    295 except client.err.Error as err:
--> 296     raise translate_query_error(err, query)

File ~/anaconda3/envs/spyglass/lib/python3.9/site-packages/datajoint/connection.py:294, in Connection._execute_query(cursor, query, args, suppress_warnings)
    291         if suppress_warnings:
    292             # suppress all warnings arising from underlying SQL library
    293             warnings.simplefilter("ignore")
--> 294         cursor.execute(query, args)
    295 except client.err.Error as err:
    296     raise translate_query_error(err, query)

File ~/anaconda3/envs/spyglass/lib/python3.9/site-packages/pymysql/cursors.py:148, in Cursor.execute(self, query, args)
    144     pass
    146 query = self.mogrify(query, args)
--> 148 result = self._query(query)
    149 self._executed = query
    150 return result

File ~/anaconda3/envs/spyglass/lib/python3.9/site-packages/pymysql/cursors.py:310, in Cursor._query(self, q)
    308 self._last_executed = q
    309 self._clear_result()
--> 310 conn.query(q)
    311 self._do_get_result()
    312 return self.rowcount

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

File ~/anaconda3/envs/spyglass/lib/python3.9/site-packages/pymysql/connections.py:775, in Connection._read_query_result(self, unbuffered)
    773 else:
    774     result = MySQLResult(self)
--> 775     result.read()
    776 self._result = result
    777 if result.server_status is not None:

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

File ~/anaconda3/envs/spyglass/lib/python3.9/site-packages/pymysql/connections.py:725, in Connection._read_packet(self, packet_type)
    723     if self._result is not None and self._result.unbuffered_active is True:
    724         self._result.unbuffered_active = False
--> 725     packet.raise_for_error()
    726 return packet

File ~/anaconda3/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 ~/anaconda3/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')

Or if I tried to directly delete from the PositionOutput table via

trodes_key = {
    "nwb_file_name": 'ShyLu20240321_.nwb',
    "interval_list_name":'pos 2 valid times',
    "trodes_pos_params_name": 'default',
}
merge_key = (PositionOutput.merge_get_part(trodes_key)).fetch1("KEY")
(PositionOutput.TrodesPosV1() & merge_key).delete()

I'd get:

Error Stack

---------------------------------------------------------------------------
PermissionError                           Traceback (most recent call last)
Cell In [95], line 1
----> 1 (PositionOutput.TrodesPosV1() & merge_key).delete()

File ~/code/spyglass/src/spyglass/utils/dj_mixin.py:489, in SpyglassMixin.delete(self, force_permission, *args, **kwargs)
    487 def delete(self, force_permission=False, *args, **kwargs):
    488     """Alias for cautious_delete, overwrites datajoint.table.Table.delete"""
--> 489     self.cautious_delete(force_permission=force_permission, *args, **kwargs)

File ~/code/spyglass/src/spyglass/utils/dj_mixin.py:450, in SpyglassMixin.cautious_delete(self, force_permission, *args, **kwargs)
    447 start = time()
    449 if not force_permission:
--> 450     self._check_delete_permission()
    452 merge_deletes = self.delete_downstream_merge(
    453     dry_run=True,
    454     disable_warning=True,
    455     return_parts=False,
    456 )
    458 safemode = (
    459     dj.config.get("safemode", True)
    460     if kwargs.get("safemode") is None
    461     else kwargs["safemode"]
    462 )

File ~/code/spyglass/src/spyglass/utils/dj_mixin.py:377, in SpyglassMixin._check_delete_permission(self)
    374 experimenters = sess_summary.fetch(self._member_pk)
    375 if None in experimenters:
    376     # TODO: Check if allow delete of remainder?
--> 377     raise PermissionError(
    378         "Please ensure all Sessions have an experimenter in "
    379         + f"SessionExperimenter:\n{sess_summary}"
    380     )
    382 user_name = LabMember().get_djuser_name(dj_user)
    383 for experimenter in set(experimenters):
    384     # Check once with cache, if fails, reload and check again
    385     # On eval as set, reload will only be called once

PermissionError: Please ensure all Sessions have an experimenter in SessionExperimenter:
*nwb_file_name *lab_member_na
+------------+ +------------+
mediumnwb20230 None          
minirec2023062 None          
Banner20211221 Abhijith Manki
Banner20211222 Abhijith Manki
Banner20211227 Abhijith Manki
Banner20211228 Abhijith Manki
Banner20211230 Abhijith Manki
Banner20211231 Abhijith Manki
Banner20220103 Abhijith Manki
Banner20220105 Abhijith Manki
Banner20220123 Abhijith Manki
Banner20220124 Abhijith Manki
   ...
 (Total: 109)

How could I delete the cascade of tables that depend on the parent table? I need to delete these entries in order to rerun the analyses as I found mistakes in previous runs. Thanks for any insight!

[samuelbray32]

Couple quick things I see.

In the first Attempt:

• You should delete from the merge table PositionOutput, not it's part PositionOutput.TrodesPosV1()
• 1217 is a permission error related to how datajoint talks to SQL. See Low verbosity error 1217 on delete prevents cascade #638. Should check with chris about your permissions in our database

In thesecondAttempt:

• Again, you should delete from the merge table PositionOutput, not it's part PositionOutput.TrodesPosV1()
• The error makes it look like you're trying to delete a lot of entries that don't belong to you (e.g. Banner), hence the datajoint permissions error. This might just be due to trying to delete from the part insteasd of the parent

[xlsun79]

Thanks @samuelbray32 That clears my confusion about deleting merge tables!
When I tried PositionOutput().delete_downstream_merge(merge_key,dry_run=False) It still returns the integrity error, and I believe it's the same as the session deletion error you referenced. Thanks! @CBroz1 would you mind helping me check my user permission in the database as I have been getting 1217 permission error when trying to delete different table entries that I created. Thank you very much!

[edeno]

My understanding is that there was another user declaring a table preventing deletion. That is solved now so deletion should be possible. Please reopen if it continues to be a problems.

[xlsun79]

Hi @CBroz1 @edeno I didn't see a reopen button on this page, but after pulling the latest spyglass, I'm still running into this integrity error 1217 no matter which table I attempted to delete (I've tried "cautious delete" with Nwbfile(), Session(), and "delete_downstream_merge" with PostionOutput()). Could it be due to my restricted user permissions in our SQL database?

[CBroz1]

Hi @xlsun79 - The problem you're describing in #638, which so far can only be solved by either (a) running the downstream deletes yourself (see table in the issue) or (b) granting you admin-level privileges in the database

[xlsun79]

Thanks so much @CBroz1 ! Didn't mean to keep commenting under this closed case, but would it be possible to give me admin-level privileges for now so I could delete a few tables that need to be replaced with the newly-converted NWB data? I will make sure not to delete other people's data/table, and keep you posted when I don't need the privilege anymore so we could revert to a regular user. Thanks a lot!