I don't know if anyone needs this besides me, but recently I dug quite deeply into SQLite's source code to figure out why it was being corrupted when optimized. There is an official documentation for "special commands" and options that control optimizations and merging: https://www.sqlite.org/fts5.html#special_insert_commands , but if you are like me - it may not be descriptive enough of the actual processes.
Thus, I just finished writing a small github gist which goes into the nuts and bolts of FTS5 https://gist.github.com/indutny/ae44fd93dde2736205609d19a21b87cc
...the document is hardly comprehensive, but it has all the names of the functions and structure fields that I found very useful when trying to understand why it was crashing.
FWIW, here's the reproduction test case for the crash: https://gist.github.com/indutny/2ae1d7ac06b06c0abf6671900e4ea443
It uses a quite useful (but not available unless you compile sqlite with SQLITE_TEST) function `fts5_decode` that can be used to pretty print FTS5 index data. Every time you repeat the insert+insert+optimize sequence - it creates a new level..
...and eventually we hit 2000 levels which is the limit of FTS5. After that any access to the database will result in SQLITE_CORRUPT_VTAB errors, which isn't fun!
The table cannot be dropped because it cannot be parsed, and it cannot be rebuilt for the same reason. The only solution is to erase whole database and start a new, or to recompile SQLite with increased level limit and drop the table using CLI utility.
There might be a lesson here, but I don't know what it is. 😂
/end
@indutny Still a very commendable effort! I've been thinking of looking at how they construct their indexes so this is v inspiring
@mauve thankfully SQLite is Open-source!