SQLiteException: Constraint: FOREIGN KEY constraint failed

[sa2000 403]

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

[dropshadow 9]

this exact thing just happened to me, here are logs from library scan:

2024-05-02 21:23:22.216 Error SqliteItemRepository: Error saving items
	*** Error Report ***
	Version: 4.8.5.0
	Command line: C:\Users\Administrator\AppData\Roaming\Emby-Server\system\EmbyServer.dll -service
	Operating system: Microsoft Windows 10.0.17763
	Framework: .NET 6.0.29
	OS/Process: x64/x64
	Runtime: C:/Users/Administrator/AppData/Roaming/Emby-Server/system/System.Private.CoreLib.dll
	Processor count: 8
	Data path: C:\Users\Administrator\AppData\Roaming\Emby-Server
	Application path: C:\Users\Administrator\AppData\Roaming\Emby-Server\system
	SQLitePCL.pretty.SQLiteException: Constraint: FOREIGN KEY constraint failed
	SQLitePCL.pretty.SQLiteException: Exception of type 'SQLitePCL.pretty.SQLiteException' was thrown.
	   at SQLitePCL.pretty.SQLiteException.CheckOk(sqlite3 db, Int32 rc)
	   at SQLitePCL.pretty.StatementImpl.MoveNext()
	   at SQLitePCL.pretty.DatabaseConnection.Execute(IDatabaseConnection This, String sql)
	   at Emby.Server.Implementations.Data.SqliteItemRepository.SaveItemsInTranscation(IDatabaseConnection db, List`1 tuples, MetadataRefreshOptions metadataRefreshOptions)
	   at Emby.Server.Implementations.Data.SqliteItemRepository.SaveItems(List`1 items, MetadataRefreshOptions metadataRefreshOptions, Action`1 afterSave, Boolean disableForeignKeys, CancellationToken cancellationToken)
	Source: SQLitePCL.pretty
	TargetSite: Void CheckOk(SQLitePCLEx.sqlite3, Int32)
	
2024-05-02 21:23:22.217 Error ProviderManager: Error validating children for Movies 13870 \\omega.wangfei.com\ZEUS\Media\Movies
	*** Error Report ***
	Version: 4.8.5.0
	Command line: C:\Users\Administrator\AppData\Roaming\Emby-Server\system\EmbyServer.dll -service
	Operating system: Microsoft Windows 10.0.17763
	Framework: .NET 6.0.29
	OS/Process: x64/x64
	Runtime: C:/Users/Administrator/AppData/Roaming/Emby-Server/system/System.Private.CoreLib.dll
	Processor count: 8
	Data path: C:\Users\Administrator\AppData\Roaming\Emby-Server
	Application path: C:\Users\Administrator\AppData\Roaming\Emby-Server\system
	SQLitePCL.pretty.SQLiteException: Constraint: FOREIGN KEY constraint failed
	SQLitePCL.pretty.SQLiteException: Exception of type 'SQLitePCL.pretty.SQLiteException' was thrown.
	   at SQLitePCL.pretty.SQLiteException.CheckOk(sqlite3 db, Int32 rc)
	   at SQLitePCL.pretty.StatementImpl.MoveNext()
	   at SQLitePCL.pretty.DatabaseConnection.Execute(IDatabaseConnection This, String sql)
	   at Emby.Server.Implementations.Data.SqliteItemRepository.SaveItemsInTranscation(IDatabaseConnection db, List`1 tuples, MetadataRefreshOptions metadataRefreshOptions)
	   at Emby.Server.Implementations.Data.SqliteItemRepository.SaveItems(List`1 items, MetadataRefreshOptions metadataRefreshOptions, Action`1 afterSave, Boolean disableForeignKeys, CancellationToken cancellationToken)
	   at Emby.Server.Implementations.Data.SqliteItemRepository.SaveItems(List`1 items, MetadataRefreshOptions metadataRefreshOptions, CancellationToken cancellationToken)
	   at Emby.Server.Implementations.Library.LibraryManager.CreateItems(List`1 items, BaseItem parent, MetadataRefreshOptions metadataRefreshOptions, BaseItem[] collectionFolders, Boolean triggerItemAdded, CancellationToken cancellationToken)
	   at MediaBrowser.Controller.Entities.BaseItem.ValidateChildrenInternal(IProgress`1 progress, CancellationToken cancellationToken, Boolean recursive, Boolean refreshChildMetadata, BaseItem[] collectionFolders, LibraryOptions libraryOptions, Dictionary`2 newItemIds, MetadataRefreshOptions refreshOptions, IDirectoryService directoryService)
	   at Emby.Providers.Manager.ProviderManager.RefreshCollectionFolderChildren(MetadataRefreshOptions options, CollectionFolder collectionFolder, CancellationToken cancellationToken)
	Source: SQLitePCL.pretty
	TargetSite: Void CheckOk(SQLitePCLEx.sqlite3, Int32)

i created a new library and accidentally made the path the parent folder of the entire media library. ALL items in ALL libraries disappeared. deleting the offending library did not help. had to delete and re create all libraries, PITA.

Edited May 3, 2024 by dropshadow

[Luke 40064]

HI, this issue is on my list for review. For now you may just have to start with a fresh database. Apologies for the disruption.

[kikinjo 197]

Same here happened to me, added another folder in library, 4 folders were already in library. Btw if u add 5th folder u cant see it in emby admin gui at all

After adding folder, whole server lost content completely...after 2 hours of trying to fix it..unsuccessfully, i restored backup...

Same error messages as already posted, Sqlite mostly.

About number of folders....here is screenshot, i have 5 folders in this library..only 4 are visible in UI , on adding 5th i got sqllite crash....

 

 

Edited May 15, 2024 by kikinjo

[js28194 27]

