SQLite Forum

Internal schema cache not refreshed after DROP TABLE in different connection
Login

Internal schema cache not refreshed after DROP TABLE in different connection

(1) By Free Ekanayaka (free.ekanayaka) on 2020-07-28 09:27:40 [link]

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

(2) By anonymous on 2020-07-28 10:01:58 in reply to 1 [link]

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

(5) By Free Ekanayaka (free.ekanayaka) on 2020-07-28 14:18:31 in reply to 2 [link]

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.

(3) By Gunter Hick (gunter_hick) on 2020-07-28 11:11:04 in reply to 1 [link]

You must check the return status of EACH and EVERY call to SQLite to be sure that what you purport to be doing is actually happening.

Depending on sleep() to "synchronize" your threads is not safe (it assumes that the called function complete in negligible time, which may not be the case when one writer is blocked by the other writer) and the order of output on stdout may be affected by buffering.

Also, you neglected to call sqlite3_step() on the SELECT FROM sqlite_master.

(4) By Free Ekanayaka (free.ekanayaka) on 2020-07-28 14:05:37 in reply to 3 [link]

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.

(6) By Richard Hipp (drh) on 2020-07-28 14:27:49 in reply to 1 [link]

> 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.

(7) By Free Ekanayaka (free.ekanayaka) on 2020-07-28 14:52:41 in reply to 6

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

(8) By Richard Hipp (drh) on 2020-07-28 15:16:09 in reply to 7 [link]

OK, so it looks like you have to actually do something that needs to use
the schema before the schema is checked.  Something like this will suffice:

~~~~~~
    SELECT 1 FROM sqlite_master LIMIT 1;
~~~~~~

Do this inside of the transaction before preparing each CREATE or DROP statement.

I'll see what we can do about fixing this for the next release.

(9) By Free Ekanayaka (free.ekanayaka) on 2020-07-28 20:23:34 in reply to 8 [link]

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

(10) By Gunter Hick (gunter_hick) on 2020-07-29 06:10:29 in reply to 9 [link]

Just like I said in the first place.

(11) By Free Ekanayaka (free.ekanayaka) on 2020-07-29 08:16:06 in reply to 10 [link]

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