t8y2/dbx · error

Failed to enable SQLite legacy_alter_table for the backup…

Error message

Failed to enable SQLite legacy_alter_table for the backup rename: {error}; failed to restore foreign_keys state: {restore_error}

What it means

Composite error from a SQLite table rebuild: enabling the legacy_alter_table pragma failed, and the attempted restore of the foreign_keys pragma also failed. Both messages are concatenated so the developer knows the schema change aborted and the FK setting may be left in the wrong state.

Solutions

  1. Close other connections/transactions holding locks on the database before running the rebuild
  2. Run the structure change on a dedicated connection outside any outer transaction
  3. Retry the operation once the database is idle; verify PRAGMA foreign_keys afterwards and reset it manually if needed
  4. Check the embedded {error} detail for the underlying pragma failure cause

Example fix

// before
// rebuild run inside caller's transaction
BEGIN; CALL rebuild(...); COMMIT;
// after
// run rebuild on a fresh idle connection
conn = pool.acquire(); rebuild(conn); // manages its own BEGIN IMMEDIATE
PRAGMA foreign_keys; // verify state after
Defensive patterns

Strategy: retry

Validate before calling

fn can_run_rebuild(conn: &rusqlite::Connection) -> bool {
    conn.is_autocommit()
        && conn.query_row("PRAGMA foreign_keys", [], |r| r::<i64>(0)).is_ok()
}

Type guard

fn pragma_settable(conn: &rusqlite::Connection) -> bool {
    conn.pragma_update(None, "foreign_keys", conn.query_row::<_, i64, _>("PRAGMA foreign_keys", [], |r| r.get(0)).unwrap_or(0)).is_ok()
}

Try / catch

match rebuild::apply_change(conn, change) {
    Err(msg) if msg.contains("legacy_alter_table") => {
        eprintln!("Rebuild aborted; verify PRAGMA foreign_keys state: {msg}");
        let _ = conn.pragma_update(None, "foreign_keys", true); // repair
    }
    other => other?,
}

Prevention

When it happens

Trigger: pragma_update(None, "legacy_alter_table", true) failing inside execute_change_transaction (e.g. connection busy, pragma disallowed in active transaction, locked database), with the compensating foreign_keys pragma_update also returning an error.

Common situations: Rebuilding a table while another connection holds a write lock; running the change inside an outer transaction where pragma updates are rejected; WAL/busy contention during migration scripts.

Related errors

AI-assisted analysis of t8y2/dbx@c0390bff16 (2026-09-05). Data as JSON: /api/errors/1e1a69d390315427. Report an issue: GitHub.

Appendix: source

Thrown at crates/dbx-core/src/table_structure_sql/sqlite_rebuild.rs:628

    options: &TableStructureSqlOptions,
    expected_revision: &str,
) -> Result<db::QueryResult, String> {
    let started_at = Instant::now();
    let foreign_keys_enabled: i64 = conn
        .pragma_query_value(None, "foreign_keys", |row| row.get(0))
        .map_err(|error| format!("Failed to read SQLite foreign_keys state: {error}"))?;
    let legacy_alter_table_enabled: i64 = conn
        .pragma_query_value(None, "legacy_alter_table", |row| row.get(0))
        .map_err(|error| format!("Failed to read SQLite legacy_alter_table state: {error}"))?;
    conn.pragma_update(None, "foreign_keys", false)
        .map_err(|error| format!("Failed to disable SQLite foreign key enforcement: {error}"))?;
    if let Err(error) = conn.pragma_update(None, "legacy_alter_table", true) {
        let restore_error = conn.pragma_update(None, "foreign_keys", foreign_keys_enabled != 0).err();
        return Err(match restore_error {
            Some(restore_error) => format!(
                "Failed to enable SQLite legacy_alter_table for the backup rename: {error}; failed to restore foreign_keys state: {restore_error}"
            ),
            None => format!("Failed to enable SQLite legacy_alter_table for the backup rename: {error}"),
        });
    }

    let operation_result = (|| {
        conn.execute_batch("BEGIN IMMEDIATE")
            .map_err(|error| format!("Failed to begin SQLite table rebuild transaction: {error}"))?;
        let plan = build_change_plan(conn, options)?;
        if plan.preview.schema_revision != expected_revision {
            return Err(
                "SQLite schema changed or the structure draft differs from the preview. Refresh the table structure and preview the change again."
                    .to_string(),
            );
        }
        if !plan.preview.warnings.is_empty() {
            return Err(format!("SQLite structure change cannot be applied: {}", plan.preview.warnings.join(" ")));
        }
        // Compare the complete schema so inbound references are covered while unrelated historical violations remain tolerated.
        let foreign_key_baseline = foreign_key_violations(conn, &plan.schema)?;

View on GitHub (pinned to c0390bff16)