[Bug]: prep remove does not work with mysql database

[YurkoWasHere]

Description

Singularity will note remove a preparation using the `prep remove` command. Error indicates a sql issue

Steps to Reproduce

1. run singularity with mysql as backend
2. start a prep
3. Run below commands

singularity prep remove --cars 1

Version

singularity v0.5.14-f54c4c1

Operating System

Linux

Database Backend

MySQL

Additional context

$ singularity prep remove --cars 5
2025-03-11T11:23:58.251-0400    INFO    database        database/connstring.go:45       Opening mysql database
2025-03-11T11:23:58.260-0400    ERROR   database        database/util.go:80     DELETE FROM `preparations` WHERE `preparations`.`id` = '8'      {"rowsAffected": 0, "elapsed": 0.002745421, "err": "Error 1296 (HY000): Got error 193 '`singularity`.`file_ranges`, CONSTRAINT `fk_files_file_ranges` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`) ON DELETE CASCADE' from InnoDB"}
Error 1296 (HY000): Got error 193 '`singularity`.`file_ranges`, CONSTRAINT `fk_files_file_ranges` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`) ON DELETE CASCADE' from InnoDB

This issue exists also when trying to run the delete command manually in mysql. This indicates some sort of mysql issues.

I managed to work around by first manually deleting file_ranges.

DELETE FROM file_ranges WHERE file_id IN (SELECT id FROM files WHERE attachment_id= 5);

Then performing the remove again.

singularity prep remove --cars 5

[parkan]

note for others who may come across this: postgresql is likely to be the most stable and supported backend for the foreseeable future

[brianeggert]

I am still seeing issues with singularity prep remove. I cannot reproduce Yurko's error message. Mine just hangs until killed. It seems to only affect preps that have been scanned. A brand new prep can be removed.

$brianeggert$ singularity prep create --name dummy --source 1    
2025-09-17T12:04:27.308-0500	INFO	database	database/connstring_cgo.go:35	Opening postgres database
ID   Name   DeleteAfterExport  MaxSize      PieceSize    MinPieceSize  NoInline  NoDag  
765  dummy  false              33822867456  34359738368  1048576       false     false  
    Source Storages:
        ID  Name                            Type             Path                            
        1   201569_News_Footage_1954_Can_7  internetarchive  201569_News_Footage_1954_Can_7  
$brianeggert$ singularity prep remove 765
2025-09-17T12:04:36.955-0500	INFO	database	database/connstring_cgo.go:35	Opening postgres database
2025-09-17T12:04:40.042-0500	WARN	database	database/util.go:78	[SLOW!] DELETE FROM "preparations" WHERE "preparations"."id" = '765'	{"rowsAffected": 1, "elapsed": 2.097980334, "err": null}

That above delete is successful and does not register in the db log. I tried removing one that I had named experiment. I encountered an error because there was still a wallet attached. I detached it and tried again, and it stays open until killed.

$brianeggert$ singularity prep remove 561
2025-09-17T12:05:57.786-0500	INFO	database	database/connstring_cgo.go:35	Opening postgres database
2025-09-17T12:05:58.912-0500	ERROR	database	database/util.go:80	DELETE FROM "preparations" WHERE "preparations"."id" = '561'	{"rowsAffected": 0, "elapsed": 0.115128, "err": "ERROR: update or delete on table \"preparations\" violates foreign key constraint \"fk_wallet_assignments_preparation\" on table \"wallet_assignments\" (SQLSTATE 23503)"}
ERROR: update or delete on table "preparations" violates foreign key constraint "fk_wallet_assignments_preparation" on table "wallet_assignments" (SQLSTATE 23503)
$brianeggert$ singularity prep detach-wallet 561 f01131298
2025-09-17T12:06:35.372-0500	INFO	database	database/connstring_cgo.go:35	Opening postgres database
2025-09-17T12:06:37.075-0500	WARN	database	database/util.go:78	[SLOW!] SELECT * FROM "preparations" WHERE "preparations"."id" = 561 ORDER BY "preparations"."id" LIMIT 1	{"rowsAffected": 1, "elapsed": 1.167786833, "err": null}
ID   Name                                    DeleteAfterExport  MaxSize     PieceSize   MinPieceSize  NoInline  NoDag  
561  200411_Mode-Art_Can_1482_R3-experiment  false              3758096384  4294967296  1048576       false     false  
    Source Storages:
        ID   Name                         Type             Path                         
        389  200411_Mode-Art_Can_1482_R3  internetarchive  200411_Mode-Art_Can_1482_R3  
$brianeggert$ singularity prep remove 561
2025-09-17T12:06:42.631-0500	INFO	database	database/connstring_cgo.go:35	Opening postgres database
^C

