System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: e9fb60feb53aa0d4d12db741159620c931df5780
Title: Database backup fails
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Connection Resolution: Works_As_Designed
Last Modified: 2016-08-25 00:45:12
Version Found In: 1.0.101.0
User Comments:
anonymous added on 2016-05-19 14:43:03:
I use the following code to backup a database. The code worked using System.Data.SQLite version 1.0.99.0.

using (SQLiteConnection source = new SQLiteConnection(sourceConnectionString))
using (SQLiteConnection destination = new SQLiteConnection(destinationConnectionString))
{
    source.Open();
    destination.Open();
    source.BackupDatabase(destination, "main", "main", -1, null, -1);
}

Running the same code with version 1.0.101.0 produces the following exception:

System.Data.SQLite.SQLiteException (0x80004005): attempt to write a readonly database
not an error
   bei System.Data.SQLite.SQLite3.StepBackup(SQLiteBackup backup, Int32 nPage, Boolean& retry)
   bei System.Data.SQLite.SQLiteConnection.BackupDatabase(SQLiteConnection destination, String destinationName, String sourceName, Int32 pages, SQLiteBackupCallback callback, Int32 retryMilliseconds)

The message claims that this is no error, but it is. The destination database is empty. No backup is created.

anonymous added on 2016-05-19 14:45:59:
If this is relevant: I use the package sqlite-netFx46-static-binary-Win32-2015-1.0.101.0.zip

The database to be backed up is encrypted.

mistachkin added on 2016-05-19 20:58:54:
Does the source connection string include the encryption password?

Do either of the connection strings include the "Read Only" connection
string property?

mistachkin added on 2016-05-19 21:09:57:
Also, what are the journal modes for the source and destination databases?

anonymous added on 2016-05-19 21:41:32:
The connection strings for the source and destination database use the same pattern: "Data Source=<DB_File>;Version=3;DateTimeFormat=ISO8601;DateTimeKind=UTC;Password=<Password>".

No "Read only" property is set.

The journal mode of the database to backup is 'Delete'.

mistachkin added on 2016-05-20 16:06:44:
And both connections are using different file names?

mistachkin added on 2016-05-20 16:10:12:
The current test case I have (which does not reproduce the issue) is:

using System;
using System.IO;
using System.Data.SQLite;

namespace ConsoleApplication1
{
    class Program
    {
        private static string GetConnectionString(
            bool destination
            )
        {
            string fileName = Path.Combine(
                AppDomain.CurrentDomain.BaseDirectory,
                destination ? "test2.db" : "test.db");

            string password = "xyzzy";

            return String.Format(
                "Data Source={0};Version=3;" +
                "DateTimeFormat=ISO8601;DateTimeKind=UTC;" +
                "Password={1};", fileName, password);
        }

        private static void CreateSourceDb()
        {
            using (SQLiteConnection source = new SQLiteConnection(
                    GetConnectionString(false)))
            {
                source.Open();

                using (SQLiteCommand command = source.CreateCommand())
                {
                    command.CommandText = "CREATE TABLE IF NOT EXISTS t1(x);";
                    command.ExecuteNonQuery();

                    command.CommandText = "INSERT INTO t1 (x) VALUES (RANDOMBLOB(1048576));";
                    command.ExecuteNonQuery();
                }
            }
        }

        static void Main(string[] args)
        {
            CreateSourceDb();

            using (SQLiteConnection source = new SQLiteConnection(
                    GetConnectionString(false)))
            {
                source.Open();

                using (SQLiteConnection destination = new SQLiteConnection(
                        GetConnectionString(true)))
                {
                    destination.Open();

                    source.BackupDatabase(
                        destination, "main", "main", -1, null, -1);
                }
            }
        }
    }
}

anonymous added on 2016-05-22 06:17:51:
Will be out of office for a week, but I will perform further tests myself once I'm back.

anonymous added on 2016-05-28 21:40:49:
I could reproduce the exception, but I am not sure what the real reason for the problem is. Using your test code, I created a database with version 1.0.89.0 (see attached file). Afterwards I tried to perform a backup also using your test code (without the call to CreateSourceDb) and the described exception occured.
I am not quite sure with which version I created the database I want to backup, but I assume it was 1.0.96.0.
Like described in the initial post, the backup works using version 1.0.99.0.

anonymous added on 2016-05-29 12:06:49:
I feared that even after my current edits this ticket would be ignored if the status and resolution remains 'Pending' / 'Unable_To_Reproduce'.

So I changed the status and resolution to 'Open'. Please forgive me if I should not have changed these flags.

mistachkin added on 2016-05-29 15:18:35:
It's fine, you can change whatever fields you feel are appropriate.  Meanwhile,
I'll use your database and try to recreate the problem here.

mistachkin added on 2016-05-31 20:08:07:
I've tried using your database here locally to reproduce the issue.  No error was
seen.

mistachkin added on 2016-05-31 20:08:48:
What operating system and processor architecture are you using?

anonymous added on 2016-05-31 20:29:35:
Operating system is Windows 10, 64 bit. The program is compiled for x86.

anonymous added on 2016-08-02 14:24:05:
Different anonymous user here. Recently switched from 1.0.99.0 to 1.0.102.0, and am now seeing the exact same error on every backup. I'll try to come up with code that reproduces the problem, but in the mean time, here is how our connection string(s) are built:

'From' connection string is assigned using SQLiteConnectionStringBuilder with the following properties:

sb.DataSource = [our 'from' file];
sb.DateTimeFormat = SQLiteDateFormats.ISO8601;
sb.DateTimeKind = DateTimeKind.Unspecified;
sb.FailIfMissing = true;
sb.JournalMode = SQLiteJournalModeEnum.Wal;
sb.LegacyFormat = false;
sb.ReadOnly = false;
sb.SyncMode = SynchronizationModes.Normal;
sb.UseUTF16Encoding = false;
sb.ForeignKeys = true;

'To' is identical except for DataSource and FailIfMissing.

mistachkin added on 2016-08-02 17:56:47:
Attempted to reproduce with WAL journal mode.  Still unable to reproduce.

anonymous added on 2016-08-02 19:30:52:
Haven't been able to reproduce, but I did see this in the SQLite docs:

"The sqlite3_backup_step() might return SQLITE_READONLY if ...the destination database is using write-ahead-log journaling and the destination and source page sizes differ..."

Then I found that the default page size changed in 3.12.0, which was integrated with System.Data.SQLite in 1.0.100.0. Last version working for myself and previous poster was 1.0.99.0.

My backups failed on dbs created in 99.

Could this explain the error?

mistachkin added on 2016-08-03 17:42:37:
Yes, I think that explains the error.  Thanks for the additional information.