SQLite error bind on a busy prepared statement

[SchroterQuentin]

Library Version

Version 3.0.0

.NET Runtime

dotnet 7.0

Operating System / Device Details

mcr.microsoft.com/dotnet/aspnet:7.0

Log Output

2023-7-3 02:00:16.029+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): bind on a busy prepared statement: [SELECT purgeCnt FROM kvmeta WHERE name=?] 2023-7-3 02:00:16.029+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): misuse at line 84119 of [0c1fcf4711] 2023-7-3 02:00:16.031+00:00 [.NET ThreadPool Worker]| ERROR) [Database] another row available (21/0)

2023-7-3 02:01:16.189+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): bind on a busy prepared statement: [SELECT purgeCnt FROM kvmeta WHERE name=?] 2023-7-3 02:01:16.189+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): misuse at line 84119 of [0c1fcf4711] 2023-7-3 02:01:16.189+00:00 [.NET ThreadPool Worker]| ERROR) [Database] not an error (21/0)

2023-7-3 01:59:38.032+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): misuse at line 84119 of [0c1fcf4711] 2023-7-3 01:59:38.032+00:00 [.NET ThreadPool Worker]| ERROR) [Database] bad parameter or other API misuse (21/21) [13:59:38 ERR] [Grpc.AspNetCore.Server.ServerCallHandler] Error when executing service method 'Get'. Couchbase.Lite.CouchbaseSQLiteException: CouchbaseLiteException (SQLiteDomain / 21): bad parameter or other API misuse. at LiteCore.Interop.NativeHandler.ThrowOrHandle() at LiteCore.Interop.NativeHandler.Execute(C4TryLogicDelegate2 block) at Couchbase.Lite.Support.ThreadSafety.DoLockedBridge(C4TryLogicDelegate2 a) at Couchbase.Lite.Internal.Query.NQuery.Execute()

Expected behavior

No error.

Steps To Reproduce

Unfortunately it seems to appear since we use dotnet 7 and version 3.0 of couchbase lite. It randomly appears and may cause the application to crash completely after some time.

[SchroterQuentin]

Hi ! We still have the same issue on production server ... Any news on this ?

2023-8-21 06:21:21.609+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): bind on a busy prepared statement: [SELECT lastSeq FROM kvmeta WHERE name=?] 2023-8-21 06:21:21.609+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): misuse at line 88783 of [df5c253c0b] 2023-8-21 06:21:21.609+00:00 [.NET ThreadPool Worker]| ERROR) [Database] bad parameter or other API misuse (21/21) [18:21:21 ERR] [Grpc.AspNetCore.Server.ServerCallHandler] Error when executing service method 'Get'. Couchbase.Lite.CouchbaseSQLiteException: CouchbaseLiteException (SQLiteDomain / 21): bad parameter or other API misuse. at LiteCore.Interop.NativeHandler.ThrowOrHandle() at LiteCore.Interop.NativeHandler.Execute(C4TryLogicDelegate2 block) at Couchbase.Lite.Support.ThreadSafety.DoLockedBridge(C4TryLogicDelegate2 a) at Couchbase.Lite.Internal.Query.NQuery.Execute()

[borrrden]

Hmmmm, without a reproduction I doubt this will be tracked down unfortunately. Do you have any information on when this happens? It's basically saying that at some point there was a misuse of SQLite by us when making a query, but the query and particular set of data that caused this are unknown from this ticket.

[SchroterQuentin]

The query is build like that :

var fullQuery =
    $@"
SELECT
    {string.Join(",\n", selects)}
FROM
    {from}
WHERE
    {string.Join(" AND \n", wheres)}
;";

var query = _db.CreateQuery(fullQuery);
var result = query.Execute().AllResults();    

I'm adding more logs to give you all the queries but the issue seems related to concurrency problem not database schema or stuff like that. It appears when there is more than 10 requests done at the same time on the gRPC server. I'll take some time to create a repro if it's needed to be taken seriously.

[borrrden]

If this is being done concurrently, are you creating a DB handle per thread? That would be my recommended way of doing it. In general mutable objects like that are not guaranteed to be thread safe like the immutable ones are.

[SchroterQuentin]

2023-9-3 03:42:56.667+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): bind on a busy prepared statement: [SELECT purgeCnt FROM kvmeta WHERE name=?] 2023-9-3 03:42:56.667+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): misuse at line 88783 of [df5c253c0b] 2023-9-3 03:42:56.667+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): bind on a busy prepared statement: [SELECT purgeCnt FROM kvmeta WHERE name=?] 2023-9-3 03:42:56.667+00:00 [.NET ThreadPool Worker]| ERROR) [Database] SQLite error (code 21): misuse at line 88783 of [df5c253c0b] 2023-9-3 03:42:56.668+00:00 [.NET ThreadPool Worker]| ERROR) [Database] another row available (21/100) 2023-9-3 03:42:56.668+00:00 [.NET ThreadPool Worker]| ERROR) [Database] no more rows available (21/0) [15:42:56 ERR] [Monitoring.Context.Core.Repositories.DeviceWorkflowRepository] An error occured while retrieving data from the database using query SELECT workflows.Id, workflows.Label, workflows.DeviceId, devices.Label AS DeviceLabel FROM

            _ AS workflows
            LEFT OUTER JOIN _ AS devices
            ON (
                devices.Id = workflows.DeviceId AND
                devices.Type = "device"
            )

        WHERE
            workflows.Type = "workflow" AND 

