Hello,

it seems that if I drop a table from connection C1, then another connection C2 that had already that table recorded in its internal schema cache does not notice the change.

Here is a sample C program to illustrate the issue (error handling omitted for clarity):

#include <stdio.h>
#include <sqlite3.h>
#include <unistd.h>
#include <pthread.h>

static void* thread1(void* arg) {
  sqlite3 *db;
  sqlite3_stmt *stmt;

  sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
  sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, NULL, NULL);

  printf("thread 1: create table\n");
  sqlite3_prepare_v2(db, "CREATE TABLE test(n INT)", -1, &stmt, NULL);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);
  sleep(2);

  printf("thread 1: drop table\n");
  sqlite3_prepare_v2(db, "DROP TABLE test", -1, &stmt, NULL);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);
  sleep(2);

  printf("thread 1: done\n");
  return NULL;
}

static void* thread2(void* arg) {
  sqlite3 *db;
  sqlite3_stmt *stmt;
  int rv;

  sleep(1);

  sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);

  printf("thread 2: trigger schema initialization\n");
  sqlite3_prepare_v2(db, "SELECT * FROM sqlite_master", -1, &stmt, NULL);
  sqlite3_finalize(stmt);

  sleep(2);
  printf("thread 2: create table\n");
  rv = sqlite3_prepare_v2(db, "CREATE TABLE test(n INT)", -1, &stmt, NULL);
  if (rv != 0) {
    printf("thread 2: err: %s (%d)\n", sqlite3_errmsg(db), rv);
  }

  printf("thread 2: done\n");
  return NULL;
}

int main() {
  pthread_t t1;
  pthread_t t2;
  void *result;
  unlink("test.db");
  unlink("test.db-shm");
  unlink("test.db-wal");
  pthread_create(&t1, 0, &thread1, NULL);
  pthread_create(&t2, 0, &thread2, NULL);
  pthread_join(t1, &result);
  pthread_join(t2, &result);
}

Running it with "gcc main.c -lpthread -lsqlite3 -omain && ./main", produces this output:

thread 1: create table
thread 2: trigger schema initialization
thread 1: drop table
thread 2: create table
thread 2: err: table test already exists (1)
thread 2: done
thread 1: done

How is the db connection in thread2 supposed to be able to create the "test" table again? Is there a way to trigger a refresh of the internal schema cache?

I've set WAL mode in this example, but I believe it shouldn't matter much.

Thanks,

Free

Can you print [1] and [2] at various points in your program too?
Also try w/o WAL IMHO. And with WAL, perhaps force a WAL checkpoint, to see what happens.

[1] https://www.sqlite.org/pragma.html#pragma_schema_version
[2] https://www.sqlite.org/pragma.html#pragma_data_version

The return status is not checked in the code I pasted to avoid cluttering the code, which is just an example to show the issue. This is not real-world code. The sleeps here are enough to reproduce the issue in any reasonable system, they are meant to simplify the example code. Checking the return code or using safe synchronization primitives won't change the point I'm trying to make.

The fact that sqlite3_step() is not called in "SELECT * FROM sqlite_master" is by purpose, not neglected. Preparing the statement is sufficient to initialize the internal schema cache of that connection, which is all what is needed to highlight the issue here.

I modified the code to print the schema version before and after each step:

#include <stdio.h>
#include <sqlite3.h>
#include <unistd.h>
#include <pthread.h>

static int schema_version(sqlite3 *db) {
  sqlite3_stmt *stmt;
  int version;
  sqlite3_prepare_v2(db, "PRAGMA schema_version", -1, &stmt, NULL);
  sqlite3_step(stmt);
  version = sqlite3_column_int(stmt, 0);
  sqlite3_finalize(stmt);
  return version;
}

static void* thread1(void* arg) {
  sqlite3 *db;
  sqlite3_stmt *stmt;

  sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
  sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, NULL, NULL);

  printf("thread 1 - schema %d: creating table\n", schema_version(db));
  sqlite3_prepare_v2(db, "CREATE TABLE test(n INT)", -1, &stmt, NULL);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);
  printf("thread 1 - schema %d: table created\n", schema_version(db));
  sleep(2);

  printf("thread 1 - schema %d: dropping table\n", schema_version(db));
  sqlite3_prepare_v2(db, "DROP TABLE test", -1, &stmt, NULL);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);
  printf("thread 1 - schema %d: table dropped\n", schema_version(db));
  sleep(2);

  printf("thread 1: done\n");
  return NULL;
}

static void* thread2(void* arg) {
  sqlite3 *db;
  sqlite3_stmt *stmt;
  int rv;

  sleep(1);

  sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);

  printf("thread 2 - schema %d: trigger schema initialization\n", schema_version(db));
  sqlite3_prepare_v2(db, "SELECT * FROM sqlite_master", -1, &stmt, NULL);
  sqlite3_finalize(stmt);
  printf("thread 2 - schema %d: schema initialized\n", schema_version(db));

  sleep(2);
  printf("thread 2 - schema %d: creating table\n", schema_version(db));
  rv = sqlite3_prepare_v2(db, "CREATE TABLE test(n INT)", -1, &stmt, NULL);
  if (rv != 0) {
    printf("thread 2 - schema %d: err: %s (%d)\n", schema_version(db), sqlite3_errmsg(db), rv);
  }

  printf("thread 2: done\n");
  return NULL;
}

