Foreign key constraint failed error when inserting into ScannedMedia_Timings during episode import

[tikibozo]

Hi - I'm using the docker image, and I've noticed a new (to me anyway) error/crash (causes container restart).

credits | plex-credits-detect Database.ExecuteDBCommand exception: constraint failed credits | FOREIGN KEY constraint failed while executing SQL: INSERT INTO ScannedMedia_Timings (ScannedMedia_id, is_plex_intro, time_offset, end_time_offset, isCredits, isSilence, isBlackframes) VALUES (@ScannedMedia_id, @is_plex_intro, @time_offset, @end_time_offset, @isCredits, @isSilence, @isBlackframes); credits | With data: credits | ScannedMedia_id = /tv/show/episode.mp4 credits | is_plex_intro = False credits | time_offset = 0 credits | end_time_offset = 599.5936337763062 credits | isCredits = True credits | isSilence = False credits | isBlackframes = False credits exited with code 255

I'm using Sonarr-SMA which will transcode tv episodes during import. Plex Library settings auto-scan (filesystem change notifications or similar), and these monitoring settings (if other settings are relevant lmk):

[monitoring] monitorPlexIntros = true monitorDirectoryChanges = true

Seems to be a sort of orchestration "race condition" with credits running while plex is scanning and rescanning while the sonarr/sma stack does its thing.

I realize I could work around this behavior by turning off the plex auto-scan, but I like how quickly the overall system can process requests and make them available so I'd rather not. Thanks for the script, and for reading!

[cjmanca]

