Triggers that insert into fts5 tables fail to transact

[aeroheim]

I've got a bit of a strange error that I'm seeing under oddly specific circumstances.

In essence, when a trigger that inserts into an fts5 table runs after a statement using a RETURNING clause, the trigger itself (I presume) fails to transact and results in the following error: SqliteError: cannot commit - no transaction is active.

The example code below should reproduce the issue:

const Database = require('better-sqlite3');
const db = new Database(':memory:');

db.exec(`
  CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
  CREATE VIRTUAL TABLE b USING fts5(name);
  CREATE TRIGGER a_trigger AFTER INSERT ON a
  BEGIN
    INSERT INTO b (name) VALUES ('test');
  END;
`);

db.transaction(() => {
  // SqliteError: cannot commit - no transaction is active
  const { id } = db.prepare(`INSERT INTO a VALUES (0, 'test') RETURNING id;`).get();
})();

Running the same statements in the sqlite repl succeeds without any issues:

λ sqlite3
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
sqlite> CREATE VIRTUAL TABLE b USING fts5(name);
sqlite> CREATE TRIGGER a_trigger AFTER INSERT ON a BEGIN INSERT INTO b (name) VALUES ('test'); END;
sqlite> BEGIN TRANSACTION; INSERT INTO a VALUES(0, 'test') RETURNING id; COMMIT;
0

And here's a simple sandbox that contains the code above and reproduces the issue. You can run yarn start or node index/src.js on the sandbox's terminal to run the code.

[JoshuaWise]

I was able to reproduce this. I'll look into it now.

[JoshuaWise]

I've confirmed that this is an issue with SQLite itself, not better-sqlite3. I've created a post on the SQLite3 forum to track the issue: https://sqlite.org/forum/forumpost/21127c1160

[leafac]

Passing by to say that y’all are awesome!

I ran into exactly the same bug, and before I could report it, not only you had already found it, but you had already fixed it!

By the way, here’s my snippet to reproduce the bug. It isn’t exactly the same because it doesn’t use a transaction, but I believe it’ll be fixed by the same fix (right?):

const database = require("better-sqlite3")(":memory:");

database.exec(`
  CREATE TABLE "table1" ("column1");
  CREATE VIRTUAL TABLE "table2" USING fts5("column1");
  CREATE TRIGGER "trigger1" AFTER INSERT ON "table1" BEGIN
    INSERT INTO "table2" ("rowid", "column1") VALUES ("new"."rowid", "new"."column1");
  END;
`);

console.log(database.prepare(`INSERT INTO "table1" ("column1") VALUES (10) RETURNING *`).get()); // This returns ‘{ column1: 10 }’, but that’s a lie. The row was never inserted, as proved by the next line.
console.log(database.prepare(`SELECT * FROM "table1"`).all()); // Expected ‘[{ column1: 10 }]’, got ‘[]’.

[JoshuaWise]

This should be fixed in the most recent version of better-sqlite3 (which has been updated to use the most recent version of SQLite, which contains the actual fix)

[leafac]

I just tested the code posted by @aeroheim with better-sqlite3@7.4.4 and I’m afraid as far as I can tell the issue remains.

[JoshuaWise]

My mistake, it looks like SQLite team did create a fix, but it hasn't been released yet.

[JoshuaWise]

This should be fixed in better-sqlite3 version 7.4.6 (for real, this time 😉)