SQLite Dump Import Fails Due to Missing sqlite_sequence Table

Issue Overview: Dump Import Fails with DELETE on sqlite_sequence

When attempting to import a SQLite database dump into a new database, users may encounter the error Parse error near line 5: no such table: sqlite_sequence, specifically referencing a DELETE FROM sqlite_sequence; statement in the dump file. This occurs even when the original database appears to function correctly and the dump operation completes without errors. The problem is reproducible in scenarios where:

1. A table with an AUTOINCREMENT column is created and later dropped.
2. The .dump command generates SQL that includes operations on the sqlite_sequence system table.
3. The target database lacks the sqlite_sequence table during import.

The sqlite_sequence table is an internal SQLite system table that tracks the maximum sequence values for AUTOINCREMENT columns. When a table with AUTOINCREMENT is created, SQLite automatically generates this table if it does not already exist. However, when such tables are dropped, SQLite does not automatically remove the sqlite_sequence table. The .dump command includes DELETE and INSERT statements for sqlite_sequence to reset its state during restoration, but this becomes problematic if the target database has no AUTOINCREMENT tables (and thus no sqlite_sequence table).

Possible Causes: AUTOINCREMENT Handling and Dump Logic

1. Residual sqlite_sequence Entries After Dropping AUTOINCREMENT Tables

When a table with an AUTOINCREMENT column is dropped, SQLite removes its corresponding row from sqlite_sequence but does not drop the sqlite_sequence table itself. This leaves the table empty but still present in the schema. The .dump command includes all system tables by default, generating DELETE FROM sqlite_sequence; and INSERT INTO sqlite_sequence ... statements even if the table is empty. During import, these statements execute before any AUTOINCREMENT tables are recreated, causing a failure if the target database has no AUTOINCREMENT tables (and thus no sqlite_sequence table).

2. Default .dump Behavior Includes System Tables

The .dump command in SQLite versions prior to 3.47 includes system tables like sqlite_sequence unless explicitly excluded with the --nosys flag. This design assumes the presence of sqlite_sequence in the target database, which is invalid if no AUTOINCREMENT tables exist in the dumped schema. The inclusion of system table operations in dumps creates unnecessary dependencies on internal structures that may not exist in the target environment.

3. Version-Specific Handling of AUTOINCREMENT Metadata

Older SQLite versions (e.g., 3.40.1) do not conditionally exclude sqlite_sequence operations from dumps when the table is absent. This is a long-standing edge case that was only addressed in SQLite 3.47. The bug arises because the dump logic does not check whether sqlite_sequence exists in the source database before generating DELETE/INSERT statements for it.

Troubleshooting Steps, Solutions & Fixes

Step 1: Verify SQLite Version and Upgrade to 3.47+

The root cause of the error is resolved in SQLite 3.47 via check-in 8d7fe903d09a2a79, which ensures .dump skips sqlite_sequence operations if the table does not exist in the source database.

Action:

• Check your SQLite version:

  sqlite3 --version
  

• Upgrade to SQLite 3.47 or newer if the output shows a version older than 3.47. Precompiled binaries are available on the SQLite Download Page.

Step 2: Use –nosys Flag with .dump to Exclude System Tables

If upgrading is not immediately feasible, use the --nosys flag to exclude system tables from the dump:

Modified Dump Command:

sqlite3 original.db ".dump --nosys" > dump.sql

This skips sqlite_sequence and other internal tables, eliminating the problematic DELETE statement. However, this approach has tradeoffs:

• Pros: Avoids errors during import.
• Cons: Discards sqlite_sequence data, which is necessary for restoring AUTOINCREMENT values accurately.

Step 3: Manually Edit the Dump File

For one-off fixes, manually remove references to sqlite_sequence from the dump file:

1. Open the dump file in a text editor.
2. Delete or comment out the line:

   DELETE FROM sqlite_sequence;
   

3. Remove any INSERT INTO sqlite_sequence ... statements.

Caution: This method is error-prone and impractical for automated workflows.

Step 4: Recreate sqlite_sequence in the Target Database

If retaining sqlite_sequence data is critical, explicitly create the table before importing the dump:

Modified Import Script:

sqlite3 new.db "CREATE TABLE sqlite_sequence(name, seq);"
sqlite3 new.db < dump.sql

This ensures the DELETE and INSERT statements execute without errors. However, this creates an empty sqlite_sequence table, which may not match the original database’s state.

Step 5: Avoid AUTOINCREMENT Unless Necessary

AUTOINCREMENT is rarely required in SQLite. Instead, use INTEGER PRIMARY KEY for implicit auto-incrementing behavior without sqlite_sequence:

Example:

CREATE TABLE t_test1 (
  id INTEGER PRIMARY KEY,  -- Implicit auto-increment
  c_ VARCHAR(255)
);

This eliminates reliance on sqlite_sequence entirely.

Final Recommendation

Upgrade to SQLite 3.47+ and use --nosys for dumps that do not require AUTOINCREMENT state preservation. For legacy systems, manually editing dumps or pre-creating sqlite_sequence provides a workaround, but these are suboptimal compared to upgrading.