SQLite Forum

Timeline
Login

7 forum posts by user free.ekanayaka

2020-09-14
09:10 Reply: Changes to support concurrent writes to wal database distributed over network FS (artifact: f020fb94a4 user: free.ekanayaka)

Dqlite author here. How are you going to resolve conflicts when merging WAL files? Or, alternatively, how are you going to prevent conflicts from happening in the first place?

2020-07-29
08:16 Reply: Internal schema cache not refreshed after DROP TABLE in different connection (artifact: 9fe5d047f0 user: free.ekanayaka)

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

2020-07-28
20:23 Reply: Internal schema cache not refreshed after DROP TABLE in different connection (artifact: 8a8f59762e user: free.ekanayaka)

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

14:52 Reply: Internal schema cache not refreshed after DROP TABLE in different connection (artifact: d7157bd505 user: free.ekanayaka)

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);
  }
}
14:18 Reply: Internal schema cache not refreshed after DROP TABLE in different connection (artifact: 880368154f user: free.ekanayaka)

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.

14:05 Reply: Internal schema cache not refreshed after DROP TABLE in different connection (artifact: 7a12a0c032 user: free.ekanayaka)

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.

09:27 Post: Internal schema cache not refreshed after DROP TABLE in different connection (artifact: 83e63f70a7 user: free.ekanayaka)

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