Currently, I am trying to completely delete an Sqlite database and recreating it if a database corruption is detected.

However, when I:

1. Create the database using Mode=ReadWriteCreate
2. Delete it
3. Recreate it again, same as step 1

The database file isn't re-created. More specifically, the following code:

using System;
using System.IO;
using Microsoft.Data.Sqlite;

namespace PotentialSqliteBug
{
    class Program
    {
        private static string DbPath = "/Users/jbickle/Downloads/SomeDatabase.sqlite";
        private static string ConnectionString = $"Data Source={DbPath};Mode=ReadWriteCreate";
            
        static void Main(string[] args)
        {
            using var connection = new SqliteConnection(ConnectionString);
            connection.Open();

            if (File.Exists(DbPath))
                Console.WriteLine("The database exists after connection was opened.");
            else
            {
                Console.WriteLine("The database DOES NOT exist after connection was opened.");
                return;
            }

            connection.Close();
            
            File.Delete(DbPath);
            if (File.Exists(DbPath))
                Console.WriteLine("The database unexpectedly exists after attempting to delete it.");
            else
                Console.WriteLine("The database no longer exists after attempting to delete it, as expected.");
            
            using var secondConnection = new SqliteConnection(ConnectionString);
            secondConnection.Open();

            if (File.Exists(DbPath))
                Console.WriteLine("The database exists after connection was opened.");
            else
                Console.WriteLine("The database DOES NOT exist after connection was opened.");
        }
    }
}

Produces the following output:

The database exists after connection was opened.
The database no longer exists after attempting to delete it, as expected.
The database DOES NOT exist after connection was opened.

This is a problem, because attempting to execute queries or creating transactions afterwards causes an Sqlite error 26: "file is not a database".

So my question is: Am I misunderstanding how database creation works in Microsoft.Data.Sqlite? How can I re-create a corrupted Sqlite database without side affects such as this?

One extra bit of confusion: if I delete the line connection.Close(), the database does get recreated as expected. Obviously, this is not a great workaround since it's not ideal to hang onto database connections forever.

If it's important, this code was executed on macOS 12.3.1 in .NET 6.0.402 SDK

CodePudding user response：

It may have to do with your first connection staying in scope, even though you closed it. You could try explicitly disposing of it with connection.Dispose(), or wrap it in the using statement:

using (var connection = new SqliteConnection(ConnectionString))
{
    connection.Open();

    if (File.Exists(DbPath))
        Console.WriteLine("The database exists after connection was opened.");
    else
    {
        Console.WriteLine("The database DOES NOT exist after connection was opened.");
        return;
    }
}

CodePudding user response：

Turns out this is a duplicate of SQLite keeps the database locked even after the connection is closed. You need to call SQLiteConnection.ClearAllPools() if you want to interact with the database file after you've closed a connection to it. Disposing the connection is not enough.

Executing this code on Windows throws an exception claiming the file is locked when attempting to delete it, but that is NOT the case in macOS (and possibly Linux).