{"record":{"id":"1e1a69d390315427","repo":"t8y2/dbx","slug":"failed-to-enable-sqlite-legacy-alter-table-for-the","errorCode":null,"errorMessage":"Failed to enable SQLite legacy_alter_table for the backup rename: {error}; failed to restore foreign_keys state: {restore_error}","messagePattern":"Failed to enable SQLite legacy_alter_table for the backup rename: (.+?); failed to restore foreign_keys state: (.+?)","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"crates/dbx-core/src/table_structure_sql/sqlite_rebuild.rs","lineNumber":628,"sourceCode":"    options: &TableStructureSqlOptions,\n    expected_revision: &str,\n) -> Result<db::QueryResult, String> {\n    let started_at = Instant::now();\n    let foreign_keys_enabled: i64 = conn\n        .pragma_query_value(None, \"foreign_keys\", |row| row.get(0))\n        .map_err(|error| format!(\"Failed to read SQLite foreign_keys state: {error}\"))?;\n    let legacy_alter_table_enabled: i64 = conn\n        .pragma_query_value(None, \"legacy_alter_table\", |row| row.get(0))\n        .map_err(|error| format!(\"Failed to read SQLite legacy_alter_table state: {error}\"))?;\n    conn.pragma_update(None, \"foreign_keys\", false)\n        .map_err(|error| format!(\"Failed to disable SQLite foreign key enforcement: {error}\"))?;\n    if let Err(error) = conn.pragma_update(None, \"legacy_alter_table\", true) {\n        let restore_error = conn.pragma_update(None, \"foreign_keys\", foreign_keys_enabled != 0).err();\n        return Err(match restore_error {\n            Some(restore_error) => format!(\n                \"Failed to enable SQLite legacy_alter_table for the backup rename: {error}; failed to restore foreign_keys state: {restore_error}\"\n            ),\n            None => format!(\"Failed to enable SQLite legacy_alter_table for the backup rename: {error}\"),\n        });\n    }\n\n    let operation_result = (|| {\n        conn.execute_batch(\"BEGIN IMMEDIATE\")\n            .map_err(|error| format!(\"Failed to begin SQLite table rebuild transaction: {error}\"))?;\n        let plan = build_change_plan(conn, options)?;\n        if plan.preview.schema_revision != expected_revision {\n            return Err(\n                \"SQLite schema changed or the structure draft differs from the preview. Refresh the table structure and preview the change again.\"\n                    .to_string(),\n            );\n        }\n        if !plan.preview.warnings.is_empty() {\n            return Err(format!(\"SQLite structure change cannot be applied: {}\", plan.preview.warnings.join(\" \")));\n        }\n        // Compare the complete schema so inbound references are covered while unrelated historical violations remain tolerated.\n        let foreign_key_baseline = foreign_key_violations(conn, &plan.schema)?;","sourceCodeStart":610,"sourceCodeEnd":646,"githubUrl":"https://github.com/t8y2/dbx/blob/c0390bff16418b651f4728520d99adf8ce48829a/crates/dbx-core/src/table_structure_sql/sqlite_rebuild.rs#L610-L646","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Close other connections/transactions holding locks on the database before running the rebuild","Run the structure change on a dedicated connection outside any outer transaction","Retry the operation once the database is idle; verify PRAGMA foreign_keys afterwards and reset it manually if needed","Check the embedded {error} detail for the underlying pragma failure cause"],"exampleFix":"// before\n// rebuild run inside caller's transaction\nBEGIN; CALL rebuild(...); COMMIT;\n// after\n// run rebuild on a fresh idle connection\nconn = pool.acquire(); rebuild(conn); // manages its own BEGIN IMMEDIATE\nPRAGMA foreign_keys; // verify state after","handlingStrategy":"retry","validationCode":"fn can_run_rebuild(conn: &rusqlite::Connection) -> bool {\n    conn.is_autocommit()\n        && conn.query_row(\"PRAGMA foreign_keys\", [], |r| r::<i64>(0)).is_ok()\n}","typeGuard":"fn pragma_settable(conn: &rusqlite::Connection) -> bool {\n    conn.pragma_update(None, \"foreign_keys\", conn.query_row::<_, i64, _>(\"PRAGMA foreign_keys\", [], |r| r.get(0)).unwrap_or(0)).is_ok()\n}","tryCatchPattern":"match rebuild::apply_change(conn, change) {\n    Err(msg) if msg.contains(\"legacy_alter_table\") => {\n        eprintln!(\"Rebuild aborted; verify PRAGMA foreign_keys state: {msg}\");\n        let _ = conn.pragma_update(None, \"foreign_keys\", true); // repair\n    }\n    other => other?,\n}","preventionTips":["Run rebuilds when no other writers hold locks","Never nest the rebuild inside an outer transaction","Check PRAGMA foreign_keys after any failed rebuild and restore it","Retry with a fresh idle connection rather than reusing a failed one"],"tags":["sqlite","pragma","migration","transaction","foreign-keys"],"backgroundTag":"pragma-update-failed","analyzedSha":"c0390bff16418b651f4728520d99adf8ce48829a","analyzedAt":"2026-09-05T23:05:10.900Z","contentChangedAt":"2026-09-05T23:05:10.900Z","schemaVersion":2},"datasetVersion":"2026-09-14T05:17:10.506Z"}