All episodes are lost and rescanning is broken due to unique constraint

[EraYaN]

VERSION INFORMATION

Server Version: shokoanime/server:daily@sha256:cf77e24b0d0ec8fd593f1b382a54915db50c8524058ed11ff956cf6640cc2f9d

4.2.2.0 (7505d51)

LOG FILE

Log example

General Queue state change: Getting file info from UDP API: [C-W]_GTO_-_19_(640x480_DVD_H264)_[41FD90F1].mkv
16:32:02| CommandRequest_GetFile --- Get AniDB file info: 478
16:32:02| AniDBRateLimiter --- Time since last request is 40 ms, throttling for 2010.
16:32:04| AniDBRateLimiter --- Sending AniDB command.
16:32:04| AniDBUDPConnectionHandler --- AniDB UDP Call: (Using Unicode) FILE size=424901341&ed2k=935D1D487338B5FD387F40ECFAD73716&fmask=7F00C0D1F0&amask=00000000&s=QbgMq
16:32:04| AniDBUDPConnectionHandler --- AniDB Response: Received in 00.0415s
220 FILE
1486662|191|3736|2586|307097292||0|4097|high|DVD|japanese'english|english'english||GTO - 19 - Private Investigations - [C-W](41fd90f1).mkv|3|0|0|0
16:32:04| CommandRequest_ProcessFile --- Error processing CommandRequest_ProcessFile: {"VideoLocalID":478,"ForceAniDB":true,"SkipMyList":false,"CommandRequestID":866442,"Priority":3,"CommandType":1,"CommandID":"CommandRequest_ProcessFile_478","CommandDetails":"\u003CCommandRequest_ProcessFile\u003E\u003CVideoLocalID\u003E478\u003C/VideoLocalID\u003E\u003CForceAniDB\u003Etrue\u003C/ForceAniDB\u003E\u003CSkipMyList\u003Efalse\u003C/SkipMyList\u003E\u003C/CommandRequest_ProcessFile\u003E","DateTimeUpdated":"2023-09-01T16:26:59.179519","Processor":{"QueueState":{"message":"Getting file info from UDP API: {0}"}}}: NHibernate.Exceptions.GenericADOException: could not insert: [Shoko.Server.Models.AniDB_FileUpdate][SQL: INSERT INTO AniDB_FileUpdate (FileSize, Hash, HasResponse, UpdatedAt) VALUES (?, ?, ?, ?); select last_insert_rowid()]
 ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'UNIQUE constraint failed: AniDB_FileUpdate.FileSize, AniDB_FileUpdate.Hash'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at NHibernate.AdoNet.AbstractBatcher.DoExecuteReader(DbCommand cmd)
   at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(DbCommand cmd)
   at NHibernate.Id.IdentityGenerator.InsertSelectDelegate.ExecuteAndExtract(DbCommand insert, ISessionImplementor session)
   at NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder)
   --- End of inner exception stack trace ---
   at NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlCommandInfo sql, Object obj, ISessionImplementor session)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Object obj, ISessionImplementor session)
   at NHibernate.Action.EntityIdentityInsertAction.Execute()
   at NHibernate.Engine.ActionQueue.InnerExecute(IExecutable executable)
   at NHibernate.Engine.ActionQueue.Execute(IExecutable executable)
   at NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(Object entity, EntityKey key, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
   at NHibernate.Event.Default.AbstractSaveEventListener.PerformSave(Object entity, Object id, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
   at NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Object entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent event)
   at NHibernate.Impl.SessionImpl.FireSaveOrUpdate(SaveOrUpdateEvent event)
   at NHibernate.Impl.SessionImpl.SaveOrUpdate(Object obj)
   at Shoko.Server.Repositories.BaseDirectRepository`2.<>c__DisplayClass36_0.<Save>b__0() in /usr/src/app/source/Shoko.Server/Repositories/BaseDirectRepository.cs:line 152
   at Shoko.Server.Repositories.BaseRepository.Lock(Action action) in /usr/src/app/source/Shoko.Server/Repositories/BaseRepository.cs:line 24
   at Shoko.Server.Providers.AniDB.UDP.Info.RequestGetFile.ParseResponse(UDPResponse`1 response) in /usr/src/app/source/Shoko.Server/Providers/AniDB/UDP/Info/RequestGetFile.cs:line 264
   at Shoko.Server.Providers.AniDB.UDP.Generic.UDPRequest`1.Execute() in /usr/src/app/source/Shoko.Server/Providers/AniDB/UDP/Generic/UDPRequest.cs:line 39
   at Shoko.Server.Commands.AniDB.CommandRequest_GetFile.Process() in /usr/src/app/source/Shoko.Server/Commands/AniDB/CommandRequest_GetFile.cs:line 68
   at Shoko.Server.Commands.Generic.CommandRequestImplementation.ProcessCommand() in /usr/src/app/source/Shoko.Server/Commands/Generic/CommandRequestImplementation.cs:line 56
   at Shoko.Server.Commands.CommandRequest_ProcessFile.TryGetAniDBFileFromAniDB(SVR_VideoLocal vidLocal, Dictionary`2 animeIDs) in /usr/src/app/source/Shoko.Server/Commands/Import/CommandRequest_ProcessFile.cs:line 427
   at Shoko.Server.Commands.CommandRequest_ProcessFile.ProcessFile_AniDB(SVR_VideoLocal vidLocal) in /usr/src/app/source/Shoko.Server/Commands/Import/CommandRequest_ProcessFile.cs:line 123
   at Shoko.Server.Commands.CommandRequest_ProcessFile.Process() in /usr/src/app/source/Shoko.Server/Commands/Import/CommandRequest_ProcessFile.cs:line 68
   at Shoko.Server.Commands.Generic.CommandRequestImplementation.ProcessCommand() in /usr/src/app/source/Shoko.Server/Commands/Generic/CommandRequestImplementation.cs:line 56
