Problem with upgrading to v3 from v2 - database error

Sonarr version (exact version): 3.0.6
Mono version (if Sonarr is not running on Windows): 6.8
OS: debian
Debug logs:
Description of issue: when upgrading from 2.0.0.5344 to 3.0.6, v3 is not able to read the v2 database - error in pastebin log below

i backed up in v2
installed v3
v3 started
chose to restore the saved v2 backup from the disk
v3 won’t start

log - https://pastebin.com/1zvEUWC8

database disk image is malformed

https://wiki.servarr.com/sonarr/faq#i-am-getting-an-error-database-disk-image-is-malformed

what i don’t understand is that the database is fine as i am able to reinstall v2 and restore the database and it works.

isn’t v3 meant to be able to take a v2 database and work with it?

No it’s not fine, it’s just not running into that particular error in v2 because it’s not hitting the affected rows that it is during the migration. Since it is working with v2 there is a pretty good chance that the steps to copy the contents of the damaged DB to a new DB will be successful.

you are indeed correct

# sqlite3 nzbdrone.db "PRAGMA integrity_check"
database disk image is malformed

then ran

# sqlite3 nzbdrone.db ".recover" | sqlite3 sonarrv3.db
# sqlite3 sonarrv3.db "PRAGMA integrity_check"
ok

then
cp sonarrv3.db /var/lib/sonarr/sonarr.db

different problem now

2021-09-29 02:05:08.4|Error|MigrationLogger|System.FormatException: String was not recognized as a valid DateTime.
  at System.DateTimeParse.ParseExactMultiple (System.ReadOnlySpan`1[T] s, System.String[] formats, System.Globalization.DateTimeFormatInfo dtfi, System.Globalization.DateTimeStyles style) [0x0002f] in <12b418a7818c4ca0893feeaaf67f1e7f>:0
  at System.DateTime.ParseExact (System.String s, System.String[] formats, System.IFormatProvider provider, System.Globalization.DateTimeStyles style) [0x00025] in <12b418a7818c4ca0893feeaaf67f1e7f>:0
  at System.Data.SQLite.SQLiteConvert.ToDateTime (System.String dateText, System.Data.SQLite.SQLiteDateFormats format, System.DateTimeKind kind, System.String formatString) [0x000f9] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLiteConvert.ToDateTime (System.String dateText) [0x00000] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLiteConvert.ToDateTime (System.IntPtr ptr, System.Int32 len) [0x00009] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLite3.GetDateTime (System.Data.SQLite.SQLiteStatement stmt, System.Int32 index) [0x00079] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLiteDataReader.GetDateTime (System.Int32 i) [0x00034] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at NzbDrone.Core.Datastore.Migration.add_download_history.InitialImportedDownloadHistory (System.Data.IDbConnection conn, System.Data.IDbTransaction tran) [0x00047] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Core\Datastore\Migration\139_add_download_history.cs:61
  at FluentMigrator.Runner.Processors.SQLite.SQLiteProcessor.Process (FluentMigrator.Builders.Execute.PerformDBOperationExpression expression) [0x00038] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Builders.Execute.PerformDBOperationExpression.ExecuteWith (FluentMigrator.IMigrationProcessor processor) [0x00000] in <c16130ff2bfb4746b4fb36de17115e3e>:0
  at FluentMigrator.Runner.MigrationRunner+<>c__DisplayClass72_0.<ExecuteExpressions>b__1 () [0x00011] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.StopWatch.Time (System.Action action) [0x00006] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.MigrationRunner.AnnounceTime (System.String message, System.Action action) [0x0000c] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.MigrationRunner.ExecuteExpressions (System.Collections.Generic.ICollection`1[T] expressions) [0x0006c] in <137fb96feee44f379d6a8fba4e172d1c>:0