The first attempt blocked by the wallet did register as an error in the db log, but once I removed the wallet and ran prep remove, it logs nothing

2025-09-17 17:05:58.970 GMT [24775] ERROR:  update or delete on table "preparations" violates foreign key constraint "fk_wallet_assignments_preparation" on table "wallet_assignments"
2025-09-17 17:05:58.970 GMT [24775] DETAIL:  Key (id)=(561) is still referenced from table "wallet_assignments".
2025-09-17 17:05:58.970 GMT [24775] STATEMENT:  DELETE FROM "preparations" WHERE "preparations"."id" = $1

These prep remove commands will hang indefinitely. I believe I have left one overnight to see if it would finish. I can't see much else in the db log. In the past I remember seeing all workers reset in the dataset-worker log at the moment I would run a prep remove command, but I cannot reproduce that this time. This is what I have seen in the past at the moment I run prep remove

2025-09-17T16:45:35.969Z	INFO	database	database/connstring_cgo.go:35	Opening postgres database
2025-09-17T16:45:36.005Z	INFO	datasetworker	service/service.go:60	starting service	{"name": "Preparation Worker Thread - 084b7bb6-7384-4c05-b6d9-c0d7c3d77ea6"}
2025-09-17T16:45:36.010Z	INFO	datasetworker	service/service.go:60	starting service	{"name": "Preparation Worker Thread - 6c27abeb-25ed-4c0d-ac85-78a37ce561ca"}
2025-09-17T16:45:36.012Z	INFO	datasetworker	service/service.go:60	starting service	{"name": "Preparation Worker Thread - bff80817-cae7-4497-a9ef-4c32ffc410f8"}
2025-09-17T16:45:36.015Z	INFO	datasetworker	service/service.go:60	starting service	{"name": "Preparation Worker Thread - c28d5b8e-50ab-484f-bd13-e0888a1d1043"}

[parkan]

it seems like there are two separate issues here, I will address them separately:

files/file ranges related error in the OP

here's a diagram of the relevant tables in the schema as realized by postgres, however MySQL likely yields the same FKs

my suspicion is that the duplicate ON CASCADE DELETE paths:

• preparations → source_attachments → directories → files → file_ranges
• preparations → source_attachments → files → file_ranges

result in InnoDB refusing to complete the operation, I am working on validating this and proposing a fix

Brian's reported error

first error is just due to wallet assignments join table:

(snippet of the full schema)

here we simply have a composite primary key on the wallet_assignments table, unless we think having a wallet association should "protect" a prep (I personally do not, and if so the error should be more helpful) these rows should be removed automatically if either referenced prep or wallet is removed

the hanging behavior after this is due to recursive deadlock:

 preparations | relation |    16384 |    16403 |      |       |            |               | RowExclusiveLock    | t       | singularity | DELETE FROM "preparations" WHERE
"preparations"."id" = $1 | 2025-09-17 16:49:42.700381+00 | active | 24709
 preparations | relation |    16384 |    16403 |      |       |            |               | RowShareLock        | t       | singularity | DELETE FROM "preparations" WHERE
"preparations"."id" = $1 | 2025-09-17 16:49:42.700381+00 | active | 24709
 preparations | tuple    |    16384 |    16403 |    9 |    21 |            |               | AccessExclusiveLock | t       | singularity | DELETE FROM "preparations" WHERE
"preparations"."id" = $1 | 2025-09-17 16:56:11.566394+00 | active | 24732
 preparations | relation |    16384 |    16403 |      |       |            |               | RowExclusiveLock    | t       | singularity | DELETE FROM "preparations" WHERE
"preparations"."id" = $1 | 2025-09-17 16:56:11.566394+00 | active | 24732
 preparations | relation |    16384 |    16403 |      |       |            |               | RowExclusiveLock    | t       | singularity | DELETE FROM "preparations" WHERE
"preparations"."id" = $1 | 2025-09-17 17:06:43.946778+00 | active | 24784
 preparations | relation |    16384 |    16403 |      |       |            |               | RowShareLock        | t       | singularity | DELETE FROM "preparations" WHERE
"preparations"."id" = $1 | 2025-09-17 17:06:43.946778+00 | active | 24784

this is consistent with the behavior @YurkoWasHere reported; it appears that InnoDB will refuse to enter the deadlocked state and outright refuses the query, whereas postgres goes ahead and gets stuck, so my working theory is that this is the same issue as in (1)

we do have two other multipath structures that may be a problem:

• preparations → cars + preparations → source_attachments → cars
• preparations → cars → car_blocks + preparations → source_attachments → directories → files → car_blocks

I will test the fixes in #583 to see if that clears our situation; if not the two above may need to be cleared as well