16:32:04| CommandProcessorGeneral --- Deleting command request: CommandRequest_ProcessFile_478

DESCRIPTION

All episodes were lost a couple days ago. (All files are in the unrecognized list)

A rescan gives a ton of errors like the one in the log example. Not so sure what happened.

STEPS TO REPRODUCE

Run the server in an auto-updating fashion (my bad) and then it just kills all episodes (I notices since Jellyfin also turned up empty). All shows are still listed in the web ui, but all without content.

I'll try to grab full logs from the last restart (3 days ago I believe) when I'm home this weekend. And I believe the server makes database backups, so maybe I'm still fine.

[da3dsoul]

All episodes were lost a couple days ago. (All files are in the unrecognized list)

Can you elaborate what happened here? That's certainly not normal.

Shoko only makes auto backups when the schema updates, so your backup may be fairly old. If you have another system that backs it up (you should if you want to have auto updates), then you may be safe.

If you have a backup, try it, obviously.

The change that would matter for that constraint failure happened in January. Nice one... If you are going to have auto updates, you should also keep an eye on the logs once in a while. It's probably been bleeding errors this whole time.

I would also recommend being in our discord and letting @daily users ping you, as I tell people when I find out stuff like this happened and we can resolve it before it's been 8 months and recovery becomes difficult

[da3dsoul]

Oh right. The solution for the constraint issue...run this query in SQLite Studio:

DROP INDEX UIX_AniDB_FileUpdate;
CREATE INDEX IX_AniDB_FileUpdate ON AniDB_FileUpdate(FileSize, Hash);

[EraYaN]

Yeah I do have backups, but I also haven't looked at my anime in quite some time. So it's probably longer ago. The constraint problem and query did not fix the fact that all files are now unrecognized for some reason.

Like so:
'
Seems like the Anime_Episode table is indeed empty, weird. I'll go an do some more digging this weekend to try and root cause it. I do have Daily backups, just maybe not for long enough back, but I for sure have one from august 7 and that one has all episodes still.

I'll be back with more.

[da3dsoul]

CrossRef_File_Episode is where the mappings that define a file as recognized live. AnimeEpisode being empty is a bad sign though. Is AnimeSeries ok? If that's ok, you might be able to recover it by running Update All AniDB Info

[revam]

He might have used one of the faulty episode 'fixes' that were later fixed but required restoring from a backup i did a few weeks(/months?) back.

[revam]

Things to learn from this; don't enable auto update.

[da3dsoul]

Oh.... Didn't know there was an issue like that.

[EraYaN]

Well I guess I'll add the episode count to my monitoring stack and alert on it. Auto update has mostly worked so I'll leave it and backups are always close by. (ZFS snapshots have saved me so many times)

I have recovered and it did seem to be some problem in the last month but at least the current daily did not break it. (It has happened some time after august 7.)

I don't really feel like bisecting since it seems to be fixed.