( workflows.Disabled = FALSE OR workflows.Disabled IS NOT VALUED ) AND workflows.DeviceId IN ('09ed24b2-c219-4be5-b11b-098842f8e3a0') ; Couchbase.Lite.CouchbaseSQLiteException: CouchbaseLiteException (SQLiteDomain / 100): another row available. at LiteCore.Interop.NativeHandler.ThrowOrHandle() at LiteCore.Interop.NativeHandler.Execute(C4TryLogicDelegate2 block) at LiteCore.LiteCoreBridge.Check(C4TryLogicDelegate2 block) at Couchbase.Lite.Support.ThreadSafety.DoLockedBridge(C4TryLogicDelegate2 a) at Couchbase.Lite.Internal.Query.NQuery.Execute() at Monitoring.Context.Core.BaseCouchbaseRepository1.GetManyEntities[TInfos](IEnumerable1 selects, String from, IEnumerable1 wheres) in /app/src/context/core/Monitoring.Context.Core/Tools/BaseCouchbaseRepository.cs:line 65 [15:42:56 ERR] [Grpc.AspNetCore.Server.ServerCallHandler] Error when executing service method 'GetMany'. Couchbase.Lite.CouchbaseSQLiteException: CouchbaseLiteException (SQLiteDomain / 100): another row available. at LiteCore.Interop.NativeHandler.ThrowOrHandle() at LiteCore.Interop.NativeHandler.Execute(C4TryLogicDelegate2 block) at LiteCore.LiteCoreBridge.Check(C4TryLogicDelegate2 block) at Couchbase.Lite.Support.ThreadSafety.DoLockedBridge(C4TryLogicDelegate2 a) at Couchbase.Lite.Internal.Query.NQuery.Execute() at Monitoring.Context.Core.BaseCouchbaseRepository1.GetManyEntities[TInfos](IEnumerable1 selects, String from, IEnumerable1 wheres) in /app/src/context/core/Monitoring.Context.Core/Tools/BaseCouchbaseRepository.cs:line 65 at Monitoring.Context.Core.Repositories.DeviceWorkflowRepository.GetMany(DeviceWorkflowsFilter filter) in /app/src/context/core/Monitoring.Context.Core/Repositories/App/Workflows/DeviceWorkflows/DeviceWorkflowRepository.cs:line 157 at Monitoring.Context.Server.DeviceWorkflowService.GetMany(DeviceWorkflowFilterRequest request, ServerCallContext context) in /app/src/context/core/Monitoring.Context.Server/Services/App/Workflows/DeviceWorkflows/DeviceWorkflowService.cs:line 53 at Grpc.Shared.Server.UnaryServerMethodInvoker3.AwaitInvoker(Task1 invokerTask, GrpcActivatorHandle1 serviceHandle) at Grpc.Shared.Server.UnaryServerMethodInvoker3.AwaitInvoker(Task1 invokerTask, GrpcActivatorHandle1 serviceHandle) at Grpc.AspNetCore.Server.Internal.CallHandlers.UnaryServerCallHandler3.HandleCallAsyncCore(HttpContext httpContext, HttpContextServerCallContext serverCallContext) at Grpc.AspNetCore.Server.Internal.CallHandlers.ServerCallHandlerBase3.g__AwaitHandleCall|8_0(HttpContextServerCallContext serverCallContext, Method`2 method, Task handleCall)

I'm resolving Database instance using Microsoft Dependency Injection with a singleton lifetime. Do you think that it will it be better to have scopped instance ? How does the sqlite will handle multi concurrency access ?

[borrrden]

Somehow I missed this latest update it looks like. SQLite handles concurrency with locks but that is just to prevent actual corruption and not to address busy and/or race conditions. As for our library we recommend not sharing these instances between threads. One handle per thread is the recommended model when it comes to stuff like this.

[SchroterQuentin]

What is "one handle" ? One Database instance per thread ? So scopped lifetime instance for ASP.Net Core projet ? What will happen if I have 2 databases that are updating the same document on multiple thread ? I have a Replicator using this singleton instance too. How does it works if I have scopped Database instance ?

[borrrden]

Again sorry for not responding, I'd been out on leave for a while but if you use one database instance per thread, the SQLite locking will prevent them from doing things at the same time and probably prevent SQLite errors like this. The replicator has its own instance (when you create it the first thing it does is open a new instance for itself to the same data file).

Having a replicator be a singleton is probably slightly less problematic, but still it can probably get hairy if you are calling start and stop from different threads as you will race and such. But since the replicator has its own db instance that it uses, the situation with scoped databases and the replicator is the same story as right now: They are separate instances doing their own thing and they are locked out from doing the same things at the same time (simplification).

[SchroterQuentin]

Well, I finally managed to prevent concurrency issues by disabling concurrent request on my backend

services.AddRateLimiter(limiterOptions => limiterOptions
    .AddConcurrencyLimiter(policyName: DEFAULT, options =>
    {
        options.PermitLimit = 1;
        options.QueueProcessingOrder = QueueProcessingOrder.OldestFirst;
        options.QueueLimit = 1000;
    }));

For my use case it's enough but I'll definitively try to create scoped instances (per HTTP request = thread) of Database object to see if it's working in case of high concurrency scenario.

[borrrden]

You might gain some performance by doing scoped objects, but if that's not an issue for you this works just as well.

[snej]

Regarding @borrrden 's comment above:

SQLite handles concurrency with locks but that is just to prevent actual corruption and not to address busy and/or race conditions.

That isn't relevant at this level. Couchbase Lite has its own locking to make concurrent access safe. The sorts of errors in this bug report look like they'd be caused by bugs in our code.

As for our library we recommend not sharing these instances between threads.

This is for performance reasons, not correctness. Using one Database instance on multiple threads won't be any faster than using it on a single thread, because there are mutexes -- our own and SQLite's -- preventing any concurrency. Whereas if you use a different instance on each thread they can all read the database simultaneously. (But two writes cannot be concurrent; that's an architectural limitation of SQLite.)