The FTS5 data structures are internally consistent and there is no corruption at the database level but, as you say, the "licenses_fts" index does not match the contents of the "licenses" table.

Your triggers look correct to me.

Are you using "REPLACE INTO", or "UPDATE OR REPLACE" on the "licenses" table without having first executed "PRAGMA recursive_triggers = 1"? The docs note that delete triggers will not be fired in this case, which would explain things. Second paragraph under "REPLACE" here:

https://www.sqlite.org/lang_conflict.html

Other than that, could there be any code that writes to "licenses_fts" directly - bypassing "licences" altogether.

Dan.

Thanks - that's really useful. I didn't know about recursive_triggers.

I just noticed that the triggers are operating on rowid but the license table has a text primary key, so I'm thinking maybe that could be part of the problem:

CREATE TABLE [licenses] (
   [key] TEXT PRIMARY KEY,
   [name] TEXT,
   [spdx_id] TEXT,
   [url] TEXT,
   [node_id] TEXT
);

Another possibility: I noticed on https://www.sqlite.org/rowidtable.html this note:

If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.

I ran VACUUM on that database quite often. Is it possible that running VACUUM is rewriting the rowid values for the licenses table but leaving the values in the licenses_fts table unchanged? If so, maybe I get a new set of orphaned duplicate rows every time I vacuum?

the triggers are operating on rowid but the license table has a text primary key

Shouldn't be a problem. The table still has a rowid.

Forgot about that. You should probably fix that.

In practice running VACUUM won't change rowids for any table that has at least one index on it, and your "TEXT PRIMARY KEY" column counts as an index. But maybe if you're doing the VACUUM with a very old shell tool or something.

If you run VACUUM do the rowids in "licenses" change?

Not as far as I can tell:

/tmp % sqlite-utils github.db 'select rowid, * from licenses' -t
  rowid  key           name                                            spdx_id       url                                           node_id
-------  ------------  ----------------------------------------------  ------------  --------------------------------------------  ----------------
     58  cc-by-4.0     Creative Commons Attribution 4.0 International  CC-BY-4.0     https://api.github.com/licenses/cc-by-4.0     MDc6TGljZW5zZTI1
    109  unlicense     The Unlicense                                   Unlicense     https://api.github.com/licenses/unlicense     MDc6TGljZW5zZTE1
    112  bsd-3-clause  BSD 3-Clause "New" or "Revised" License         BSD-3-Clause  https://api.github.com/licenses/bsd-3-clause  MDc6TGljZW5zZTU=
   6189  other         Other                                           NOASSERTION                                                 MDc6TGljZW5zZTA=
   8860  gpl-3.0       GNU General Public License v3.0                 GPL-3.0       https://api.github.com/licenses/gpl-3.0       MDc6TGljZW5zZTk=
   8932  mit           MIT License                                     MIT           https://api.github.com/licenses/mit           MDc6TGljZW5zZTEz
   9150  apache-2.0    Apache License 2.0                              Apache-2.0    https://api.github.com/licenses/apache-2.0    MDc6TGljZW5zZTI=
/tmp % sqlite-utils github.db 'vacuum'                          
[{"rows_affected": -1}]
/tmp % sqlite-utils github.db 'select rowid, * from licenses' -t
  rowid  key           name                                            spdx_id       url                                           node_id
-------  ------------  ----------------------------------------------  ------------  --------------------------------------------  ----------------
     58  cc-by-4.0     Creative Commons Attribution 4.0 International  CC-BY-4.0     https://api.github.com/licenses/cc-by-4.0     MDc6TGljZW5zZTI1
    109  unlicense     The Unlicense                                   Unlicense     https://api.github.com/licenses/unlicense     MDc6TGljZW5zZTE1
    112  bsd-3-clause  BSD 3-Clause "New" or "Revised" License         BSD-3-Clause  https://api.github.com/licenses/bsd-3-clause  MDc6TGljZW5zZTU=
   6189  other         Other                                           NOASSERTION                                                 MDc6TGljZW5zZTA=
   8860  gpl-3.0       GNU General Public License v3.0                 GPL-3.0       https://api.github.com/licenses/gpl-3.0       MDc6TGljZW5zZTk=
   8932  mit           MIT License                                     MIT           https://api.github.com/licenses/mit           MDc6TGljZW5zZTEz
   9150  apache-2.0    Apache License 2.0                              Apache-2.0    https://api.github.com/licenses/apache-2.0    MDc6TGljZW5zZTI=

I'm going to do some deeper digging into what happens when I run the FTS-related code (this is actually all happening when I run my https://github.com/dogsheep/github-to-sqlite script). I'll report back if I find anything useful.

I've managed to replicate the issue!

(github-to-sqlite) /tmp % sqlite-utils tables --counts github.db | grep licenses
 {"table": "licenses", "count": 7},
 {"table": "licenses_fts_data", "count": 35},
 {"table": "licenses_fts_idx", "count": 16},
 {"table": "licenses_fts_docsize", "count": 9151},
 {"table": "licenses_fts_config", "count": 1},
 {"table": "licenses_fts", "count": 7},
(github-to-sqlite) /tmp % github-to-sqlite repos github.db dogsheep       
(github-to-sqlite) /tmp % sqlite-utils tables --counts github.db | grep licenses
 {"table": "licenses", "count": 7},
 {"table": "licenses_fts_data", "count": 45},
 {"table": "licenses_fts_idx", "count": 26},
 {"table": "licenses_fts_docsize", "count": 9161},
 {"table": "licenses_fts_config", "count": 1},
 {"table": "licenses_fts", "count": 7},

I dug into this and it turns out that github-to-sqlite repos command does indeed run the following SQL 10 times:

INSERT OR REPLACE INTO [licenses] ([key], [name], [node_id], [spdx_id], [url]) VALUES 
   (?, ?, ?, ?, ?);

So I like your PRAGMA recursive_triggers theory. I'm going to give that a try.

I can confirm that using PRAGMA recursive_triggers=on; appears to fix this issue - running the script no longer adds ten new records to the licenses_fts_docsize table.

This leaves me with a smaller problem: I now have a whole bunch of tables with enormous amounts of obsolete data in their *_fts_docsize tables. I'll write myself a utility for cleaning those up, probably by running something like this:

delete from licenses_fts_docsize
where rowid not in (select rowid from licenses_fts)

I'm not sure if I should delete data from any of the other tables - https://github-to-sqlite.dogsheep.net/github/licenses_fts_data for example has 41 rows in (when licenses_fts has only 7) but those rows don't seem to have IDs that correspond to the rowid column in licenses_fts.

Thanks for your help Dan - it turned out to be the INSERT OR REPLACE issue you suggested. I shipped a new release of my software with a fix: https://sqlite-utils.readthedocs.io/en/stable/changelog.html#v2-17

Easiest thing would be to run a 'rebuild' to rebuild the FTS index from scratch based on the contents of the content table. i.e.

    INSERT INTO licenses_fts(licenses_fts) VALUES('rebuild');

https://www.sqlite.org/fts5.html#the_rebuild_command

Dan.

That's a much better fix than the hack I had where I would delete spare rows from that table.

I just released sqlite-utils 2.18 which adds a sqlite-utils rebuild-fts data.db command-line command and a db["my_table"].rebuild_fts() Python API method: https://sqlite-utils.readthedocs.io/en/stable/changelog.html#v2-18