[bug]: UNIQUE constraint failed when applying kv_payments_migration

[guggero]

Pre-Submission Checklist

• I have searched the existing issues and believe this is a new bug.
• I am not asking a question about how to use lnd, but reporting a bug (otherwise open a discussion).

LND Version

v0.21.0-beta.rc1

LND Configuration

Relevant parts:

  --db.backend=sqlite
  --db.use-native-sql

Backend Version

Bitcoin Core v31

Backend Configuration

Not relevant

OS/Distribution

Ubuntu 24.04

Bug Details & Steps to Reproduce

Updated from v0.20.0-beta to v0.21.0-beta.rc1 with existing payments (very old node), then received the following error:

2026-05-09 11:57:41.864 [INF] LTND: Opening the main database, this might take a few minutes...
2026-05-09 11:57:41.868 [INF] LTND: Creating local graph and channel state DB instances
2026-05-09 11:57:41.868 [INF] CHDB: Checking for schema update: latest_version=35, db_version=35
2026-05-09 11:57:41.868 [INF] CHDB: Applying 2 optional migrations
2026-05-09 11:57:41.868 [INF] CHDB: Checking for optional update: name=prune_revocation_log
2026-05-09 11:57:41.868 [DBG] CHDB: Skipping optional migration: name=prune_revocation_log as it is not specified in the config
2026-05-09 11:57:41.868 [INF] CHDB: Checking for optional update: name=gc_decayed_log
2026-05-09 11:57:41.868 [DBG] CHDB: Skipping optional migration: name=gc_decayed_log as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration '000001_invoices' (version 1) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration '000002_amp_invoices' (version 2) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration '000003_invoice_events' (version 3) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration '000004_invoice_expiry_fix' (version 4) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration '000005_migration_tracker' (version 5) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration '000006_invoice_migration' (version 6) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration 'kv_invoice_migration' (version 7) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration '000007_invoice_add_settled_index' (version 8) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration '000008_graph' (version 9) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration 'kv_graph_migration' (version 10) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration '000009_graph_v2' (version 11) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration '000010_payments' (version 12) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Skipping migration '000011_payment_duplicates' (version 13) as it has already been applied
2026-05-09 11:57:41.869 [INF] SQLD: Migrating SQL schema to version 11
2026-05-09 11:57:41.869 [INF] SQLD: Applying migrations from version=11
2026-05-09 11:57:41.869 [INF] SQLD: Applying custom migration 'kv_payments_migration' (version 14) to schema version 11
2026-05-09 11:57:41.869 [INF] PYDB: Starting payment migration from KV to SQL...
2026-05-09 11:57:41.944 [INF] PYDB: Found ~5513 index entries to migrate (includes duplicates)
2026-05-09 11:57:41.946 [INF] SQLB: Closing database channeldb
2026-05-09 11:57:41.946 [INF] SQLB: Closing database macaroondb
2026-05-09 11:57:41.946 [INF] SQLB: Closing database decayedlogdb
2026-05-09 11:57:41.946 [INF] SQLB: Closing database towerclientdb
2026-05-09 11:57:41.946 [INF] SQLB: Closing database towerserverdb
2026-05-09 11:57:41.946 [INF] SQLB: Closing database walletdb
2026-05-09 11:57:41.947 [ERR] LTND: faild to run migrations for the native SQL store: error applying migration 'kv_payments_migration' (version 14) to schema version 11: failed to migrate payments to SQL: migrate payments: sql unique constraint violation: constraint failed: UNIQUE constraint failed: payment_htlc_attempts.attempt_index (2067)
2026-05-09 11:57:41.947 [ERR] LTND: Shutting down due to error in main method rev=dfb7d5 err="faild to run migrations for the native SQL store: error applying migration 'kv_payments_migration' (version 14) to schema version 11: failed to migrate payments to SQL: migrate payments: sql unique constraint violation: constraint failed: UNIQUE constraint failed: payment_htlc_attempts.attempt_index (2067)"
2026-05-09 11:57:41.948 [INF] LTND: Shutdown complete
unable to open databases: faild to run migrations for the native SQL store: error applying migration 'kv_payments_migration' (version 14) to schema version 11: failed to migrate payments to SQL: migrate payments: sql unique constraint violation: constraint failed: UNIQUE constraint failed: payment_htlc_attempts.attempt_index (2067)

I can provide my channel.db file to you if that helps tracking down the issue.

Expected Behavior

Migration should succeed.

Debug Information

No response

Environment

No response