2024-08-27, 12:25 AM
Finally figured this out. To any future readers, please be warned that these steps are potentially dangerous to your instance, and should not be attempted without backups and knowing how to restore them.
The fix was found and applied using sqlite3, so if the instance of Jellyfin does not have this installed (such as in a Docker setup like mine), you will have to either apply the fixes on the host system or on another machine and transfer the corrected database. These steps will assume you are working on the system where you're using sqlite3 to run the commands, and Jellyfin has been turned off or you're not working on the live dabatase.
-Change your working directory to the location of library.db
-Issue this command: sqlite3 library.db "PRAGMA integrity_check;"
-This should return errors such as "row 177 missing from index UserDatasIndex5", in my instance it returned errors for indexes 1-5
-First attempt to reindex using this command as a template: sqlite3 library.db "reindex UserDatasIndex1;"
-If an index is unique, it likely won't reindex and will need to be dropped and recreated as non-unique (this is what happened to me for UserDatasIndex1)
-To drop the UserDatasIndex1, I used this command: sqlite3 library.db "DROP INDEX IF EXISTS UserDatasIndex1;"
-To recreate the index, first attempt this command: sqlite3 library.db "CREATE UNIQUE INDEX UserDatasIndex1 on UserDatas (key, userId);"
-If that doesn't work, use this command instead: sqlite3 library.db "CREATE INDEX UserDatasIndex1 ON UserDatas (key, userId);"
If you're working on the live database while Jellyfin is turned off, you now just need to turn it back on and everything should work. If you were working on a copy of the database, you'll need to turn Jellyfin off and replace the live database with your copy.
The fix was found and applied using sqlite3, so if the instance of Jellyfin does not have this installed (such as in a Docker setup like mine), you will have to either apply the fixes on the host system or on another machine and transfer the corrected database. These steps will assume you are working on the system where you're using sqlite3 to run the commands, and Jellyfin has been turned off or you're not working on the live dabatase.
-Change your working directory to the location of library.db
-Issue this command: sqlite3 library.db "PRAGMA integrity_check;"
-This should return errors such as "row 177 missing from index UserDatasIndex5", in my instance it returned errors for indexes 1-5
-First attempt to reindex using this command as a template: sqlite3 library.db "reindex UserDatasIndex1;"
-If an index is unique, it likely won't reindex and will need to be dropped and recreated as non-unique (this is what happened to me for UserDatasIndex1)
-To drop the UserDatasIndex1, I used this command: sqlite3 library.db "DROP INDEX IF EXISTS UserDatasIndex1;"
-To recreate the index, first attempt this command: sqlite3 library.db "CREATE UNIQUE INDEX UserDatasIndex1 on UserDatas (key, userId);"
-If that doesn't work, use this command instead: sqlite3 library.db "CREATE INDEX UserDatasIndex1 ON UserDatas (key, userId);"
If you're working on the live database while Jellyfin is turned off, you now just need to turn it back on and everything should work. If you were working on a copy of the database, you'll need to turn Jellyfin off and replace the live database with your copy.