SQLite Forum

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