Low verbosity error `1217` on delete prevents cascade

[samuelbray32]

Describe the bug Attempting to delete an entry within the Session table results in the following error: IntegrityError: (1217, 'Cannot delete or update a parent row: a foreign key constraint fails')

To Reproduce Steps to reproduce the behavior:

1. Run (Session & {"nwb_file_name": 'samtest20230817_.nwb'}).delete({"nwb_file_name": 'samtest20230817_.nwb'})

Expected behavior The entry and all other downstream should be removed

Additional context The only thing done with this nwb_file was insertion into spyglass, if that helps narrow down potential schema permission errors

[edeno]

For reference, here are the key restraints:

[khl02007]

@samuelbray32 this probably won't solve it but did you try not passing in the dictionary to delete? i.e. (Session & {"nwb_file_name": 'samtest20230817_.nwb'}).delete() instead of ((Session & {"nwb_file_name": 'samtest20230817_.nwb'}).delete({"nwb_file_name": 'samtest20230817_.nwb'}). And I thought delete only gets rid of the downstream entries -- the upstream entries (e.g. those in Nwbfile) will need to be deleted manually

[edeno]

I was able to delete Sharon's file when she had this issue. So one possibility is that this is a permissions issue.

The other thought is that experiment_description is varchar(2000), which is a problem with the current MySQL. The permissions issue seems more likely though.

[samuelbray32]

@samuelbray32 this probably won't solve it but did you try not passing in the dictionary to delete? i.e. (Session & {"nwb_file_name": 'samtest20230817_.nwb'}).delete() instead of ((Session & {"nwb_file_name": 'samtest20230817_.nwb'}).delete({"nwb_file_name": 'samtest20230817_.nwb'}). And I thought delete only gets rid of the downstream entries -- the upstream entries (e.g. those in Nwbfile) will need to be deleted manually

The alternative delete call also didn't work. Attempting to delete from Nwbfile caused the same error. Also, I mistyped in the initial description when I said upstream. I've edited that now

[edeno]

My current hypothesis is that it is the alison_ prefixed tables that are causing the issue. The ms_ tables are also maybe problematic for other users, but @samuelbray32 has permissions for these tables.

[acomrie]

