Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 14: 'unable to open database file'

Sayan751 commented 4 months ago

Ask a question

I am seeing the following error sporadically.

An error occurred using the connection to database 'main' on server 'path\to\db'.

Exception: 
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 14: 'unable to open database file'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteConnectionInternal..ctor(SqliteConnectionStringBuilder connectionOptions, SqliteConnectionPool pool)
   at Microsoft.Data.Sqlite.SqliteConnectionFactory.GetConnection(SqliteConnection outerConnection)
   at Microsoft.Data.Sqlite.SqliteConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)

I am using pooled database context and factory. My assumption here is that concurrent write-operations is causing the issue. Or as far as I understand, as the DB is locked during a write-operation, a read-operation at the same, can also cause the issue.

As I cannot deterministically reproduce the issue, I would like to ask if anyone can point me in the direction, to better debug and solve this issue.

Include your code

Program.cs

services
    .AddDbContextPool<DatabaseContext>((serviceProvider, options) =>
    {
        Directory.CreateDirectory(ApplicationDataPath);
        options.UseSqlite($"Data Source={Path.Combine(ApplicationDataPath, "data.db")}");
        options.AddInterceptors(serviceProvider.GetRequiredService<DatabaseConnectionInterceptor>());
    })
    .AddPooledDbContextFactory<DatabaseContext>(options => { });
services.AddHostedService<DatabaseMigrationService>();

DatabaseContext.cs

using Microsoft.EntityFrameworkCore;
using SQLitePCL;

public class DatabaseContext : DbContext
{
    public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options) { }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {       
        Batteries.Init();
    }
}

DatabaseMigrationService.cs

public class DatabaseMigrationService : IHostedLifecycleService
{
    private readonly IDbContextFactory<DatabaseContext> _DbContextFactory;

    public DatabaseMigrationService(IDbContextFactory<DatabaseContext> dbContextFactory)
    {
        _DbContextFactory = dbContextFactory;
    }

    public async Task StartingAsync(CancellationToken cancellationToken)
    {
        await using var ctx = await _DbContextFactory.CreateDbContextAsync(cancellationToken);
        await ctx.Database.MigrateAsync(cancellationToken);
    }

    public Task StartAsync(CancellationToken cancellationToken) => Task.CompletedTask;
    public Task StartedAsync(CancellationToken cancellationToken) => Task.CompletedTask;
    public Task StopAsync(CancellationToken cancellationToken) => Task.CompletedTask;
    public Task StoppedAsync(CancellationToken cancellationToken) => Task.CompletedTask;
    public Task StoppingAsync(CancellationToken cancellationToken) => Task.CompletedTask;
}

As there is another background service that runs tasks periodically (also a IHostedLifecycleService, and uses the StartedAsync hook), any timing conflict during the startup of the service should be avoided.

DatabaseConnectionInterceptor.cs

public class DatabaseConnectionInterceptor : DbConnectionInterceptor
{
    private readonly ILogger<DatabaseConnectionInterceptor> _Logger;

    public DatabaseConnectionInterceptor(ILogger<DatabaseConnectionInterceptor>  logger)
    {
        _Logger = logger;
    }

    public override void ConnectionFailed(DbConnection connection, ConnectionErrorEventData eventData)
    {
        base.ConnectionFailed(connection, eventData);
        Log(eventData);
    }

    public override async Task ConnectionFailedAsync(DbConnection connection, ConnectionErrorEventData eventData, CancellationToken cancellationToken = default)
    {
        await base.ConnectionFailedAsync(connection, eventData, cancellationToken);
        Log(eventData);
    }

    private void Log(ConnectionErrorEventData eventData) => _Logger.LogWarning(WarningEvents.DatabaseConnectionFailed, eventData.Exception, "Connection failed: {Error}", eventData.ToString());
}

Include stack traces

See above.

Include verbose output

NA

Include provider and version information

EF Core version: 8.0.7 Database provider: Microsoft.EntityFrameworkCore.Sqlite 8.0.7 Target framework: net 8 Operating system: Windows Server IDE: Visual Studio 2022 17.4

Additional details and questions

I have a queuing service, that writes to a database table. The same service is also periodically triggered to process the queued stuff that involves both read and write.
On the other hand, there are endpoints with high traffic that involves only (happy-path) reading from the database. One of these endpoints uses the queuing service to write to the database.
My assumption is that when the queuing service locks the DB for the write operation, any other attempt to establish a connection fails. Is this assumption unfounded?
I am inclining towards employing a semaphoreslim in the queuing service for the db operations. However, I don't want to write noisy code in every other endpoints to check the semaphore status and perform operations conditionally or wait. Is there a better way?

Sayan751 commented 3 months ago

It seems that the issue occurs if there are many pending migrations. Here is a reproduction of this issue: https://github.com/Sayan751/efcore-sqlite-issue-34273

Sayan751 commented 3 months ago

The issue also exists for .net 9.0. The reproduction can be found here: https://github.com/Sayan751/efcore-sqlite-issue-34273/tree/net9

WeihanLi commented 1 week ago

Same issue, works on windows but failed on linux container

dotnet / efcore