Drop a table originally created with 'unknown

问题:

I have a sqlite3 database. A single table inside this DB can't be dropped, the error message says unknown tokenizer: mm.

I tried it directly with the command DROP TABLE tablename; inside the newest SQLiteSpy v1.9.11 and also within .NET code and the official sqlite NuGet package v 1.0.103.

How can I drop a table where the tokenizer is unknown?

回答1:

The documentation says:

For each FTS virtual table in a database, three to five real (non-virtual) tables are created to store the underlying data. These real tables are called "shadow tables". The real tables are named "%_content", "%_segdir", "%_segments", "%_stat", and "%_docsize", where "%" is replaced by the name of the FTS virtual table.

So to get rid of that table, drop the shadow tables:

DROP TABLE tablename_content;
DROP TABLE tablename_segdir;
DROP TABLE tablename_segments;
DROP TABLE tablename_stat;
DROP TABLE tablename_docsize;

And then use the (very dangerous) PRAGMA writable_schema to remove the remaining information about this table from the system table:

PRAGMA writable_schema = ON;
DELETE FROM sqlite_master WHERE type = 'table' AND name = 'tablename';

SQLite caches schema information, so then you need to close and re-open the database.