wallet: create bwatch tables before backfilling them
What changed, and why it matters
This commit fixes a database upgrade bug in Core Lightning. Wallets created with version v26.06.10 were missing two new tables (our_outputs and our_txs) because they skipped the migrations that create them. When users later upgraded, a follow-up migration tried to insert data into those non-existent tables and crashed. The fix simply creates the tables if they don't already exist right before filling them, and adds a test that simulates the problematic v26.06.10 database state. It is a reliability/data-integrity fix, not an exploitable security vulnerability.
No immediate security action required. This is a bug-fix commit that should be included in the next release to ensure v26.06.10 users can upgrade without migration failure. Operators who already upgraded successfully are unaffected.
Security signals we found
Database migration failure could prevent node startup after upgrade
Fixes missing table creation before data backfill
Adds regression test for upgrade path from v26.06.10
Evidence from the diff
Migration 285, migrate_backfill_bwatch_tables, backfills our_outputs/our_txs from legacy outputs/transactions tables. Databases at v26.06.10 were left at migration version 284, but migrations 283/284 (which create our_outputs and our_txs) were not present in that release. On upgrade to master, migration 285 runs first and INSERTs into our_outputs, causing a SQLite ‘no such table’ error. The patch adds CREATE TABLE IF NOT EXISTS for both tables at the start of migrate_backfill_bwatch_tables. A unit test constructs a v26.06.10-shaped DB (vars, outputs, transactions, no bwatch tables) and verifies the backfill succeeds.
Changed components
wallet/migrations.cwallet/test/run-migrate_backfill_bwatch_tables.cSQLite wallet database migration layerInspect captured patch +105 / −0
### wallet/migrations.c
@@ -66,6 +66,36 @@ void migrate_backfill_bwatch_tables(struct lightningd *ld UNNEEDED, struct db *d
{
struct db_stmt *stmt;
+ /* v26.06.10 databases are at 284 and never ran the CREATEs at 283/284 */
+ stmt = db_prepare_v2(db,
+ SQL("CREATE TABLE IF NOT EXISTS our_outputs ("
+ " txid BLOB NOT NULL,"
+ " outnum INTEGER NOT NULL,"
+ " blockheight INTEGER NOT NULL,"
+ " txindex INTEGER NOT NULL DEFAULT 0,"
+ " scriptpubkey BLOB NOT NULL,"
+ " satoshis BIGINT NOT NULL,"
+ " spendheight INTEGER,"
+ " keyindex INTEGER,"
+ " reserved_til INTEGER NOT NULL DEFAULT 0,"
+ " channel_dbid BIGINT,"
+ " peer_id BLOB,"
+ " commitment_point BLOB,"
+ " option_anchors INTEGER,"
+ " csv INTEGER,"
+ " PRIMARY KEY (txid, outnum)"
+ ")"));
+ db_exec_prepared_v2(take(stmt));
+
+ stmt = db_prepare_v2(db,
+ SQL("CREATE TABLE IF NOT EXISTS our_txs ("
+ " txid BLOB NOT NULL PRIMARY KEY,"
+ " blockheight INTEGER NOT NULL,"
+ " txindex INTEGER NOT NULL DEFAULT 0,"
+ " rawtx BLOB"
+ ")"));
+ db_exec_prepared_v2(take(stmt));
+
stmt = db_prepare_v2(db,
SQL("INSERT INTO our_outputs "
"(txid, outnum, blockheight, txindex, scriptpubkey, satoshis, "
### wallet/test/run-migrate_backfill_bwatch_tables.c
@@ -271,12 +271,57 @@ static void populate_db(struct db *db)
}
}
+/* A v26.06.10 database at version 284: the bwatch tables were never created. */
+static const char *hotfix_setup_stmts[] = {
+ SQL("CREATE TABLE vars ("
+ " name VARCHAR(32)"
+ ", val VARCHAR(255)"
+ ", PRIMARY KEY (name)"
+ ");"),
+ SQL("ALTER TABLE vars ADD COLUMN intval INTEGER"),
+ SQL("ALTER TABLE vars ADD COLUMN blobval BLOB"),
+ SQL("CREATE TABLE outputs ("
+ " prev_out_tx BLOB"
+ ", prev_out_index INTEGER"
+ ", value BIGINT"
+ ", type INTEGER"
+ ", status INTEGER"
+ ", keyindex INTEGER"
+ ", channel_id BIGINT"
+ ", peer_id BLOB"
+ ", commitment_point BLOB"
+ ", confirmation_height INTEGER"
+ ", spend_height INTEGER"
+ ", scriptpubkey BLOB"
+ ", reserved_til INTEGER DEFAULT NULL"
+ ", option_anchor_outputs INTEGER DEFAULT 0"
+ ", csv_lock INTEGER DEFAULT 1"
+ ", is_in_coinbase INTEGER DEFAULT 0"
+ ", PRIMARY KEY (prev_out_tx, prev_out_index));"),
+ SQL("CREATE TABLE transactions ("
+ " id BLOB"
+ ", blockheight INTEGER"
+ ", txindex INTEGER"
+ ", rawtx BLOB"
+ ", PRIMARY KEY (id));"),
+ SQL("INSERT INTO outputs (prev_out_tx, prev_out_index, value, type, status, keyindex, "
+ " channel_id, peer_id, commitment_point, confirmation_height, spend_height, "
+ " scriptpubkey, reserved_til, option_anchor_outputs, csv_lock, is_in_coinbase) "
+ "VALUES (X'0101010101010101010101010101010101010101010101010101010101010101', 0, "
+ " 1000000, 0, 0, 1, NULL, NULL, NULL, 100, NULL, "
+ " X'0014010101010101010101010101010101010101ff01', NULL, 0, 1, 0);"),
+ SQL("INSERT INTO transactions (id, blockheight, txindex, rawtx) VALUES ("
+ " X'0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a', "
+ " 100, 1, X'deadbeef');"),
+};
+
static void test_error(void *arg, bool fatal, const char *fmt, va_list ap)
{
vfprintf(stderr, fmt, ap);
abort();
}
+
static size_t count_rows(struct db *db, const char *query)
{
struct db_stmt *stmt;
@@ -310,6 +355,34 @@ static struct db_stmt *get_our_output(struct db *db, u8 txid_byte, u32 outnum)
return stmt;
}
+static void test_backfill_without_bwatch_tables(void)
+{
+ char *dsn, *filename;
+ struct db *db;
+ struct db_stmt *stmt;
+
+ close(tmpdir_mkstemp(tmpctx, "ldb-XXXXXX", &filename));
+ dsn = tal_fmt(tmpctx, "sqlite3://%s", filename);
+ db = db_open(tmpctx, dsn, true, true, test_error, NULL);
+ db->report_changes_fn = NULL;
+
+ db_begin_transaction(db);
+ for (size_t i = 0; i < ARRAY_SIZE(hotfix_setup_stmts); i++) {
+ stmt = db_prepare_v2(db, hotfix_setup_stmts[i]);
+ db_exec_prepared_v2(take(stmt));
+ }
+ db->data_version = 0;
+ db_set_intvar(db, "data_version", db->data_version);
+
+ migrate_backfill_bwatch_tables(NULL, db);
+
+ assert(count_rows(db, SQL("SELECT COUNT(*) FROM our_outputs")) == 1);
+ assert(count_rows(db, SQL("SELECT COUNT(*) FROM our_txs")) == 1);
+ db_commit_transaction(db);
+ unlink(filename);
+ tal_free(db);
+}
+
int main(int argc, const char *argv[])
{
char *dsn, *filename;
@@ -320,6 +393,8 @@ int main(int argc, const char *argv[])
close(tmpdir_mkstemp(tmpctx, "ldb-XXXXXX", &filename));
chainparams = chainparams_for_network("bitcoin");
+ test_backfill_without_bwatch_tables();
+
dsn = tal_fmt(tmpctx, "sqlite3://%s", filename);
db = db_open(tmpctx, dsn, true, true, test_error, NULL);
db->report_changes_fn = NULL;Why this scored 27/100
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.