[v3.0.6.1265] System.FormatException: String was not recognized as a valid DateTime.
  at System.DateTimeParse.ParseExactMultiple (System.ReadOnlySpan`1[T] s, System.String[] formats, System.Globalization.DateTimeFormatInfo dtfi, System.Globalization.DateTimeStyles style) [0x0002f] in <12b418a7818c4ca0893feeaaf67f1e7f>:0
  at System.DateTime.ParseExact (System.String s, System.String[] formats, System.IFormatProvider provider, System.Globalization.DateTimeStyles style) [0x00025] in <12b418a7818c4ca0893feeaaf67f1e7f>:0
  at System.Data.SQLite.SQLiteConvert.ToDateTime (System.String dateText, System.Data.SQLite.SQLiteDateFormats format, System.DateTimeKind kind, System.String formatString) [0x000f9] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLiteConvert.ToDateTime (System.String dateText) [0x00000] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLiteConvert.ToDateTime (System.IntPtr ptr, System.Int32 len) [0x00009] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLite3.GetDateTime (System.Data.SQLite.SQLiteStatement stmt, System.Int32 index) [0x00079] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLiteDataReader.GetDateTime (System.Int32 i) [0x00034] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at NzbDrone.Core.Datastore.Migration.add_download_history.InitialImportedDownloadHistory (System.Data.IDbConnection conn, System.Data.IDbTransaction tran) [0x00047] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Core\Datastore\Migration\139_add_download_history.cs:61
  at FluentMigrator.Runner.Processors.SQLite.SQLiteProcessor.Process (FluentMigrator.Builders.Execute.PerformDBOperationExpression expression) [0x00038] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Builders.Execute.PerformDBOperationExpression.ExecuteWith (FluentMigrator.IMigrationProcessor processor) [0x00000] in <c16130ff2bfb4746b4fb36de17115e3e>:0
  at FluentMigrator.Runner.MigrationRunner+<>c__DisplayClass72_0.<ExecuteExpressions>b__1 () [0x00011] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.StopWatch.Time (System.Action action) [0x00006] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.MigrationRunner.AnnounceTime (System.String message, System.Action action) [0x0000c] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.MigrationRunner.ExecuteExpressions (System.Collections.Generic.ICollection`1[T] expressions) [0x0006c] in <137fb96feee44f379d6a8fba4e172d1c>:0

2021-09-29 02:05:08.7|Fatal|ConsoleApp|EPIC FAIL!

