Weird initialisation bug occurring only occasionally

[polterguy]

I've got the following sqlite-vector loader code.

using (var cmd = connection.CreateCommand())
{
    var cmdTxt = @"select load_extension(""./sqlite-plugins/vector"", ""sqlite3_vector_init"")";
    cmd.CommandText = cmdTxt;
    cmd.ExecuteNonQuery();
}

This code runs automatically every single time I create an SQLite connection, and it succeeds 99% of the time, but fails occasionally. The good news is that it fails consistently, as in only when using HttpClient with streaming (SSE, as in Server Side Events), and from the callback (implying a callback from server, hence also a new or previously suspended background thread since HttpClient is async).

I've tried creating all sorts of "reproducibles" with multiple threads creating database connections at the same time, but I can't reproduce it, and the only common denominator is that it's invoked from a callback supplied to HttpClient, which makes it ridiculously complex to create a reproducible.

The code is identical to the code I previously used for "sqlite-vec", so at least in theory there shouldn't be anything wrong with my code, but given my "funny thread configuration" it seems to fail consistently ...

Notice, the only SQL that it executes in the command can be seen above. The connection is 100% new, and I don't use connection pooling. See the attached screenshot for the exception that occurs ...

[polterguy]

Psst, I don't use quantisation at all (yet) ...

[marcobambini]

@polterguy The Microsoft.Data.Sqlite package from Microsoft does not support dynamic extension loading due to sandbox/security restrictions. Use System.Data.SQLite if you need full extension support.

[polterguy]

What version should I use? I'm getting weird errors during initialisation?

As in, 100+ of these ...

dlopen(libe_sqlite3, 0x0001): tried: 'libe_sqlite3' (no such file), '/System/Volumes/Preboot/Cryptexes/OSlibe_sqlite3' (no such file), '/usr/lib/libe_sqlite3' (no such file, not in dyld cache), 'libe_sqlite3' (no such file), '/usr/local/lib/libe_sqlite3' (no such file), '/usr/lib/libe_sqlite3' (no such file, not in dyld cache)

[polterguy]

Using

<PackageReference Include="System.Data.SQLite" Version="2.0.1" />

[polterguy]

FYIm, I've tried every imaginable permutation of System.Data.Sqlite, and it seems as though I must combine it with the platform DLLs and object files from SQLite's binaries, at which point absolutely nothing I've tried allows me to invoke EnableExtensions without the process simply dying. I realise this has probably nothing to do with your code, since it is triggered before the extension is even loaded - But I thought you'd might like to know, since I'm not sure System.Data.Sqlite even is possible to get working ...

Ohh yeah, ChatGPT brought me through 50 different suggestions - None of which worked ofc ...

Funny thing is, with Microsoft.Data.Sqlite everything works, except that "funny thread combination" which give me an "interesting" error message from SQLite (see screenshot) ...

[marcobambini]

@polterguy you are right, the error is in the SQLite library.

We are investigating the issue, but after further investigation, I think that the way to go is to use Microsoft.Data.Sqlite because it provides the convenient LoadExtension method (introduced in v3.0) that lets you load native SQLite extensions seamlessly, without needing to dig into low-level interop.

