Lightningd stops with FATAL SIGNAL 6 `UNIQUE constraint failed: network_events.id`

[ShahanaFarooqui]

Reported by: @ElStone on Telegram.

I'm having a real hard time getting my CLN up and running via RaspiBlitz. Any help troubleshooting is appreciated. Here's the final error before breaking (sends twice):

2025-12-17T17:28:39.557Z **BROKEN** lightningd: Error executing statement: wallet/wallet.c:7515: INSERT INTO network_events ( id, peer_id, type,  timestamp, reason, duration_nsec, connect_attempted) VALUES (?, ?, ?, ?, ?, ?, ?);: UNIQUE constraint failed: network_events.id
2025-12-17T17:28:39.557Z **BROKEN** lightningd: Error executing statement: wallet/wallet.c:7515: INSERT INTO network_events ( id, peer_id, type,  timestamp, reason, duration_nsec, connect_attempted) VALUES (?, ?, ?, ?, ?, ?, ?);: UNIQUE constraint failed: network_events.id
2025-12-17T17:28:39.663Z **BROKEN** lightningd: FATAL SIGNAL 6 (version v25.12)
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: common/daemon.c:46 (send_backtrace) 0x5588f89a07
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: common/daemon.c:83 (crashdump) 0x5588f89a5f
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: (null):0 ((null)) 0x7f8158b81f
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: ./nptl/pthread_kill.c:44 (__pthread_kill_implementation) 0x7f81103c18
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: ../sysdeps/posix/raise.c:26 (__GI_raise) 0x7f810ba8ab
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: ./stdlib/abort.c:79 (__GI_abort) 0x7f810a747f
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: lightningd/log.c:1054 (fatal_vfmt) 0x5588ef971f
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: wallet/db.c:95 (db_error) 0x5588f35e53
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: db/utils.c:326 (db_fatal) 0x5588f78863
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: db/utils.c:200 (db_exec_prepared_v2) 0x5588f78cdb
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: wallet/wallet.c:7536 (wallet_save_network_event) 0x5588f49a8f
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: lightningd/connect_control.c:274 (connect_failed) 0x5588ee335b
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: lightningd/connect_control.c:315 (handle_connect_failed) 0x5588ee3753
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: lightningd/connect_control.c:563 (connectd_msg) 0x5588ee3b87
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: lightningd/subd.c:560 (sd_msg_read) 0x5588f2aaab
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: ccan/ccan/io/io.c:60 (next_plan) 0x5588fd087f
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: ccan/ccan/io/io.c:422 (do_plan) 0x5588fd0e5f
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: ccan/ccan/io/io.c:439 (io_ready) 0x5588fd0f3b
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: ccan/ccan/io/poll.c:470 (io_loop) 0x5588fd2f17
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: lightningd/io_loop_with_timers.c:22 (io_loop_with_timers) 0x5588ef08db
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: lightningd/lightningd.c:1492 (main) 0x5588ef698f
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: ../sysdeps/nptl/libc_start_call_main.h:58 (__libc_start_call_main) 0x7f810a7743
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: ../csu/libc-start.c:360 (__libc_start_main_impl) 0x7f810a7817
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: (null):0 ((null)) 0x5588ec30af
2025-12-17T17:28:39.663Z **BROKEN** lightningd: backtrace: (null):0 ((null)) 0xffffffffffffffff

Setup (with current versions):
CLN: v25.12
RASPIBLITZ + TOR
TunnelSats

References:
https://t.me/lightningd/37995
https://t.me/raspiblitz/152827

[ShahanaFarooqui]

Comment by the user after troubleshooting:

"Thank you! This is the cause of your original UNIQUE constraint network_events.id error
—> the value of last_networkevents_created_index in the vars table should be +1 bigger than the max(id) from network_events
"

[endothermicdev]

Might this be related to #8779?

[steepdawn974]

More context on #8800 (comment)

This was the content of the user's sqlite db at the time of the error:

sudo -u bitcoin sqlite3 /home/bitcoin/.lightning/bitcoin/lightningd.sqlite3 "SELECT * FROM vars WHERE name LIKE '%networkevents%';"

----------------------------------------------------------------
last_networkevents_created_index||7588|
last_networkevents_deleted_index||925|

sudo -u bitcoin sqlite3 /home/bitcoin/.lightning/bitcoin/lightningd.sqlite3 "SELECT MAX(id) FROM network_events;"

-----------------------
7588

--> the last_networkevents_created_index should have been +1 w.r.t. max(network_vents.id) , i.e. 7589. That caused the UNIQUE key error.

Workaround:

manually updating the vars value resolved the user's issue

sudo -u bitcoin sqlite3 /home/bitcoin/.lightning/bitcoin/lightningd.sqlite3 "update vars set intval = 7589 where name = 'last_networkevents_created_index';"

Analysis:

I am not super-familiar with CLN's code base, but the incrementing of this in-memory counter must have failed.

lightning/lightningd/wait.c

Lines 148 to 155 in 2bba6f0

	/* FIXME: We can optimize this! It's always the max of the fields in
	* the table, *unless* we delete one. So we can lazily write this on
	* delete, and fix it up to MAX() when we startup. */
	db_set_intvar(db,
	tal_fmt(tmpctx, "last_%s_%s_index",
	wait_subsystem_name(subsystem),
	wait_index_name(index)),
	*idxval);

Thoughts

Why not just make network_events.id a sqlite AUTOINCREMENT column?! Instead of the current fancy in-memory counter handling && saving it (asynchronously) to a separate database table...

There would be an equivilent option for a Postgres backend as well:

CREATE TABLE network_events (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ...
);