[v3.0.6.1265] System.FormatException: String was not recognized as a valid DateTime.
  at System.DateTimeParse.ParseExactMultiple (System.ReadOnlySpan`1[T] s, System.String[] formats, System.Globalization.DateTimeFormatInfo dtfi, System.Globalization.DateTimeStyles style) [0x0002f] in <12b418a7818c4ca0893feeaaf67f1e7f>:0
  at System.DateTime.ParseExact (System.String s, System.String[] formats, System.IFormatProvider provider, System.Globalization.DateTimeStyles style) [0x00025] in <12b418a7818c4ca0893feeaaf67f1e7f>:0
  at System.Data.SQLite.SQLiteConvert.ToDateTime (System.String dateText, System.Data.SQLite.SQLiteDateFormats format, System.DateTimeKind kind, System.String formatString) [0x000f9] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLiteConvert.ToDateTime (System.String dateText) [0x00000] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLiteConvert.ToDateTime (System.IntPtr ptr, System.Int32 len) [0x00009] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLite3.GetDateTime (System.Data.SQLite.SQLiteStatement stmt, System.Int32 index) [0x00079] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at System.Data.SQLite.SQLiteDataReader.GetDateTime (System.Int32 i) [0x00034] in <61a20cde294d4a3eb43b9d9f6284613b>:0
  at NzbDrone.Core.Datastore.Migration.add_download_history.InitialImportedDownloadHistory (System.Data.IDbConnection conn, System.Data.IDbTransaction tran) [0x00047] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Core\Datastore\Migration\139_add_download_history.cs:61
  at FluentMigrator.Runner.Processors.SQLite.SQLiteProcessor.Process (FluentMigrator.Builders.Execute.PerformDBOperationExpression expression) [0x00038] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Builders.Execute.PerformDBOperationExpression.ExecuteWith (FluentMigrator.IMigrationProcessor processor) [0x00000] in <c16130ff2bfb4746b4fb36de17115e3e>:0
  at FluentMigrator.Runner.MigrationRunner+<>c__DisplayClass72_0.<ExecuteExpressions>b__1 () [0x00011] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.StopWatch.Time (System.Action action) [0x00006] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.MigrationRunner.AnnounceTime (System.String message, System.Action action) [0x0000c] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.MigrationRunner.ExecuteExpressions (System.Collections.Generic.ICollection`1[T] expressions) [0x000a5] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.MigrationRunner.ExecuteMigration (FluentMigrator.IMigration migration, System.Action`2[T1,T2] getExpressions) [0x0005a] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.MigrationRunner.ApplyMigrationUp (FluentMigrator.Infrastructure.IMigrationInfo migrationInfo, System.Boolean useTransaction) [0x0010f] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at FluentMigrator.Runner.MigrationRunner.MigrateUp (System.Boolean useAutomaticTransactionManagement) [0x000af] in <137fb96feee44f379d6a8fba4e172d1c>:0
  at NzbDrone.Core.Datastore.Migration.Framework.MigrationController.Migrate (System.String connectionString, NzbDrone.Core.Datastore.Migration.Framework.MigrationContext migrationContext) [0x000a2] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Core\Datastore\Migration\Framework\MigrationController.cs:51
  at NzbDrone.Core.Datastore.DbFactory.CreateMain (System.String connectionString, NzbDrone.Core.Datastore.Migration.Framework.MigrationContext migrationContext) [0x0000b] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Core\Datastore\DbFactory.cs:117
  at NzbDrone.Core.Datastore.DbFactory.Create (NzbDrone.Core.Datastore.Migration.Framework.MigrationContext migrationContext) [0x00027] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Core\Datastore\DbFactory.cs:82
  at NzbDrone.Core.Datastore.DbFactory.Create (NzbDrone.Core.Datastore.Migration.Framework.MigrationType migrationType) [0x00000] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Core\Datastore\DbFactory.cs:70
  at NzbDrone.Core.Datastore.DbFactory.RegisterDatabase (NzbDrone.Common.Composition.IContainer container) [0x00000] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Core\Datastore\DbFactory.cs:48
  at NzbDrone.Host.NzbDroneServiceFactory.Start () [0x00037] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Host\ApplicationServer.cs:60
  at NzbDrone.Host.Router.Route (NzbDrone.Host.ApplicationModes applicationModes) [0x0007f] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Host\Router.cs:56
  at NzbDrone.Host.Bootstrap.Start (NzbDrone.Host.ApplicationModes applicationModes, NzbDrone.Common.EnvironmentInfo.StartupContext startupContext) [0x0003d] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Host\Bootstrap.cs:78
  at NzbDrone.Host.Bootstrap.Start (NzbDrone.Common.EnvironmentInfo.StartupContext startupContext, NzbDrone.Host.IUserAlert userAlert, System.Action`1[T] startCallback) [0x0007f] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Host\Bootstrap.cs:41
  at NzbDrone.Console.ConsoleApp.Main (System.String[] args) [0x00030] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Console\ConsoleApp.cs:42

2021-09-29 02:05:11.8|Info|Bootstrap|Starting Sonarr - /usr/lib/sonarr/bin/Sonarr.exe - Version 3.0.6.1265
2021-09-29 02:05:12.9|Info|AppFolderInfo|Data directory is being overridden to [/var/lib/sonarr]
2021-09-29 02:05:13.8|Info|Router|Application mode: Interactive
2021-09-29 02:05:14.3|Info|MigrationLogger|*** Checking database for required migrations data source=/var/lib/sonarr/sonarr.db;cache size=-10000;datetimekind=Utc;journal mode=Wal;pooling=True;version=3 ***
2021-09-29 02:05:14.8|Info|MigrationLogger|*** 139: add_download_history migrating ***
2021-09-29 02:05:14.8|Info|add_download_history|Starting migration to 139

there seems to be progress probably hindered by some date format issue?

String was not recognized as a valid DateTime

Looks like something in History has an invalid date.

It could be null, which would come back with: select * from history where Date is null
or it could be some other invalid value, empty string or otherwise invalid. Querying the DB and finding it is going to be the best way to recover it here.

@markus101

we have fast moved past any area where i could have even imagined to try claim i know what is happening. so don’t even know if the below is what you are suggesting

 $ sqlite3 sonarrv3.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> select * from history where Date is null
   ...>

The dump an create fresh instructions likely will help figure out which line it is and get you going with a good DB

right. so after i repaired the nzbdrone v2 database, i actually then copied that file over the existing db in v2 (this was last night just after i posted).

today i made a backup of that v2 database. installed v3 and then restored that zip backup.

it worked.

1 Like

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.