I have no issues adding 5 folders.  Don't have this problem on Emby Server 4.7.13.0

[kikinjo 197]

I m on latest beta server, Version 4.9.0.18 beta

[pwhodges 1855]

The latest is 4.9.0.19.

Paul

[Happy2Play 9441]

On 4/11/2024 at 3:54 AM, sa2000 said:

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

So you are saying having starting with libraries then changing to unsupported library as the new library is parent to existing libraries no longer show content in child libraries ie expected behavior as there now technically are being shown in parent.  And removing the parent not basically nuke the db do to "Constraint: FOREIGN KEY constraint failed" issues?

 

[Happy2Play 9441]

On 5/15/2024 at 10:33 AM, kikinjo said:

Same here happened to me, added another folder in library, 4 folders were already in library. Btw if u add 5th folder u cant see it in emby admin gui at all

After adding folder, whole server lost content completely...after 2 hours of trying to fix it..unsuccessfully, i restored backup...

Can't reproduce a number of folders issue in my testing in stable or beta

[sa2000 403]

On 07/06/2024 at 17:06, Happy2Play said:

So you are saying having starting with libraries then changing to unsupported library as the new library is parent to existing libraries no longer show content in child libraries ie expected behavior as there now technically are being shown in parent.

What I am saying is that it should not have allowed me to end up with an unsupported library. It was an error that one library had inadvertently a parent directory included and added to the existing folder path.  The impact was massive because this added parent directory as a root folder to this library was a parent directory in lots of other libraries and the media disappeared from these libraries.   There should be no concept of parent and child libraries and each library ought to be self contained. But I do understand that the design may not allow for that - in which case we need to warn users when a mistake is made that could have catastrophic impact.

I have not revisited since the original error and tests - I would need to setup a test server to see if I am still allowed to have overlapping directories in library definitions and what the impact is if it is still permitted

 

[Happy2Play 9441]

I can't reproduce the error but can reproduce library showing no items in the parent/subfolder scenario removing parent folder from library setup cause removal of all folders then readd.  

[sa2000 403]

2 hours ago, Happy2Play said:

I can't reproduce the error but can reproduce library showing no items in the parent/subfolder scenario removing parent folder from library setup cause removal of all folders then readd.  

Thanks 

Would be good if we can stop / warn users when inadvertently adding a parent folder causing overlaps. And is there a way to limit the impact to just the library that has the incorrect overlapping directories ? cc @Luke

Edited June 11, 2024 by sa2000

[podonnell 42]

I had this error occur once but I think it is corrected. Is there anything I can search my log for at this time to see if I have any lingering issues or corruption in the database?

[sa2000 403]

On 09/08/2024 at 18:20, podonnell said:

Is there anything I can search my log for at this time to see if I have any lingering issues or corruption in the database

You could search for SQLiteException in the logs and see if you are getting any and see what the errors are.

In my case, to recover, I recreated from scratch all the libraries that had library folder paths that were below the parent folder that i inadvertently included as a folder path in another library 

Edited August 12, 2024 by sa2000

[Luke 40064]

@sa2000does this still reproduce on a new install of 4.8.10?

[sa2000 403]

18 hours ago, Luke said:

does this still reproduce on a new install of 4.8.10

I will go through the same sequence on a new test server and report back

[sa2000 403]

On 21/10/2024 at 18:38, Luke said:

does this still reproduce on a new install of 4.8.10?

An improvement in that when the parent folder is added as another folder path, the media items in the library and other library that had a base folder below this parent folder remained accessible and no issue with access to the media whilst we had overlapping paths in a library

However, when the erroneous parent folder was removed from the library, then the SQLITE error arose and all media in this library disappeared as well as the media in the 2nd library which had the base folder below this parent folder

I will pass on the captured diagnostics 

So comparing to the original outcome here , step 2 is now good with no issues but what followed from step 3 is the same as before

[sa2000 403]

Updating this with the latest position - in Emby Server beta 4.9.0.41

- When a parent folder is inadvertantly added to a library folder paths, the media within that library will remain accessible

- Any other library with a folder path that is a child or grandchild etc ... of the parent folder added in the other library, may lose its content

- When the library that had the parent folder added is corrected by removing that parent folder from the paths, the library will be adjusted and no SQLITE EXCEPTION is given. Furthermore, other libraries that were impacted by adding the parent folder to this library, should now have their content restored and accessible

 

Edited March 11 by sa2000

[Carlo 4552]

@Luke
When showing the directory tree for selecting a library mount point, could we dim locations already mounted and not allow selection?

A user could still type a location to override this for advanced uses when it's ok to do this for the same library type.

This could help preventing the use of paths already part of an existing library.

[Luke 40064]

Actually at this point we’re getting closer to them being mostly ok.

[Carlo 4552]

27 minutes ago, Luke said:

Actually at this point we’re getting closer to them being mostly ok.

What being mostly ok?
Do you mean mount points that are already part of another library?

[Luke 40064]

56 minutes ago, Carlo said:

What being mostly ok?
Do you mean mount points that are already part of another library?

Yes. 

[Luke 40064]

It's still not really recommended unless library options are identical, but it should mostly be fine.

[deelight 2]

Same problem here.
My main media library was /var/lib/emby/media and I tried adding the path /var/lib/emby/media2, but I accidentally added /var/lib (I pressed enter because I expected the file browser to refresh the view to show me everything that was under /var/lib).
All libraries now appears empty.

[Luke 40064]

4 hours ago, deelight said:

Same problem here.
My main media library was /var/lib/emby/media and I tried adding the path /var/lib/emby/media2, but I accidentally added /var/lib (I pressed enter because I expected the file browser to refresh the view to show me everything that was under /var/lib).
All libraries now appears empty.

HI, did you remove what you accidentally added?