Hi, in case useful, I haven't populated these alison schema or interacted with them at all in this timeframe, so it would have to be something about how they're instantiated I suppose (there are likely some dependencies on Session as expected, but I'd expect others have custom schema that depend on Session as well)

[edeno]

To test the permissions part, @samuelbray32 can you reinsert your test file and try to delete it? Upon failure, I think @acomrie can try to delete it, which should work.

[edeno]

Possibly caused by #641

[samuelbray32]

There's another entry samtestb20230817_.nwb with the same issue can test on

[samuelbray32]

Created issue for datajoint here

[shijiegu]

Hi everyone. I might have a similar issue here. I have some data processed from last year 2022. I cannot open these data now. Relevant files:

position_info = (IntervalLinearizedPosition() &
                 {'nwb_file_name': 'molly20220416_.nwb','interval_list_name': 'pos 1 valid times',
                  'position_info_param_name': 'default'}
                ).fetch1_dataframe()

This works for data processed this year.

position_info = (IntervalLinearizedPosition() &
                 {'nwb_file_name': 'eliot20221016_.nwb','interval_list_name': 'pos 1 valid times',
                  'position_info_param_name': 'default'}
                ).fetch1_dataframe()

If you try to delete old entries in the IntervalLinearizedPosition(), you get the same error which is (1217, 'Cannot delete or update a parent row: a foreign key constraint fails')

[CBroz1]

Hi @shijiegu - Are you seeing Error 1217 with fetch1_dataframe()? I would only expect to see this when deleting data. I'm able to run your first command just fine

[edeno]

@shijiegu how updated is your spyglass?

[shijiegu]

@edeno I think you might be right. I only updated Spyglass this year in June...

[edeno]

@CBroz1's PR will hopefully fix this in datajoint when it is merged: https://github.com/datajoint/datajoint-python/pull/1112

[CBroz1]

Closing with https://github.com/datajoint/datajoint-python/pull/1112

[CBroz1]

(Session & restriction).delete() is still an issue for @sharon-chiang. In her case, it is a permission error and not a mysql 8 error code issue. I think this is solvable when we tackle the spike-sorting unix user group issue

[edeno]

Could you give a little more context for what the error was?

[sharon-chiang]

The error was the same 1217 integrity error as previously encountered. This occurred for (sgc.Session() & {'nwb_file_name': 'SC3820230615_.nwb'}).delete() after pulling the most recent updates from datajoint.

[edeno]

Just to update from discussions with @CBroz1:

This issue should not affect people newly setting up spyglass so far as we know. Currently only a problem with the Frank Lab database.

[CBroz1]

I've (a) reached out to our db admin to request that logs be turned on to better monitor occurrences, (b) reached out to a member of the datajoint team for insights, and (c) posted to stack overflow for community input

[CBroz1]

I have continued to pursue solutions as described in post here.

[CBroz1]

This list details which tables do and do not permit cascading deletes for low-privilege users. Attempting to delete from each table resulted in one of three outcomes...

• deleted - A delete from this table works fine
• verbose - A delete from this table returns the error DataJoint needs to continue the cascade, directing to the blocking table
• problem- A delete from this table points to the blocking table for high- but not low-privilege users

SCHEMA	TABLE NAME	RESULT	Blocking Table
common_nwbfile	nwbfile	PROBLEM	_session
common_session	_session	PROBLEM	position_source
common_behav	position_source	verbose	raw_position
common_behav	_raw_position	PROBLEM	_raw_position__pos_object
common_behav	_raw_position__pos_object	deleted
common_behav	position_source__spatial_series	verbose	raw_position__pos_object
common_dio	_d_i_o_events	deleted
common_ephys	_electrode_group	PROBLEM	_electrode
common_ephys	_electrode	deleted
common_ephys	_raw	deleted
common_ephys	_sample_count	deleted
common_interval	interval_list	PROBLEM	_position_interval_map
common_behav	__position_interval_map	deleted
common_task	_task_epoch	PROBLEM	_video_file
common_behav	_video_file	deleted
common_session	_session__data_acquisition_device	deleted

---

@edeno edit formatting for table

[shijiegu]

This issue persists:

(LFPSelection() & {'nwb_file_name' :'eliot20221025_.nwb'}).delete()

gives:

IntegrityError: (1217, 'Cannot delete or update a parent row: a foreign key constraint fails')

[shijiegu]

Hi! I see the various attempts above, but at the end of the day, if a researcher wants to delete an entry, they are supposed to be able to do it. Now I need to traverse all children tables to find a potential blocking table myself. This error was not there, at least for me, prior to 2023 Jun. This is caused by changes/upgrades in the database; I expect to receive a concrete fix for this, i.e., some scripts or an alternate delete function that I can call.

[samuelbray32]

As we've discussed in lab meetings, this is a permissions issue specific to our implemented database and we have collectively agreed on the working solution of escalating a user's permissions as needed.

If you are experiencing this error, please reach out the the database admins with the username(s) that are experiencing the issue to have their permissions altered

[shijiegu]

Thanks Sam. I vaguely remember this discussion, spanning multiple lab meetings. I also recall a suggestion to implement a special delete function to call in an event like this.

I also need to point out no one has perfect memory on every issue of the database. A written resolution on how to proceed temporarily, especially in Open Issues, will be very helpful.

[shijiegu]

As a second note, the scattered comments above do not fully explain why I need to traverse children tables because the principle of the datajoint is such that children tables will be subject to parent tables.

I could guess that a downstream table entry privilege is different from any of its parent because it is a more precious table, like curated spike sorting, but the reason is not fully split out. To better help users fix their own problems, some summarized knowledge on this kind of long and open issue will be helpful too.

[edeno]

Hi @shijiegu, please review the Code of Conduct and come talk to me.

[CBroz1]

In a further attempt to explore this issue, I dropped all schemas on our development server, declared empty tables with datajoint, and then loaded a mysqldump of common_. The verbosity issue persists.

This dump...

1. had --skip-add-drop-table to avoid dropping tables during the load
2. had edits to CREATE TABLE to include IF NOT EXISTS
3. had edits to INSERT to ignore duplicates, specifically for conflicting log lines related to declaration

script

```bash #!/bin/bash # MySQL credentials USER="cbroz" PROD_PASS="redacted" PROD_HOST="redacted" DEV_PASS="redacted" DEV_HOST="redacted" OUTPUT_DIR="/home/cbroz/wrk/alt/common_dump" DATABASE_LIST="temp-dump-list.txt" DUMP_FILE="all_common_databases.sql" mkdir -p "$OUTPUT_DIR" # Create output directory if it doesn't exist DATABASES=$(tr '\n' ' ' < "$DATABASE_LIST") # Load text list of databases echo "Dumping databases: $DATABASES" # dump all, skipping drop and create info mysqldump \ -u "$USER" -p"$PROD_PASS" -h "$PROD_HOST" \ --skip-add-drop-table \ --databases $DATABASES > "$OUTPUT_DIR/$DUMP_FILE" if [ $? -eq 0 ]; then echo "Successfully dumped databases: $DATABASES" else echo "Error dumping databases: $DATABASES" fi sed -i 's/CREATE TABLE /CREATE TABLE IF NOT EXISTS /g' "$OUTPUT_DIR/$DUMP_FILE" sed -i 's/INSERT INTO /INSERT IGNORE INTO /g' "$OUTPUT_DIR/$DUMP_FILE" echo "Loading databases from $DUMP_FILE" mysql\ -u "$USER" -p"$DEV_PASS" -h "$DEV_HOST" \ < "$OUTPUT_DIR/$DUMP_FILE" ```