FTS5 Table Drop and Access Errors Due to Missing Custom Tokenizer

Understanding FTS5 Table Dependencies and Tokenizer Validation Failures

When working with SQLite’s Full-Text Search version 5 (FTS5), users may encounter two critical errors related to custom tokenizers:

1. Inability to drop FTS5 tables with no such tokenizer errors
2. Invalid no such fts5 table errors when querying fts5vocab auxiliary tables

These issues arise from how FTS5 validates tokenizer dependencies during schema operations and auxiliary table access. The errors occur even when the tokenizer is not logically required for the requested operation, creating unexpected roadblocks in database maintenance. This guide dissects the technical underpinnings of these failures and provides actionable solutions.

FTS5 Internal Architecture and Tokenizer Dependency Chains

Shadow Table Ecosystem

FTS5 virtual tables rely on six underlying shadow tables that store inverted indexes, document sizes, and configuration data:

• test (main virtual table)
• test_data (inverted index storage)
• test_idx (segment index metadata)
• test_content (original document storage if content= option used)
• test_docsize (document size statistics)
• test_config (tokenizer configuration parameters)

The CREATE VIRTUAL TABLE statement initializes this ecosystem with references to the tokenizer implementation. The test_config table stores serialized tokenizer parameters, creating a hard dependency on the tokenizer’s availability during certain operations.

Tokenizer Binding Mechanics

Custom tokenizers register with SQLite via sqlite3Fts5CreateTokenizer during extension loading. When an FTS5 table uses tokenize=example, SQLite stores three critical pieces of information:

1. Tokenizer name (example)
2. Pointer to the tokenizer implementation (if loaded)
3. Configuration parameters passed during table creation

During DDL operations like DROP TABLE, FTS5 attempts to clean up tokenizer-specific resources by consulting the test_config table. This process requires re-instantiating the tokenizer object using stored parameters, which fails if the tokenizer isn’t registered in the current database connection.

Vocabulary Table Dependencies

The fts5vocab auxiliary tables provide term statistics for FTS5 indexes. Their creation syntax (CREATE VIRTUAL TABLE temp.foo USING fts5vocab(main, search, row)) links them to the target FTS5 table through schema and table name references. However, accessing these tables triggers implicit validation of the underlying FTS5 table’s tokenizer, even though vocabulary data doesn’t require tokenization.

Root Causes of Tokenizer-Related Operation Failures

1. Tokenizer Rehydration During Table Deletion

The DROP TABLE command for FTS5 virtual tables initiates a cleanup routine that:

• Deletes all shadow tables
• Removes tokenizer configuration from test_config
• Invalidates internal FTS5 registry entries

To perform these actions, SQLite attempts to reconstruct the tokenizer’s state from the test_config table. If the tokenizer isn’t available (e.g., in a new SQLite shell session without extension loading), this fails with no such tokenizer, aborting the entire drop operation.

2. Overzealous Tokenizer Checks in Vocabulary Table Access

When querying an fts5vocab table, SQLite validates the existence of the linked FTS5 table by:

1. Looking up the FTS5 table in sqlite_schema
2. Rehydrating its tokenizer configuration from test_config
3. Verifying tokenizer availability

This third step is unnecessary for vocabulary queries, as they only read pre-tokenized terms from the inverted index. The validation error (no such tokenizer) masquerades as a missing table error (no such fts5 table), misleading users about the true cause.

3. Session-Specific Tokenizer Registration

Custom tokenizers are registered per database connection, not persistently stored. Operations requiring tokenizer access will fail in sessions where:

• The tokenizer wasn’t originally registered
• The tokenizer was registered but the connection was reset
• Multiple connections have inconsistent tokenizer registrations

This creates an environment where schema operations succeed in some sessions but fail in others, depending on transient registration states.

Resolving Tokenizer Dependency Issues in FTS5 Operations

Immediate Workarounds for Existing Deployments

Manual Shadow Table Deletion (Not Recommended)
Directly deleting FTS5 shadow tables bypasses tokenizer validation:

DELETE FROM sqlite_schema WHERE name LIKE 'test_%';

Risks: Leaves orphaned entries in SQLite’s internal schema. May corrupt FTS5 consistency if other virtual tables exist.