I believe this is already solved in the current develop branch. I still have a couple of things I'd like to add before releasing it to main, but it's currently pretty stable (I'm running the develop branch on my server right now).

cjmanca/plex-credits-detect:develop

[tikibozo]

Best answer! I'll give it a shot, thanks!

[tikibozo]

No luck with develop it looks like

[09/14/2022 05:31:55 INFO] - Audio credits match from 0.00 to 362.54. Duration: 362.54. SQLite error (787): abort at 31 in [INSERT INTO ScannedMedia_Timings (ScannedMedia_id, is_plex_intro, time_offset, end_time_offset, isCredits, isSilence, isBlackframes) VALUES (@ScannedMedia_id, @is_plex_intro, @time_offset, @e [09/14/2022 05:31:55 ERROR] - plex-credits-detect Database.ExecuteDBCommand exception: constraint failed FOREIGN KEY constraint failed while executing SQL: INSERT INTO ScannedMedia_Timings (ScannedMedia_id, is_plex_intro, time_offset, end_time_offset, isCredits, isSilence, isBlackframes) VALUES (@ScannedMedia_id, @is_plex_intro, @time_offset, @end_time_offset, @isCredits, @isSilence, @isBlackframes); [09/14/2022 05:31:55 ERROR] - With data: [09/14/2022 05:31:55 ERROR] - ScannedMedia_id = /tv/show/episode.mp4 [09/14/2022 05:31:55 ERROR] - is_plex_intro = False [09/14/2022 05:31:55 ERROR] - time_offset = 0 [09/14/2022 05:31:55 ERROR] - end_time_offset = 362.54282200872814 [09/14/2022 05:31:55 ERROR] - isCredits = True [09/14/2022 05:31:55 ERROR] - isSilence = False [09/14/2022 05:31:55 ERROR] - isBlackframes = False [09/14/2022 05:31:59 INFO] - Loading global config file: /config/plex-credits-detect/fingerprint.ini SQLite notice (283): recovered 55 frames from WAL file /config/plex-credits-detect/database/fingerprintMedia.db-wal

[09/14/2022 05:32:00 INFO] - Syncing newly added episodes from plex...

[cjmanca]

Going back to your original message:

I'm using Sonarr-SMA which will transcode tv episodes during import. Seems to be a sort of orchestration "race condition" with credits running while plex is scanning and rescanning while the sonarr/sma stack does its thing.

This shouldn't affect anything. Missing or changed files may give a "file not found" exception, but wouldn't affect these database inserts. The ScannedMedia_Timings table is the PCD private database, which nothing by PCD should be accessing, so as long as you're not running more than one instance of PCD, then there shouldn't be any race conditions. PCD is single threaded, and only detects new intros/directory changes in between processing tasks, not during.

I realize I could work around this behavior by turning off the plex auto-scan, but I like how quickly the overall system can process requests and make them available so I'd rather not. Thanks for the script, and for reading!

Plex auto-scan won't cause or affect this in any way, turning it off wouldn't solve this issue.

I've been running the tool on the develop version for a couple weeks and haven't seen this error once in that time. I can't figure out where it would be coming from since I'm forcing an update of the main entry that the foreign key links to prior to inserting to make sure the IDs match.

The only thing I can think of that may cause it would be running multiple instances of PCD which are accessing the same privatre database.

Could you double check to make sure you only have a single instance of PCD running?

[tikibozo]

Interesting, thanks. I'm running it in docker, and there's only one container. Don't see any additional processes running it.

Is it expected for the 'dotnet plex-credits-direct.dll' process to spawn a lot of sub-instances?

[cjmanca]

No, there should only be one instance, it doesn't spawn sub-processes. If you only have one container and aren't running it natively then there should only be one instance running

[cjmanca]

Is it the same series all the time? Does the bug happen the next time you restart it too immediately when it tries to pick up from where it left off?

[tikibozo]

it's whatever is importing, and it seems to pick up on the next thing to import when the container restarts. So it works, it's just chatty (I have alerts that send me push notifications..)

That said, I'm importing something else now and it seems happy. Maybe there was a delay in picking up your recent change into the container? (docker-compose pull/up runs nightly.)

[tikibozo]

Jinxed it. Got a few instances. Is it possible the there are multiple worker threads (or similar) that pick up the same file to process?

Log snippet with more context

[tikibozo]

Oh actually - notice in that log that it's picking up both the .mkv and then the mp4 version of the file (due to the sma transcode on import.) Could processing both at the same time cause this?

[cjmanca]

Hmm, it could be related to that. Plex would see both of them as the same episode, and use a single metadata ID for both. PCD would see this as the file being renamed (it would see one, update the database to reflect that path, then see the other after and update the database as though it were renamed from one to the other).

Theoretically that should be fine though, it's just wasted processing power. It should just treat it like it was renamed when processing the second one.

That said - the way that transcode is being done (in place in the directory rather than using a temp directory outside of the media paths) is bad practice, and will cause a lot of thrashing with plex, while plex sees file changes and repeatedly attempts to re-scan/analyze and detect intros and generate thumbnails. I'd recommend looking into something like Tdarr for your transcoding instead.

I put up a new develop release about an hour ago that forces a db update on the episode before putting the timings in, see if that helps.

[demize]

Plex would see both of them as the same episode, and use a single metadata ID for both.

I'm experiencing what I think is a similar issue. Getting the same basic error, and I'm guessing because two files share the same metadata ID, but it's actually because it's a multi-part episode:

SQLite error (787): abort at 31 in [INSERT INTO ScannedMedia_Timings  (ScannedMedia_id, is_plex_intro, time_offset, end_time_offset, isCredits, isSilence, isBlackframes) VALUES  (@ScannedMedia_id, @is_plex_intro, @time_offset, @e
[10/16/2022 21:27:10 ERROR] - plex-credits-detect Database.ExecuteDBCommand exception: constraint failed
FOREIGN KEY constraint failed while executing SQL: INSERT INTO ScannedMedia_Timings  (ScannedMedia_id, is_plex_intro, time_offset, end_time_offset, isCredits, isSilence, isBlackframes) VALUES  (@ScannedMedia_id, @is_plex_intro, @time_offset, @end_time_offset, @isCredits, @isSilence, @isBlackframes);
[10/16/2022 21:27:10 ERROR] - With data: 
[10/16/2022 21:27:10 ERROR] - ScannedMedia_id = /{series}/Season 1/{series} - s01e96 - Part 2 [x264 AC3 [EN]] [DVD].mkv
[10/16/2022 21:27:10 ERROR] - is_plex_intro = False
[10/16/2022 21:27:10 ERROR] - time_offset = 480.4658460083008
[10/16/2022 21:27:10 ERROR] - end_time_offset = 511.39762980902105
[10/16/2022 21:27:10 ERROR] - isCredits = True
[10/16/2022 21:27:10 ERROR] - isSilence = False
[10/16/2022 21:27:10 ERROR] - isBlackframes = False

Already updated to the develop branch and it didn't fix the issue, though it does look like PCD can work past it as long as I let it keep restarting. It had to restart a few times thanks to this crash, but it's worked past that series and done a few more with no issue now (though I have very few shows where episodes are split between files; this one is a victim of TheTVDB's cartoon policies, unfortunately).

Well, uh, I guess I was wrong--just got the exact same error on a movie, which only has one file. Any way for me to collect better logs so I can open another issue?

[cjmanca]

I've seen it myself now too. It's still on my radar, I've just been busy!

[tikibozo]

fwiw i disabled monitorDirectoryChanges which has reduced the collision rate. Thanks for looking!