Upgrade from 2.0.3.3 to 2.0.5.1 PathPrefix table not upgraded

rain · March 26, 2020, 10:02pm

I finally upgraded to the latest beta and after the upgrade, the backup failed with a fatal error:
Failed: Failed to execute SQL: /*
The PathPrefix contains a set
of path prefixes, used to minimize
the space required to store paths
/
CREATE TABLE “PathPrefix” (
“ID” INTEGER PRIMARY KEY,
“Prefix” TEXT NOT NULL
)
Error: SQLite error
table “PathPrefix” already exists
Database is NOT upgraded.
Details: System.Exception: Failed to execute SQL: /
The PathPrefix contains a set
of path prefixes, used to minimize
the space required to store paths
*/
CREATE TABLE “PathPrefix” (
“ID” INTEGER PRIMARY KEY,
“Prefix” TEXT NOT NULL
)
Error: SQLite error
table “PathPrefix” already exists
Database is NOT upgraded. —> Mono.Data.Sqlite.SqliteException: SQLite error
table “PathPrefix” already exists
at Mono.Data.Sqlite.SQLite3.Prepare (Mono.Data.Sqlite.SqliteConnection cnn, System.String strSql, Mono.Data.Sqlite.SqliteStatement previous, System.UInt32 timeoutMS, System.String& strRemain) [0x001f8] in :0
at Mono.Data.Sqlite.SqliteCommand.BuildNextCommand () [0x000d3] in :0
at Mono.Data.Sqlite.SqliteCommand.GetStatement (System.Int32 index) [0x00008] in :0
at (wrapper remoting-invoke-with-check) Mono.Data.Sqlite.SqliteCommand.GetStatement(int)
at Mono.Data.Sqlite.SqliteDataReader.NextResult () [0x000b3] in :0
at Mono.Data.Sqlite.SqliteDataReader…ctor (Mono.Data.Sqlite.SqliteCommand cmd, System.Data.CommandBehavior behave) [0x0004e] in :0
at (wrapper remoting-invoke-with-check) Mono.Data.Sqlite.SqliteDataReader…ctor(Mono.Data.Sqlite.SqliteCommand,System.Data.CommandBehavior)
at Mono.Data.Sqlite.SqliteCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00006] in :0
at Mono.Data.Sqlite.SqliteCommand.ExecuteNonQuery () [0x00000] in :0
at Duplicati.Library.SQLiteHelper.DatabaseUpgrader.UpgradeDatabase (System.Data.IDbConnection connection, System.String sourcefile, System.String schema, System.Collections.Generic.IList1[T] versions) [0x0026c] in <236a9d00f4eb4c8dbad1308df2d204f1>:0 --- End of inner exception stack trace --- at Duplicati.Library.SQLiteHelper.DatabaseUpgrader.UpgradeDatabase (System.Data.IDbConnection connection, System.String sourcefile, System.String schema, System.Collections.Generic.IList1[T] versions) [0x00306] in <236a9d00f4eb4c8dbad1308df2d204f1>:0
at Duplicati.Library.SQLiteHelper.DatabaseUpgrader.UpgradeDatabase (System.Data.IDbConnection connection, System.String sourcefile, System.Type eltype) [0x00105] in <236a9d00f4eb4c8dbad1308df2d204f1>:0
at Duplicati.Library.Main.Database.LocalDatabase.CreateConnection (System.String path) [0x00027] in <8f1de655bd1240739a78684d845cecc8>:0
at Duplicati.Library.Main.Database.LocalDatabase…ctor (System.String path, System.String operation, System.Boolean shouldclose) [0x00000] in <8f1de655bd1240739a78684d845cecc8>:0
at Duplicati.Library.Main.Database.LocalBackupDatabase…ctor (System.String path, Duplicati.Library.Main.Options options) [0x00000] in <8f1de655bd1240739a78684d845cecc8>:0
at Duplicati.Library.Main.Operation.BackupHandler.RunAsync (System.String sources, Duplicati.Library.Utility.IFilter filter, System.Threading.CancellationToken token) [0x00042] in <8f1de655bd1240739a78684d845cecc8>:0
at CoCoL.ChannelExtensions.WaitForTaskOrThrow (System.Threading.Tasks.Task task) [0x00050] in <9a758ff4db6c48d6b3d4d0e5c2adf6d1>:0
at Duplicati.Library.Main.Operation.BackupHandler.Run (System.String sources, Duplicati.Library.Utility.IFilter filter, System.Threading.CancellationToken token) [0x00009] in <8f1de655bd1240739a78684d845cecc8>:0
at Duplicati.Library.Main.Controller+<>c__DisplayClass14_0.b__0 (Duplicati.Library.Main.BackupResults result) [0x0004b] in <8f1de655bd1240739a78684d845cecc8>:0
at Duplicati.Library.Main.Controller.RunAction[T] (T result, System.String& paths, Duplicati.Library.Utility.IFilter& filter, System.Action`1[T] method) [0x0011c] in <8f1de655bd1240739a78684d845cecc8>:0

I went ahead with a database repair and that finished successfully, but included this warning:
2020-03-25 14:25:41 +01 - [Warning-Duplicati.Library.Main.Operation.RepairHandler-FailedToReadLocalDatabase]: Failed to read local db /volume1/systemstuff/duplicati/UDQKLYOJJI.sqlite, error: Failed to execute SQL: /*
The PathPrefix contains a set
of path prefixes, used to minimize
the space required to store paths
*/
CREATE TABLE “PathPrefix” (
“ID” INTEGER PRIMARY KEY,
“Prefix” TEXT NOT NULL
) Error: SQLite error
table “PathPrefix” already exists Database is NOT upgraded.

A subsequent backup proceeded without any error or warning.

Does this mean that the PathPrefix upgrade was performed now and all is well?
Or is there still something left to do?

Thanks,
Rain

drwtsn32 · March 26, 2020, 11:49pm

I did a very quick test and was not able to reproduce the issue…backed up some data with Duplicati 2.0.3.3, upgraded to 2.0.5.1, and backed up again.

OverloadUT · May 4, 2020, 10:55pm

I am experiencing this same error, and cannot run my backup.

I suspect this is due to restarting the docker container while it was in the middle of an upgrade process. Are there any suggestions for how to fix this? Can I safely manually delete the PathPrefix table from the DB file?

ts678 · May 6, 2020, 1:18am

Not in the current DB design, but in the old design it wasn’t even there. Not sure what you have now.

If you’re heading to 2.0.5.1, then the simple thing to do (assuming it works…) is to Recreate the DB from the destination files. The Database screen has the button, and also the Local database path. Being extra careful would have you go there to manually make a copy of the DB before the Recreate. What should ideally happen is the Recreate will download relatively few files, however if progress bar reaches 90% then something’s missing and an exhaustive potentially long search will be made for it.

Another option (which would have the advantage/disadvantage of keeping old logs and other DB info) would be to see if you can see the backup copy of the 2.0.3.3 DB that Duplicati made before upgrade. Make just-in-case backup copies of that and the current (broken) DB, then copy backup DB onto the current, and let Duplicati try its DB upgrade again. If interrupting it hurt it, maybe it will finish this time.

OverloadUT · May 6, 2020, 10:53pm

Thank you for the suggestion of rolling back to the pre-update backup! Didn’t occur to me to go look for one. I went ahead and did that, and now I’m sitting on “Starting backup…” with nothing in the verbose log. My assumption is this indicates that the upgrade process from 2.0.3.3 to 2.0.5.1 is currently happening. This is exactly the state it was in when I restarted the Docker container and I assume caused the corruption.

My DB is about 10GB, so I assume this might take a while.

ts678 · May 6, 2020, 11:45pm

VACUUM - performance improvement is huge describes how to possibly reduce the DB size.
Unfortunately this wasn’t in 2.0.3.3, and 2.0.5.1 has to upgrade the DB before it can vacuum.

drakar2007 · August 19, 2020, 5:15am

I just upgraded to the latest canary and a very large local backup getting the “database not upgraded” error. After hours spent on trying to allow it to repair, I get this error message:

Anyone have any insight?

ts678 · August 19, 2020, 1:37pm

Any idea what you were on before? The INDEX change was done in 2.0.5.106_canary_2020-05-11

Changes in this version:
NOTE: this version updates the database version from v10 to v11!

Downgrade from this version requires manually adjusting the version number in the database,
The additions can be re-applied if the database is upgraded again later.

github.com

duplicati/duplicati/blob/master/Duplicati/Library/Main/Database/Database schema/11. Add Block indices.sql

CREATE INDEX IF NOT EXISTS "BlockSize" ON "Block" ("Size");
CREATE UNIQUE INDEX IF NOT EXISTS "BlockHashVolumeID" ON "Block" ("Hash", "VolumeID");

UPDATE "Version" SET "Version" = 11;

Probably exact message was “Database is NOT upgraded.” which looks like it runs on an exception:

github.com

duplicati/duplicati/blob/145ef49b91fc43a77dabfb66cd8ae3c8b7ca93c2/Duplicati/Library/SQLiteHelper/DatabaseUpgrader.cs#L311-L317


      
          catch (Exception ex)

          {

              connection.Close();

              //Restore the database

              System.IO.File.Copy(backupfile, sourcefile, true);

              throw new Exception(Strings.DatabaseUpgrader.UpgradeFailure(cmd.CommandText, ex.Message), ex);

          }

It would be nice if the original message was known, although I’m not sure whether it’s actually recorded.

Any approximate recall of what was done, and how gently? At the moment, the database seems broken down at the lowest level of SQLite. You can probably confirm that if DB Browser for SQLite can’t open it.

Before going further, you should probably preserve recent files in the DB area with UUTHLYMNAE name. Before Duplicati tries DB update, it makes a backup copy of the DB which is useful sometimes to revert.

drakar2007 · August 20, 2020, 3:40am

Oof - i’m not really sure, it may have been an older one than that. But it appears as if disaster is averted for now - I gave it another run today and it managed to work itself out and finish correctly, and I’ve done an additional Verify run on top of that and it seems cool now. Will report back if the error crops back up.