SQLite Forum

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);
  }
}
```