SQLite User Forum

bug: importing duplicate dumped FTS5 tables can cause malformed database schemas
Login

bug: importing duplicate dumped FTS5 tables can cause malformed database schemas

(1) By hitchmanr on 2022-02-01 05:30:33 [source]

I tried using .dump to copy a search index between two databases, and managed to get a "Error: malformed database schema (search) - table search already exists" error -- somehow it duplicated the virtual tables? This happens in both 3.32.2 and the latest version (3.37.2).

This script gives the expected error importing the dump and otherwise works: "Error: near line 3: table search already exists":

rm -f a.db b.db

sqlite3 a.db "create table search(title)"
sqlite3 b.db "create table search(title)"
sqlite3 b.db "insert into search values('some random text')"
sqlite3 b.db .dump | sqlite3 a.db

sqlite3 a.db .schema

This script results in "Error: malformed database schema (search) - table search already exists" after creating duplicate virtual tables, instead of refusing to make a duplicate:

rm -f a.db b.db

sqlite3 a.db "create virtual table search using fts5(title)"
sqlite3 b.db "create virtual table search using fts5(title)"
sqlite3 b.db "insert into search values('some random text')"
sqlite3 b.db .dump | sqlite3 a.db

sqlite3 a.db .schema

I managed to fix my database using this tip: https://sqlite.org/forum/forumpost/7eb73e0989fbe6d5