SQLite: Foreign key constraints can be disabled #104305

New Issue

Sybren A. Stüvel · 2024-05-10T12:03:42+02:00

Sybren A. Stüvel commented

2024-05-10 12:03:42 +02:00

Flamenco Version
Is Broken: 3.5 and older 3.x releases
Worked OK: never

Short description of error
When the Flamenco process is running for long enough, it seems that GORM can close the sqlite connection and create a new one by itself. Since that then doesn't execute the PRAGMA SQL commands to enable foreign key constraints, these constraints are disabled on this new connection. The result is that deleting a job will not delete its tasks, and same for other deletion operations.

Exact steps for others to reproduce the error
This is hard to reproduce, as it includes (for me) unknown GORM behaviour.

Run Flamenco Manager for long enough.
Delete a job.

Solution
Replace GORM with something that either provides an on-connect callback function, or doesn't reconnect by itself. Work is already underway to replace GORM with sqlc in #104304.

Note that there are already some guards in place, that refuse to delete jobs / workers / tags when foreign key constraints are disabled.

**Flamenco Version** Is Broken: 3.5 and older 3.x releases Worked OK: never **Short description of error** When the Flamenco process is running for long enough, it seems that GORM can close the sqlite connection and create a new one by itself. Since that then doesn't execute the `PRAGMA` SQL commands to enable foreign key constraints, these constraints are disabled on this new connection. The result is that deleting a job will not delete its tasks, and same for other deletion operations. **Exact steps for others to reproduce the error** This is hard to reproduce, as it includes (for me) unknown GORM behaviour. 1. Run Flamenco Manager for long enough. 2. Delete a job. **Solution** Replace GORM with something that either provides an on-connect callback function, or doesn't reconnect by itself. Work is already underway to replace GORM with [sqlc](https://sqlc.dev/) in #104304. Note that there are already some guards in place, that refuse to delete jobs / workers / tags when foreign key constraints are disabled.

Sybren A. Stüvel added the

Priority

High

label 2024-05-10 12:03:50 +02:00

Sybren A. Stüvel referenced this issue

2024-05-10 12:06:30 +02:00

WIP: convert GORM to sqlc, for jobs/tasks #104304

Sybren A. Stüvel added the

Type

Bug

label 2024-05-16 12:15:54 +02:00

Sybren A. Stüvel self-assigned this 2024-06-19 15:56:26 +02:00