search

datajoint / datajoint-python

Relational data pipelines for the science lab
https://datajoint.com/docs
GNU Lesser General Public License v2.1
168 stars 84 forks source link

Deletion error "foreign key parsing error" in 0.13.dev5 #886

Closed ttngu207 closed 3 years ago

ttngu207 commented 3 years ago

Bug Report

Cascade delete error in the latest 0.13.dev6 datajoint version

Description

An error strack:

Session.delete()
debug error: Cannot delete or update a parent row: a foreign key constraint fails (`u24_img_experiment`.`session__directory`, CONSTRAINT `session__directory_ibfk_1` FOREIGN KEY (`subject`, `session_datetime`) REFERENCES `session` (`subject`, `session_datetime`) ON UPDATE CAS)
Deleting 4 rows from `u24_img_experiment`.`session__directory`
debug error: Cannot delete or update a parent row: a foreign key constraint fails (`u24_img_scan`.`scan`, CONSTRAINT `scan_ibfk_1` FOREIGN KEY (`subject`, `session_datetime`) REFERENCES `u24_img_experiment`.`session` (`subject`, `session_datetime`) ON UPDATE CASCADE)
debug error: Cannot delete or update a parent row: a foreign key constraint fails (`u24_img_imaging`.`processing_task`, CONSTRAINT `processing_task_ibfk_1` FOREIGN KEY (`subject`, `session_datetime`, `scan_id`) REFERENCES `u24_img_scan`.`scan` (`subject`, `session_datetime`,)
debug error: Cannot delete or update a parent row: a foreign key constraint fails (`u24_img_imaging`.`__processing`, CONSTRAINT `__processing_ibfk_1` FOREIGN KEY (`subject`, `session_datetime`, `scan_id`, `paramset_idx`) REFERENCES `processing_task` (`subject`, `session_date)
debug error: Cannot delete or update a parent row: a foreign key constraint fails (`u24_img_imaging`.`_motion_correction`, CONSTRAINT `_motion_correction_ibfk_1` FOREIGN KEY (`subject`, `session_datetime`, `scan_id`, `paramset_idx`) REFERENCES `__processing` (`subject`, `ses)
debug error: Cannot delete or update a parent row: a foreign key constraint fails (`u24_img_imaging`.`_motion_correction__non_rigid_motion_correction`, CONSTRAINT `_motion_correction__non_rigid_motion_correction_ibfk_1` FOREIGN KEY (`subject`, `session_datetime`, `scan_id`, )
Traceback (most recent call last):
  File "C:\ProgramData\Miniconda3\envs\workflow-imaging\lib\site-packages\datajoint\table.py", line 345, in _delete_cascade
    delete_count += self.delete_quick(get_count=True)
  File "C:\ProgramData\Miniconda3\envs\workflow-imaging\lib\site-packages\datajoint\table.py", line 334, in delete_quick
    self.connection.query(query)
  File "C:\ProgramData\Miniconda3\envs\workflow-imaging\lib\site-packages\datajoint\connection.py", line 294, in query
    self._execute_query(cursor, query, args, suppress_warnings)
  File "C:\ProgramData\Miniconda3\envs\workflow-imaging\lib\site-packages\datajoint\connection.py", line 260, in _execute_query
    raise translate_query_error(err, query)
datajoint.errors.IntegrityError: Cannot delete or update a parent row: a foreign key constraint fails (`u24_img_imaging`.`_motion_correction__non_rigid_motion_correction`, CONSTRAINT `_motion_correction__non_rigid_motion_correction_ibfk_1` FOREIGN KEY (`subject`, `session_datetime`, `scan_id`, )
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "C:\ProgramData\Miniconda3\envs\workflow-imaging\lib\site-packages\IPython\core\interactiveshell.py", line 3437, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-5-0f2b20e9c266>", line 1, in <module>
    Session.delete()
  File "C:\ProgramData\Miniconda3\envs\workflow-imaging\lib\site-packages\datajoint\table.py", line 398, in delete
    delete_count = self._delete_cascade()
  File "C:\ProgramData\Miniconda3\envs\workflow-imaging\lib\site-packages\datajoint\table.py", line 367, in _delete_cascade
    delete_count += child._delete_cascade()
  File "C:\ProgramData\Miniconda3\envs\workflow-imaging\lib\site-packages\datajoint\table.py", line 367, in _delete_cascade
    delete_count += child._delete_cascade()
  File "C:\ProgramData\Miniconda3\envs\workflow-imaging\lib\site-packages\datajoint\table.py", line 367, in _delete_cascade
    delete_count += child._delete_cascade()
  [Previous line repeated 1 more time]
  File "C:\ProgramData\Miniconda3\envs\workflow-imaging\lib\site-packages\datajoint\table.py", line 349, in _delete_cascade
    assert match is not None, "foreign key parsing error"
AssertionError: foreign key parsing error

Reproducibility

Include:

guzman-raphael commented 3 years ago

Looks like the main issue here is that the MySQL error message is being truncated and it is now strictly required for cascade delete as it progresses based on parsing it. Should be able to reproduce it with what is provided here.

guzman-raphael commented 3 years ago

Related to #883

ttngu207 commented 3 years ago

Confirming that #887 solves the deletion error reported in this issue.