SQLite Forum

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.