FOREIGN KEY constraint failed when moving to 0.1.0-beta.11

[cgrinds]

Using compose workflow with sqlite

sqlite3 --version
3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafaalt1

latest was working well, 0.1.0-beta.11 fails during migration but without much logging.

docker image ls
REPOSITORY            TAG                IMAGE ID       CREATED        SIZE
bonukai/mediatracker  latest             1e93ed94e946   7 weeks ago    233MB
bonukai/mediatracker  0.1.0-beta.11      5c2cd209e847   2 hours ago    234MB

docker logs x-x-x-x
May 04 21:59:59 mn docker-compose[2236427]: MediaTracker    | info:  Creating config directory at /root/.mediatracker
May 04 21:59:59 mn docker-compose[2236427]: MediaTracker    | migration file "20220312002700_mediaItemSlug.js" failed
May 04 21:59:59 mn docker-compose[2236427]: MediaTracker    | migration failed with error: FOREIGN KEY constraint failed
May 04 21:59:59 mn docker-compose[2236427]: MediaTracker    | error: Error: FOREIGN KEY constraint failed
May 04 21:59:59 mn docker-compose[2236427]: MediaTracker    | starting server at http://d9b86a561197:7481

Thought about exporting from current db, creating a new one, and importing into it, but I don't think there are any helpers for that yet.

Maybe the API could be used to start fresh? I'll take a look at 20220312002700_mediaItemSlug.js and see if anything jumps out.

[cgrinds]

Few things to share while debugging this

• I enabled knex debug and go more information, pasted below. It fails on the first table it touches, image. I don't see foreign key violations in between that table and mediaItem or season
• I suspect the problem is the same as this one, especially since the last statement to be logged is PRAGMA foreign_keys = ON and it appears to be wrapped in a transaction.
• Looks like mediaTracker bumped knex from 1.0.4 to 2.0.0 a few days ago and knex mentions in their upgrade notes that the sqlite3 driver should be used. Looks like mediaTracker is using better-sqlite3 perhaps sqlite3 would be a better choice?

Debug log before and including failure

May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    | {
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   method: 'raw',
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   sql: 'CREATE INDEX `image_type_index` on `image` (`type`)',
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   bindings: [],
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   options: {},
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   __knexQueryUid: 'TY5hdg_WDqvZKTLvqVpgA'
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    | }
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    | {
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   method: 'raw',
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   sql: 'PRAGMA foreign_key_check',
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   bindings: [],
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   options: {},
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   __knexQueryUid: 'b-7Ysh12ZamcM2umvKJOb'
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    | }
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    | {
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   method: 'raw',
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   sql: 'PRAGMA foreign_keys = ON',
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   bindings: [],
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   options: {},
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   __knexQueryUid: 'HEVhS4BKsLbh44eakcaEL'
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    | }
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    | migration file "20220312002700_mediaItemSlug.js" failed
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    | migration failed with error: FOREIGN KEY constraint failed
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    | {
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   method: 'update',
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   options: {},
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   timeout: false,
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   cancelOnTimeout: false,
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   bindings: [ 0 ],
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   __knexQueryUid: 'obhavWuXQDDlPxLP0TZ8W',
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    |   sql: 'update `knex_migrations_lock` set `is_locked` = ?'
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    | }
May 04 22:56:47 mn docker-compose[2282885]: MediaTracker    | error: Error: FOREIGN KEY constraint failed

[bonukai]

Other user had similar problem before (https://github.com/bonukai/MediaTracker/issues/232#issuecomment-1067219380), and I couldn't figure it out, or even replicate this error.

This migration starts with removing all foreign keys in the database, to avoid FOREIGN KEY constraint errors, and image table isn't referenced by any other table https://github.com/bonukai/MediaTracker/blob/cde1ad4dd12c35167e0e2635f2ac3b1ec8555fef/server/src/migrations/20220312002700_mediaItemSlug.ts#L6-L51

and it passes my tests, where all tables all filled with some data https://github.com/bonukai/MediaTracker/blob/cde1ad4dd12c35167e0e2635f2ac3b1ec8555fef/server/tests/migrations/migrations.test.ts#L614-L663

I tried to switch to sqlite, but I am getting following error: node-sqlite3 does not currently support RETURNING clause and I heavily relay on RETURNING

What operating system and CPU are you using?

[cgrinds]

Are your tests running on a comparable version of sqlite 3.36.0? How much data does your test have? Trying to understand why you aren't hitting https://github.com/knex/knex/issues/453#issuecomment-778733965 but I am.

System:
  Host: mn Kernel: 5.17.4-200.fc35.x86_64 arch: x86_64 bits: 64 Console: pty pts/4
    Distro: Fedora release 35 (Thirty Five)

[cgrinds]

another thought - correct me if I'm wrong, but I don't believe there is much personally identifiable in the database.

If that is correct and you want, I can send you the broken db. I'll overwrite the password. I know how frustrating it can be to fix something when it isn't easily reproducible.

[bonukai]

My sqlite version on Windows and in the docker image is 3.37.0

Your database would be very helpful, you can send it at [email protected]. Only personal information are your username, and reviews, password are hashed with Argon2

[bonukai]

Thank you so much.

This error is caused by missing/invalid foreign keys on mediaItem table. For example, in your database there is an episode

id	title	description	episodeNumber	seasonNumber	seasonAndEpisodeNumber	isSpecialEpisode	releaseDate	tvShowId	seasonId	tmdbId	imdbId	runtime
77	Good News About Hell	Mark is promoted to lead...	1	1	1001	0	2022-02-17	26	12	1982925

but mediaItem with id 26 is missing.

However, there is a mediaItem for this TV Show with id 471 and there is also an identical episode in episode table with id 3299.

[cgrinds]

Glad the DB was helpful! I guess the next questions are:

1. what caused the missing/invalid foreign keys and how do we prevent them in the future?
2. what's the best way to clean-up when this happens?
3. can the logging be improved to make these issues easier to troubleshoot?