{"record":{"id":"49ba3fb29be41d02","repo":"nautechsystems/nautilus_trader","slug":"failed-to-advance-canonical-nonce-ledger-e","errorCode":null,"errorMessage":"Failed to advance canonical nonce ledger: {e}","messagePattern":"Failed to advance canonical nonce ledger: (.+?)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":7191,"sourceCode":"        .await\n        .map_err(|e| anyhow::anyhow!(\"Failed to record verified finality transition: {e}\"))?;\n\n        let nonce_result = sqlx::query(\n            \"\n            UPDATE execution_verification_nonce\n            SET next_canonical_nonce = $3, revision = revision + 1, updated_at = NOW()\n            WHERE chain_id = $1 AND wallet_address = $2\n              AND next_canonical_nonce = $4 AND revision = $5\n            \",\n        )\n        .bind(chain_id)\n        .bind(finality.wallet_address)\n        .bind(next_nonce)\n        .bind(nonce)\n        .bind(revision)\n        .execute(&mut *transaction)\n        .await\n        .map_err(|e| anyhow::anyhow!(\"Failed to advance canonical nonce ledger: {e}\"))?;\n        anyhow::ensure!(\n            nonce_result.rows_affected() == 1,\n            \"Canonical nonce ledger changed during finality transition\"\n        );\n        transaction\n            .commit()\n            .await\n            .map_err(|e| anyhow::anyhow!(\"Failed to commit verified finality transition: {e}\"))?;\n        Ok(())\n    }\n\n    /// Loads one durable execution intent by ID.\n    pub(crate) async fn get_execution_intent(\n        &self,\n        intent_id: i64,\n    ) -> anyhow::Result<ExecutionIntentRow> {\n        sqlx::query_as::<_, ExecutionIntentRow>(\n            \"","sourceCodeStart":7173,"sourceCodeEnd":7209,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/adapters/blockchain/src/cache/database.rs#L7173-L7209","documentation":"This error wraps SQLx failures from the UPDATE of execution_verification_nonce, which advances the wallet's next_canonical_nonce with an optimistic revision check inside the finality transaction (database.rs:7191). The nonce ledger must be advanced for the finality to commit, so any query failure aborts the transaction and is surfaced with this message.","triggerScenarios":"The UPDATE ... SET next_canonical_nonce = $3 ... WHERE chain_id = $1 AND wallet_address = $2 AND next_canonical_nonce = $4 AND revision = $5 fails at the driver level: connection loss, statement timeout, wrong parameter types (e.g. wallet_address encoding vs column type), or the table/columns missing due to unapplied migrations.","commonSituations":"Postgres briefly unavailable or restarted during a finality commit; migrations not applied so execution_verification_nonce does not exist; parameter type mismatch after a chain_id/wallet column type change; network flakiness between the adapter and the database under load.","solutions":["Inspect the wrapped {e} for the driver-level cause (connection reset, undefined table, type mismatch).","Apply pending database migrations to ensure execution_verification_nonce and its columns exist.","Verify the chain_id and wallet_address values passed in match the stored ledger row's types/format (checksummed vs lowercase address).","Retry the entire finality transition; it is transactional, so a failed nonce update rolls back cleanly and a retry with the current revision is safe.","Check pool configuration and statement timeouts if failures correlate with load."],"exampleFix":"// before: retry only the nonce update out of band\nlet _ = db.advance_nonce(chain_id, wallet).await;\n// after: retry the whole transactional transition so state stays consistent\nfor attempt in 0..3 {\n    match db.record_verified_finality(&finality).await {\n        Ok(()) => break,\n        Err(e) if attempt < 2 && is_transient(&e) => tokio::time::sleep(backoff(attempt)).await,\n        Err(e) => return Err(e.context(\"advance canonical nonce ledger\")),\n    }\n}","handlingStrategy":"retry","validationCode":"let ledger: Option<(i64, i64)> = sqlx::query_as(\n    \"SELECT next_canonical_nonce, revision FROM execution_verification_nonce WHERE chain_id = $1 AND wallet_address = $2\")\n    .bind(chain_id).bind(wallet).fetch_optional(&pool).await?;\nanyhow::ensure!(ledger.is_some(), \"nonce ledger row missing for {wallet} on chain {chain_id}\");","typeGuard":"fn is_retryable_db_error(e: &anyhow::Error) -> bool {\n    let s = e.to_string();\n    s.contains(\"connection\") || s.contains(\"timed out\") || s.contains(\"reset by peer\")\n}","tryCatchPattern":"match res {\n    Err(e) if is_retryable_db_error(&e) => retry_with_backoff(|| db.record_verified_finality(&finality)).await?,\n    Err(e) => return Err(e.context(\"nonce ledger update failed\")),\n    Ok(()) => {}\n}","preventionTips":["Run migrations before every deploy so execution_verification_nonce exists.","Initialize the nonce ledger row when a wallet is first configured.","Normalize wallet address case before all database calls.","Retry whole transactions, never individual statements inside one."],"tags":["database","sqlx","transaction","nonce"],"backgroundTag":"database-write-failed","analyzedSha":"18893faf8b356be3320add8de2f861b0b647cf06","analyzedAt":"2026-09-08T20:49:34.690Z","contentChangedAt":"2026-09-08T20:49:34.690Z","schemaVersion":2},"datasetVersion":"2026-09-14T00:17:10.932Z"}