Cipher fails when reopening the database

[coltoneshaw]

I know this is probably a me thing, but i'm not able to find a solution to this.

I can encrypt the database properly with key='' and it loads and read in my application successfully. I then close the app, reopen it, input the database password, then open the database. Once i've opened this database, all the queries fail.

What am I doing wrong?

Here is me opening the database for the second time. Every query is like this.

[1] 11:33:28.885 › PRAGMA cipher='chacha20'
[1] 11:33:28.916 › PRAGMA key='ttt'
[1] 11:33:28.918 › PRAGMA journal_mode = WAL
[1] 11:33:28.919 › PRAGMA temp_store = memory
[1] 11:33:28.919 › PRAGMA mmap_size = 30000000000
[1] 11:33:28.920 › PRAGMA page_size = 32768
[1] 11:33:28.920 › PRAGMA vacuum
[1] 11:33:28.932 › select `name` from `sqlite_master` where `type` = 'table'
[1] 11:33:28.933 › [Database Knex Query] error: SqliteError: database disk image is malformed - Query String: 'select `name` from `sqlite_master` where `type` = 'table''

I'm opening it with the below class:

  async init(password: string, debug = true) {
    this.openDatabase(password, debug);

// it fails below here
    const tables = checkOrMakeTables();

    if (!tables.ok) {
      logger('error', 'Failed to create tables', tables.error);
      throw Error(tables.error);
    }
  }

openDatabase(
    password: string,
    debug?: boolean,
  ) {
   // closing the db in case one is already open
    this.close();
    this.db = new Database(
      this.path,
      {
        ...(debug ? {
          verbose(...args) {
            logger('debug', ...args);
          }
        } : {})
      },
    );

    this.db.pragma('cipher=\'chacha20\'');
    this.db.pragma(`key='${password}'`);

    this.db.pragma('journal_mode = WAL');
    this.db.pragma('temp_store = memory');
    this.db.pragma('mmap_size = 30000000000');
    this.db.pragma('page_size = 32768');
    this.db.pragma('vacuum');
  }

[coltoneshaw]

An important note, I open this same DB in SQLiteStudio, and it works fine without problems. So, it has to be an order of operations thing in my end but i'm having no luck finding it out.

[coltoneshaw]

Interesting, I disabled journal_mode = WAL and it seems to work like expected. Any ideas?

[coltoneshaw]

It seems to have something to do with connecting to the database at any point from a third party tool, like SQLiteStudio.

1. Start the application
2. Set the password for the database
3. Refresh the application
4. Type the password in to unlock it, all works.
5. Connect to the database via SQLiteStudio
6. Make a few queries on SQLiteStudio
7. Disconnect from database in SQLiteStudio
8. Start application again
9. Type password to unlock (same as step 4 above)
10. all queries from the application result in error: SqliteError: database disk image is malformed

[coltoneshaw]

I posted over in the library too, because it could be an issue there.

One other observation I've noticed is that when connecting it creates the .db-shm and .db-wal files, then closing the connection via SQLiteStudio it deletes those files, leaving me with just .db. However, when closing via db.close() via electron / better-sqlite it does not remove those files.

[coltoneshaw]

Okay, final observation, and probably rules to an issue with how i'm using better-sql or just an issue in the library here.

I've made it so the database is properly closed, meaning all .db-* files are removed when closing, just like how SQLiteStudio does it. When the application goes to reopen the database, it always fails with a disk malformed error if those .db-* files do not exist.

If I disable journal_mode = WAL it all seems to work fine. However, I have a need to keep that enabled for performance.

[coltoneshaw]

Found the issue is related to mmap_size not being supported

[m4heshd]

Glad you went ahead and reported the issue in the upstream library where the problem actually lies. People rarely do that.

SQLiteStudio has been known to have issues with WAL mode in previous releases where it depended on a problematic release of sqlite3mc.

This issue could be related: https://github.com/pawelsalawa/sqlitestudio/issues/4171