Temporary Tokenizer Registration
Re-register the tokenizer in the current session before dropping the table:

// In application code
sqlite3Fts5CreateTokenizer(pFts5, "example", pUserData, xCreate, xDelete, xTokenize);

For SQLite Shell: Reload the extension containing the tokenizer before schema changes.

Schema Version Bumping
Force SQLite to reload schema information, which may clear cached tokenizer dependencies:

PRAGMA schema_version = schema_version + 1;

Long-Term Solutions

Apply SQLite Patches
Integrate the official fixes addressing these issues:

1. Drop Table Fix (69ef47eeee8b5368): Modifies sqlite3Fts5DropTable to skip tokenizer rehydration during shadow table deletion.
2. Vocabulary Table Fix (ca4fdcb8ae95d2a6): Updates fts5VocabConnectMethod to validate FTS5 table existence without tokenizer checks.

Compilation Steps:

1. Download the SQLite amalgamation source
2. Apply the patches using patch -p1 < fix.patch
3. Recompile with -DSQLITE_ENABLE_FTS5

Tokenizer Proxy Registration
Create a stub tokenizer that satisfies FTS5’s lookup requirements during schema operations:

static int xDummyTokenizerCreate(
  void *pCtx, 
  const char **azArg, 
  int nArg, 
  Fts5Tokenizer **ppOut
){
  return SQLITE_OK;
}

sqlite3Fts5CreateTokenizer(pFts5, "example", NULL, xDummyTokenizerCreate, NULL, NULL);

This allows tokenizer validation to succeed without fully reimplementing the original tokenizer.

Schema Isolation with ATTACH DATABASE
Contain FTS5 tables in separate databases where tokenizer availability is guaranteed:

ATTACH DATABASE 'fts_tables.db' AS fts;
CREATE VIRTUAL TABLE fts.test USING fts5(x, tokenize=example);
DETACH DATABASE fts;

-- When dropping
ATTACH DATABASE 'fts_tables.db' AS fts;
DROP TABLE fts.test;

Preventive Best Practices

Tokenizer Lifetime Management

• Register custom tokenizers in all potential database connections
• Use sqlite3_auto_extension to auto-load tokenizers on connection startup
• Implement connection hooks to verify tokenizer registration

FTS5 Table Maintenance Protocol

1. Before dropping tables, ensure tokenizer extensions are loaded
2. Query sqlite_schema to identify FTS5 tables with custom tokenizers:

SELECT name, sql FROM sqlite_schema 
WHERE type='table' 
AND sql LIKE '%tokenize=%';

3. Maintain a registry of used tokenizers and their associated tables

Custom Tokenizer Fallback Handling
Enhance tokenizer implementations to handle "maintenance mode" requests:

static int xExampleTokenizer(
  Fts5Tokenizer *pTokenizer,
  int flags,
  const char *pText, int nText,
  int (*xToken)(void*, int, const char*, int, int, int)
){
  if( flags & FTS5_TOKENIZE_MAINTENANCE ){
    // Return dummy tokens during DROP TABLE
    xToken(pCtx, 0, "", 0, 0, 0);
    return SQLITE_OK;
  }
  // Normal tokenization
}

Advanced Debugging Techniques

FTS5 Trace Module
Activate FTS5 tracing to log tokenizer interactions:

PRAGMA fts5_trace = 1;
-- Execute failing commands
PRAGMA fts5_trace = 0;

Analyze logs for tokenizer instantiation attempts during drop operations.

Schema Checksum Verification
Use PRAGMA quick_check to identify inconsistencies after failed drops:

PRAGMA quick_check;
-- Look for 'malformed database schema' errors

SQLITE_DEBUG Tokenizer Simulation
Compile SQLite with debugging symbols and set breakpoints in:

• sqlite3Fts5IndexQuery
• fts5VocabFilterMethod
• sqlite3Fts5DropTables

By understanding FTS5’s internal tokenizer validation mechanics and applying these targeted solutions, developers can overcome the limitations posed by missing tokenizers during critical schema operations. The key lies in either circumventing unnecessary tokenizer checks (via patches), ensuring tokenizer availability across sessions, or implementing fallback handlers for maintenance scenarios.