broken database

[gallizoltan]

Issue and Steps to Reproduce

Every time I start lightningd, I see a **BROKEN** database message in the log.
Actually everything works fine, but I worry about that message.

2021-01-23T12:22:20.116Z INFO    plugin-bcli: bitcoin-cli initialized and connected to bitcoind.
2021-01-23T12:22:20.370Z **BROKEN** database: Accessing a null column 9 in query SELECT  id, channel_htlc_id, msatoshi, cltv_expiry, hstate, payment_hash, payment_key, routing_onion, failuremsg, malformed_onion, origin_htlc, shared_secret, received_time, we_filled FROM channel_htlcs WHERE direction= ? AND channel_id= ? AND hstate != ?
2021-01-23T12:22:20.758Z INFO    lightningd: --------------------------------------------------
2021-01-23T12:22:20.758Z INFO    lightningd: Server started with public key 03d67f36c4f81789e2fe425028bacc96b199813eae426c517f589a45f1136c1fe5, alias Jubilee (color #dc42f4) and lightningd 0.9.3

TBH it is not a new thing. I checked my logs, and its first appearance was in December 2019, when I upgraded to v0.8.0. Since then, it is in the log at every start.

getinfo output

{
   "id": "03d67f36c4f81789e2fe425028bacc96b199813eae426c517f589a45f1136c1fe5",
   "alias": "Jubilee",
   "color": "dc42f4",
   "num_peers": 31,
   "num_pending_channels": 0,
   "num_active_channels": 23,
   "num_inactive_channels": 0,
   "address": [
      {
         "type": "ipv4",
         "address": "212.96.34.143",
         "port": 9735
      },
      {
         "type": "torv2",
         "address": "yqygrtvwihl3exb2.onion",
         "port": 9735
      },
      {
         "type": "torv3",
         "address": "ihhsjdncang7ve2rvhuunvy45bxvmh2qwz3vh7mohbouizd36ywnxeid.onion",
         "port": 9735
      }
   ],
   "binding": [
      {
         "type": "ipv6",
         "address": "::",
         "port": 9735
      },
      {
         "type": "ipv4",
         "address": "0.0.0.0",
         "port": 9735
      }
   ],
   "version": "0.9.3",
   "blockheight": 668035,
   "network": "bitcoin",
   "msatoshi_fees_collected": 310625379,
   "fees_collected_msat": "310625379msat",
   "lightning-dir": "/home/zgalli/.lightning/bitcoin"
}

[cdecker]

That error seems to indicate that you have a row with a NULL malformed_onion (field 9 in a 0-indexed array) when we really shouldn't. This is likely a missing db_is_null guard in front of it.

The statement is executed here:

lightning/wallet/wallet.c

Lines 2222 to 2240 in fac8882

	stmt = db_prepare_v2(wallet->db, SQL("SELECT"
	" id"
	", channel_htlc_id"
	", msatoshi"
	", cltv_expiry"
	", hstate"
	", payment_hash"
	", payment_key"
	", routing_onion"
	", failuremsg"
	", malformed_onion"
	", origin_htlc"
	", shared_secret"
	", received_time"
	", we_filled"
	" FROM channel_htlcs"
	" WHERE direction= ?"
	" AND channel_id= ?"
	" AND hstate != ?"));

It is being accessed here:

lightning/wallet/wallet.c

Line 2067 in fac8882

in->badonion = db_column_int(stmt, 9);

All codepaths touching the table set the field, except the insert statement in

lightning/wallet/wallet.c

Lines 1893 to 1905 in fac8882

	SQL("INSERT INTO channel_htlcs ("
	" channel_id,"
	" channel_htlc_id, "
	" direction,"
	" msatoshi,"
	" cltv_expiry,"
	" payment_hash, "
	" payment_key,"
	" hstate,"
	" shared_secret,"
	" routing_onion,"
	" received_time) VALUES "
	"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"));

, which results in the NULL field. So we should just add the NULL-check before accessing the field.

[gallizoltan]

I've just updated to v0.10.0 and everything looks good! There is no more broken database message. Thank you very much! 😎