Sonarr v4 Develop Can't Import

[patrioticaction]

Sonarr version (exact version): 4.0.0.391-ls414 by linuxserver.io
Mono version (if Sonarr is not running on Windows): 6.0.13
OS: linuxserver container on Rocky 9.1
Debug logs:
Including a single stack trace, hopefully that isn’t too spammy.

Message
Couldn't import episode /downloads/tv/<redacted>.mkv: Error parsing column 12 (AirDateUtc=1 - Int64)
Exception
System.Data.DataException: Error parsing column 12 (AirDateUtc=1 - Int64)
 ---> System.FormatException: String '' was not recognized as a valid DateTime.
   at System.DateTimeParse.ParseExactMultiple(ReadOnlySpan`1 s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
   at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, SQLiteDateFormats format, DateTimeKind kind, String formatString)
   at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
   at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, Int32 index, SQLiteType typ)
   at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
   at Deserializeb295b54c-13e0-4e87-aa5a-36942a6998f9(IDataReader )
   --- End of inner exception stack trace ---
   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in /_/Dapper/SqlMapper.cs:line 3706
   at Deserializeb295b54c-13e0-4e87-aa5a-36942a6998f9(IDataReader )
   at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)
   at NzbDrone.Core.Datastore.SqlMapperExtensions.Query[T](IDatabase db, String sql, Object param)
   at NzbDrone.Core.Datastore.TableMapping.<>c.<Map>b__5_34(IDatabase db, EpisodeFile parent)
   at NzbDrone.Core.Datastore.LazyLoaded`2.LazyLoad()
   at NzbDrone.Core.MediaFiles.MediaFileService.Delete(EpisodeFile episodeFile, DeleteMediaFileReason reason)
   at NzbDrone.Core.MediaFiles.UpgradeMediaFileService.UpgradeEpisodeFile(EpisodeFile episodeFile, LocalEpisode localEpisode, Boolean copyOnly)
   at NzbDrone.Core.MediaFiles.EpisodeImport.ImportApprovedEpisodes.Import(List`1 decisions, Boolean newDownload, DownloadClientItem downloadClientItem, ImportMode importMode)

Description of issue: Every single episode I try to import gives the same error. It used to happen once in a while but now my entire queue is backed up with these.

[patrioticaction]

I can’t downgrade from v4 so I am coming here because I would like to fix forward. I have far too much data to try and reimport to v3. Was I silly to have upgraded to a development branch? Yes. I am sorry but I will help to debug my very best.

[markus101]

Looks like the Episodes table has some bad data for the AirDateUtc column for those episodes, Sonarr doesn’t set it to empty string though.

Executing this SQL on the DB might solve it:

UPDATE Episodes SET AirDateUtc = NULL WHERE AirDateUtc = ''

[patrioticaction]

Markus101,

Thank you!

In addition to that query, I had to run two additional ones as well. I am summarizing the fix for people coming here later.

# backup sonarr.db and open a new sqlite prompt
cp ./sonarr.db ./sonarr.db.bak
sqlite3 ./sonarr.db

# now run the following queries
UPDATE Episodes SET AirDateUtc = NULL WHERE AirDateUtc = '';
UPDATE Episodes SET Ratings = NULL WHERE Ratings = '';
UPDATE Episodes SET Images = NULL WHERE Images = '';