SQLiteException: Constraint: FOREIGN KEY constraint failed

[sa2000 18]

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 4]

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 by dropshadow

[Luke 37229]

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 172]

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 Wednesday at 06:42 PM by kikinjo

[js28194 15]

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

[kikinjo 172]

I m on latest beta server, Version 4.9.0.18 beta

[pwhodges 1538]

The latest is 4.9.0.19.

Paul