int main() {
  pthread_t t1;
  pthread_t t2;
  void *result;
  unlink("test.db");
  unlink("test.db-shm");
  unlink("test.db-wal");
  pthread_create(&t1, 0, &thread1, NULL);
  pthread_create(&t2, 0, &thread2, NULL);
  pthread_join(t1, &result);
  pthread_join(t2, &result);
}

The output is:

thread 1 - schema 0: creating table
thread 1 - schema 1: table created
thread 2 - schema 1: trigger schema initialization
thread 2 - schema 1: schema initialized
thread 1 - schema 1: dropping table
thread 1 - schema 2: table dropped
thread 2 - schema 2: creating table
thread 2 - schema 2: err: table test already exists (1)
thread 2: done
thread 1: done

Without WAL it's the same.

As far as I can understand from the SQLite code, this might be a case where the internal schema cache of the second connection does not ever get refreshed.

It shouldn't be necessary to force a checkpoint in order for the second connection to become aware of the schema change.

Is there a way to trigger a refresh of the internal schema cache?

There are many ways to force a check of the schema to see if it needs to be reread. Here is one:

    PRAGMA user_version;

If the schema has already been parsed and symbol tables for the schema are in memory, then sqlite3_prepare_v2() does not check to see if the schema has been changed. This is because the answer is almost always "no" and doing the test involves disk I/O. Instead, the byte-code generated by sqlite3_prepare_v2() checks for a schema change when it runs and if the schema has changed, the sqlite3_step() aborts, rereads the schema, reprepares the statement, and tries again.

So, if you do a CREATE TABLE and that table is already in the schema cache, you will get an error message, because the schema cache does not know that the table has been dropped, and no byte-code is run to check for this.

If you have multiple threads that are racing to change the schema, then you would do well to put each change inside of a transaction:

    BEGIN IMMEDIATE;
    CREATE TABLE test(...);
    COMMIT;

Do this for every sequence of CREATE and DROP commands. Note that I mean that the sqlite3_prepare_v2() for each CREATE and DROP should be run after the BEGIN IMMEDIATE returns successfully.

Thanks Richard, I tried as you suggested but when trying to prepare the CREATE TABLE statement on the second connection I still get an error because its schema cache thinks the table exists when in fact it does not.

Here follows a revised version of the reproducer, without any threading or WAL involved. Maybe I'm missing something?

#include <stdio.h>
#include <sqlite3.h>
#include <unistd.h>

static int create_table(sqlite3 *db) {
  sqlite3_stmt *stmt;
  int rc;

  sqlite3_prepare_v2(db, "BEGIN IMMEDIATE", -1, &stmt, NULL);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  rc = sqlite3_prepare_v2(db, "CREATE TABLE test(n INT)", -1, &stmt, NULL);
  if (rc != 0) {
    return rc;
  }

  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  sqlite3_prepare_v2(db, "COMMIT", -1, &stmt, NULL);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  return 0;
}

static void drop_table(sqlite3 *db) {
  sqlite3_stmt *stmt;

  sqlite3_prepare_v2(db, "BEGIN IMMEDIATE", -1, &stmt, NULL);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  sqlite3_prepare_v2(db, "DROP TABLE test", -1, &stmt, NULL);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  sqlite3_prepare_v2(db, "COMMIT", -1, &stmt, NULL);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);
}

static void trigger_schema_cache_init(sqlite3 *db) {
  sqlite3_stmt *stmt;

  sqlite3_prepare_v2(db, "BEGIN IMMEDIATE", -1, &stmt, NULL);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  sqlite3_prepare_v2(db, "SELECT * FROM sqlite_master", -1, &stmt, NULL);
  sqlite3_finalize(stmt);

  sqlite3_prepare_v2(db, "COMMIT", -1, &stmt, NULL);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);
}

int main() {
  sqlite3 *db1;
  sqlite3 *db2;
  int rc;

  unlink("test.db");
  unlink("test.db-shm");
  unlink("test.db-wal");

  sqlite3_open_v2("test.db", &db1, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
  sqlite3_open_v2("test.db", &db2, SQLITE_OPEN_READWRITE, NULL);

  create_table(db1);
  trigger_schema_cache_init(db2);

  drop_table(db1);

  rc = create_table(db2);
  if (rc != 0) {
    printf("err: %s (%d)\n", sqlite3_errmsg(db2), rc);
  }
}

Thanks Richard, running that query as indicated does the trick indeed.

Well, the point was to assess whether this behavior is expected, and it's not, as well as what the issue is (cache invalidation).