Switching back to Microsoft.Data.Sqlite should solve the loading issue except for the one related threads.
The thread issue could be related to how sqlite has been compiled and how you are trying to use it (read https://www.sqlite.org/threadsafe.html).

What is the result of the command SELECT PRAGMA compile_options;?

[polterguy]

I ran PRAGMA compile_options, and here's what I'm getting;

ATOMIC_INTRINSICS=1
COMPILER=clang-15.0.0
DEFAULT_AUTOVACUUM
DEFAULT_CACHE_SIZE=-2000
DEFAULT_FILE_FORMAT=4
DEFAULT_FOREIGN_KEYS
DEFAULT_JOURNAL_SIZE_LIMIT=-1
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_PCACHE_INITSZ=20
DEFAULT_RECURSIVE_TRIGGERS
DEFAULT_SECTOR_SIZE=4096
DEFAULT_SYNCHRONOUS=2
DEFAULT_WAL_AUTOCHECKPOINT=1000
DEFAULT_WAL_SYNCHRONOUS=2
DEFAULT_WORKER_THREADS=0
DIRECT_OVERFLOW_READ
ENABLE_COLUMN_METADATA
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS4
ENABLE_FTS5
ENABLE_MATH_FUNCTIONS
ENABLE_RTREE
ENABLE_SNAPSHOT
MALLOC_SOFT_LIMIT=1024
MAX_ATTACHED=10
MAX_COLUMN=2000
MAX_COMPOUND_SELECT=500
MAX_DEFAULT_PAGE_SIZE=8192
MAX_EXPR_DEPTH=1000
MAX_FUNCTION_ARG=127
MAX_LENGTH=1000000000
MAX_LIKE_PATTERN_LENGTH=50000
MAX_MMAP_SIZE=0x7fff0000
MAX_PAGE_COUNT=0xfffffffe
MAX_PAGE_SIZE=65536
MAX_SQL_LENGTH=1000000000
MAX_TRIGGER_DEPTH=1000
MAX_VARIABLE_NUMBER=32766
MAX_VDBE_OP=250000000
MAX_WORKER_THREADS=8
MUTEX_PTHREADS
SYSTEM_MALLOC
TEMP_STORE=1
THREADSAFE=1

Notice, this is with Microsoft.Data.Sqlite ...

[marcobambini]

I think that the issue could be fixed by just serializing the loading process.
I am not a Windows expert, so I asked for help to ChatGPT and its answer seems pretty good to me.

Short version: the error isn’t coming from SQLite itself, it’s coming from the extension’s init function.
load_extension() only prints “error during initialization:” when it successfully dlopen’ed your library and then called the entry point (sqlite3_vector_init) and that function returned non-zero without setting an error message. That’s why the text after the colon is blank.

Why it only happens from the HttpClient SSE callback

• In that path you’re very likely loading the same extension concurrently on two different connections (or re-entering the init on a background thread while another load is in progress).
• Many extensions (especially ones that set up global tables, dispatch tables, SIMD checks, etc.) are not re-entrant during *_init and will misbehave if two threads run the initializer at the same time. When this happens they often return a generic error (and sometimes forget to fill the error string), which is exactly what you’re seeing.
• Your multi-threaded repros didn’t trigger it because the specific timing in the SSE callback (thread-pool resumption, continuation on a different thread, etc.) creates the right race window.

Other less likely but common culprits

• Relative path: ./sqlite-plugins/vector can resolve differently on a callback thread. (But if the path were wrong you’d get “unable to open shared library…”, not “error during initialization”.) Still worth fixing: use an absolute path.
• Version/ABI mismatch wouldn’t be intermittent, and “not authorized” would indicate EnableExtensions() missing (not your case).

What to do (pick 1–3; doing #1 is usually enough)

1. Serialize extension loading in-process
   Load it under a static lock so only one thread can run the init at a time.

private static readonly SemaphoreSlim _extGate = new(1, 1);
private static volatile bool _vectorLoaded; // per-process

public static async Task EnsureVectorLoadedAsync(SqliteConnection connection, string absPath)
{
    if (_vectorLoaded) return;

    await _extGate.WaitAsync().ConfigureAwait(false);
    try
    {
        if (_vectorLoaded) return; // double-check after entering
        connection.EnableExtensions(true);
        using var cmd = connection.CreateCommand();
        cmd.CommandText = @"select load_extension($p, $e)";
        cmd.Parameters.AddWithValue("$p", absPath);
        cmd.Parameters.AddWithValue("$e", "sqlite3_vector_init");
        cmd.ExecuteNonQuery();
        _vectorLoaded = true;
    }
    finally
    {
        _extGate.Release();
    }
}

Call this right after opening the first connection you create in the process.
For subsequent connections, skip loading (functions and modules are registered per-connection, yes, but the fragile part is the library’s process-global init; most vector extensions keep some global state). If your extension truly requires per-connection registration, keep the lock but remove _vectorLoaded and just guard the ExecuteNonQuery() so two threads don’t initialize at the same time.

2. Use an absolute, stable path
   Resolve once at startup and pass the absolute path to avoid current-directory surprises from background threads.

var absPath = Path.GetFullPath(
    Path.Combine(AppContext.BaseDirectory, "sqlite-plugins", GetLibName("vector"))
);

3. Prefer the API helper over raw SQL (gives slightly clearer errors)

connection.EnableExtensions(true);
connection.LoadExtension(absPath, "sqlite3_vector_init");

4. Improve diagnostics inside the extension (if you control it)

• In sqlite3_vector_init(sqlite3* db, char** pzErrMsg, const sqlite3_api_routines* pApi):

  • On error, set *pzErrMsg = sqlite3_mprintf("…detailed reason…") before returning SQLITE_ERROR.
  • Add a thread-safe “already initialized” guard around any global state (e.g., std::once_flag/pthread_once/InitOnceExecuteOnce or an atomic bool + mutex).
  • Avoid non-thread-safe static initialization or make it idempotent.

5. (Advanced) Auto-extension
   If feasible, register via sqlite3_auto_extension() so the extension’s registration runs exactly once in a thread-safe way for future connections. This avoids calling load_extension on every connection.

Sanity checklist

• Call connection.EnableExtensions(true) before loading (else you’d get “not authorized”).
• Ensure the library matches the process architecture and SQLite build used by Microsoft.Data.Sqlite.
• If you must load per-connection, still serialize the init path (the heavy global work) inside the extension.

Why this aligns with your symptoms

• Works 99% → race only sometimes wins.
• Only fails from the SSE callback → that code path overlaps with other work, opening a window for a concurrent initializer.
• Error string is empty after “error during initialization:” → the extension is returning an error without setting pzErrMsg.

If you want, paste the sqlite3_vector_init body (or the part where you register modules/functions). I can point out exactly where to put the one-time guard / idempotency fix.

[polterguy]

Interestingly, sometimes it actually works, and I don't get that thread issue, but that seems to be more "luck" than having to do anything with my code. I realise that is the nature of thread issues, so it's probably irrelevant - But the old lib never exhibited this behaviour, so even though the error happens in parts that are seemingly unrelated, it only seems to happen when using the extension, and it will happen on seemingly "unrelated" code, such as simply selecting 5 records from any table.

I've tried debugging my code, but then it doesn't happen at all, which make sense since the act of debugging ensures no two threads are creating database connections at the same time due to the breakpoint waiting for me to continue. I've tried adding a lock statement around my Initialise method for the connection, but this doesn't help either, and I still get the bug, so it doesn't seem to be related to race conditions in the sqlite3_vector_init function. Besides, the error happens when executing any arbitrary SQL, so it doesn't seem to be happening in the init function.

I've also tried running my code without creating a new thread, at which point it actually works. If I do, it still runs on an HttpClient streaming callback, so technically there is some sort of threading happening here due to the code being async. I've tried creating code that creates half a dozen new threads, where each thread is accessing the database, and it doesn't happen. It only happens in a server-side event callback from HttpClient, but this might be unrelated and just a coincidence ...

The error I'm getting in my logs, which originates from SQLite, is as follows;

SQLite Error 1: 'error during initialization: '.

Below is me brainstorming the issue to suggest potential causes ...

1. Could it be related to your compiler options during compilation of the extension?
2. Could it be due to versioning issues? As in you're compiling your lib against a newer/older version that whatever Microsoft.Data.Sqlite was compiled with?
3. What shared data does your lib use?
   • I realise it's using an "internal" table for keeping track of which tables/columns?

Psst, I'm not reusing connections, and my THREADSAFE compiler option is 1.

[polterguy]

I tried your semaphore suggestion, and it throws "vector_init not found", which I assume is happening on consecutive connections due to not having access to the extension on the specific connection. Notice, I am not using connection pooling. If I keep my init code as follows it works though;

        private static async Task EnsureVectorLoadedAsync(SqliteConnection connection)
        {
            await _extGate.WaitAsync().ConfigureAwait(false);
            try
            {
                connection.EnableExtensions(true);
                connection.LoadExtension("./sqlite-plugins/vector");
            }
            finally
            {
                _extGate.Release();
            }
        }

I will make sure it resolves to an absolute path before I push, but basically the above seems to work - Which is weird considering I tried a simple lock statement, and that did not work. But I guess the async parts messes with the exact timing of the invocation due to threading overhead related to your await slightly changing the timing ...

So basically, my code now works, but I suspect that's a little bit of "timing luck" due to await, but I will let you know if I experience the same bug again ...

Psst, sorry for my last comment, I posted it before I realised you had answered me ...

[polterguy]

Psst, the more I test and think about this, I suspect the init method of the extension is the culprit due to initialising shared resources, and I think you should apply some sort of semaphore or something in it to completely avoid this from happening in the future. Serialising init invocations in client code seems "unnatural" and I suspect most others with similar issues wouldn't even know where to begin searching for a solution ...

[marcobambini]

@polterguy If you look at existing SQLite extensions, none of them add locks or mutexes inside the init function.

Such protection would only be necessary if the extension accessed shared global state, which is not the case for the vector extension.

The extension entry point is invoked once per connection when the extension is loaded, and even in the official SQLite extensions (written by the author of SQLite), that function is not protected.

[polterguy]

OK, understood. If I serialise the init function, it actually works though. Maybe add something to the dox at least ...?

But anyways, with the semaphore everything seems to work. Thank you, you're a champion! Let me know if you need a testimonial of some sort :)

[polterguy]

Psst, in case somebody else have this issue in the future, then this is the code that works

        private static async Task EnsureVectorLoadedAsync(SqliteConnection connection)
        {
            await _extGate.WaitAsync().ConfigureAwait(false);
            try
            {
                connection.EnableExtensions(true);
                connection.LoadExtension("./sqlite-plugins/vector");
            }
            finally
            {
                _extGate.Release();
            }
        }