SQLiteException: Constraint: FOREIGN KEY constraint failed

[sa2000 3]

I setup an emby server and noticed that a number of libraries show "No items found". I tracked this to an SQLite error when scanning these libraries. 

The error I believe relates to the ParentId in the MediaItems library.db table.

It appears that the issue arose with these libraries after I inadvertently added a parent folder as a media folder in one of my libraries. So ended up with overlapping folders for that library. When I noticed this erroneous folder path, I removed it and that was when the SQLite Foreign Key constraint errors started.

I confirmed this was the cause by creating a test emby server with just 4 libraries

The following were my repro steps for the problem

1. Create these libraries, 2 music libraries with one artist/album each, 2 photos libraries with a couple of photos each

Library: Music1    Folder: H:\Emby\TestMedia\Music1

Library: Photos1  Folder: H:\Emby\TestMedia\Photos1

Library: Music2    Folder: H:\Emby\TestMedia\ParentFolder\Music2

Library: Photos2   Folder: H:\Emby\TestMedia\ParentFolder\Photos2

At this point all media within these libraries were there and accessible - this relates to db snapshot taken at 15:00

2. Edit Music2 library to add folder path H:\Emby\TestMedia\ParentFolder

The database snapshot taken after this (snapshot 2 at 15:07) show all the media items (photos and music tracks) for Photos 2 and Music2 library disappearing

3. Edit Music2 library to remove the erroneous folder path H:\Emby\TestMedia\ParentFolder

The Foreign Key Constraint errors start

3rd DB snapshot taken now - at 15:12

4, Libraries for Music2 and Photos2 show No media items 

a 4th DB snapshot was taken - at 15:16

To resolve the issue, I had to delete all folder paths for the Music2 and Photos2 libraries and then add the correct paths.

(On my main server where the issue arose first time, I ended up having to delete the libraries that were for media below the parent folder - and recreate them)

Diagnostics attached here are from the test/repro environment. The excel db snapshots file has library.db snapshots exports taken at 15:00, 15:07, 15:12 and 15:16

The log file covers the whole period from start. Debug logging enabled after step 1

Would be good to reject adding a folder that would result in overlapping folders.

(one thing that slowed down my attempts to resolve the issue on my main server, is that a scan cancels any existing scan - would it be possible to queue scan requests) - only way i managed to get the scans to complete is by opening the dashboard view and waiting for the scan to complete before addressing the next library) 

embyserver [tokens redacted].txt

 

DB Snapshots.xlsx