plugin-sql BROKEN

[Lagrang3]

Running v26.04rc2, after paying an invoice in Zeus wallet plugin-sql fails.

2026-04-02T10:29:45.779Z DEBUG   plugin-sql: sendpays: records created, inserting from 1
2026-04-02T10:29:45.779Z TRACE   lightningd: waiting on sendpays created 6074
2026-04-02T10:29:45.779Z TRACE   lightningd: waiting on sendpays updated 6074
2026-04-02T10:29:45.779Z TRACE   lightningd: waiting on sendpays deleted 1
2026-04-02T10:29:45.932Z DEBUG   plugin-sql: Time to call listsendpays: 0.098392951 seconds
2026-04-02T10:29:45.932Z DEBUG   plugin-sql: Time to refresh sendpays: 0.137310484 seconds (last=6073)
2026-04-02T10:29:45.932Z DEBUG   plugin-sql: Time to refresh + create indices for sendpays: 0.140120043 seconds
2026-04-02T10:29:46.373Z INFO    plugin-clnrest: Authorized rune_id:`0` access to method: `listfunds`, params: `{}`
2026-04-02T10:29:53.954Z TRACE   gossipd: Too-old update for 923888x1305x0
2026-04-02T10:30:10.537Z INFO    plugin-clnrest: Authorized rune_id:`0` access to method: `sql`, params: `{"query":"select sp.payment_hash, sp.groupid, min(sp.status) as status, min(sp.destination) as destination, min(sp.created_at) as created_at, min(sp.description) as description, min(sp.bolt11) as bolt11, min(sp.bolt12) as bolt12, sum(case when sp.status = 'complete' then sp.amount_sent_msat else null end) as amount_sent_msat, sum(case when sp.status = 'complete' then sp.amount_msat else 0 end) as amount_msat, max(sp.payment_preimage) as preimage from sendpays sp group by sp.payment_hash, sp.groupid order by created_index desc limit 150"}`
2026-04-02T10:30:11.850Z INFO    plugin-clnrest: Authorized rune_id:`0` access to method: `getinfo`, params: `{}`
2026-04-02T10:30:11.850Z INFO    plugin-clnrest: Authorized rune_id:`0` access to method: `listtransactions`, params: `{}`
2026-04-02T10:30:11.851Z INFO    plugin-clnrest: Authorized rune_id:`0` access to method: `sql`, params: `{"query":"SELECT account, tag, outpoint, credit_msat, debit_msat, timestamp, blockheight FROM bkpr_accountevents WHERE (tag='deposit' OR tag='to_them' OR tag='channel_open' OR tag='channel_close') ORDER BY timestamp DESC LIMIT 150"}`
2026-04-02T10:30:11.863Z TRACE   lightningd: waiting on channelmoves created 11985
2026-04-02T10:30:11.863Z TRACE   lightningd: waiting on channelmoves updated 1
2026-04-02T10:30:11.863Z TRACE   lightningd: waiting on channelmoves deleted 1
2026-04-02T10:30:12.381Z INFO    plugin-sql: Killing plugin: exited during normal operation
2026-04-02T10:30:12.381Z **BROKEN** plugin-sql: Plugin marked as important, shutting down lightningd!

[Lagrang3]

Another way to reproduce:

1. upgrade from 25.12.1
2. execute

$ lightning-cli -k sql query="SELECT account, tag, outpoint, credit_msat, debit_msat, timestamp, blockheight FROM bkpr_accountevents WHERE (tag='deposit' OR tag='to_them' OR tag='channel_open' OR tag='channel_close') ORDER BY timestamp DESC LIMIT 150"
{
   "code": -4,
   "message": "Plugin terminated before replying to RPC call."
}

[Lagrang3]

The stack trace

sql: plugins/sql.c:1749: refresh_by_created_index: Assertion `td->refresh_needs != REFRESH_UNNECESSARY' failed.
sql: FATAL SIGNAL 6 (version v26.04rc2)
0x5618e147892e send_backtrace
	common/daemon.c:38
0x5618e14789bb crashdump
	common/daemon.c:83
0x7f54d10ea04f ???
	./signal/../sysdeps/unix/sysv/linux/x86_64/libc_sigaction.c:0
0x7f54d1138eec __pthread_kill_implementation
	./nptl/pthread_kill.c:44
0x7f54d10e9fb1 __GI_raise
	../sysdeps/posix/raise.c:26
0x7f54d10d4471 __GI_abort
	./stdlib/abort.c:79
0x7f54d10d4394 __assert_fail_base
	./assert/assert.c:94
0x7f54d10e2ec1 __GI___assert_fail
	./assert/assert.c:103
0x5618e1472725 refresh_by_created_index
	plugins/sql.c:1749
0x5618e14736af one_refresh_done
	plugins/sql.c:579
0x5618e1473932 limited_list_done
	plugins/sql.c:1738
0x5618e1477418 handle_rpc_reply
	plugins/libplugin.c:1093
0x5618e1477548 rpc_conn_read_response
	plugins/libplugin.c:1398
0x5618e147ec71 next_plan
	ccan/ccan/io/io.c:60
0x5618e147ef90 do_plan
	ccan/ccan/io/io.c:422
0x5618e147f049 io_ready
	ccan/ccan/io/io.c:439
0x5618e147ffae io_loop
	ccan/ccan/io/poll.c:470
0x5618e14786af plugin_main
	plugins/libplugin.c:2461
0x5618e1474b12 main
	plugins/sql.c:2219
0x7f54d10d5249 __libc_start_call_main
	../sysdeps/nptl/libc_start_call_main.h:58
0x7f54d10d5304 __libc_start_main_impl
	../csu/libc-start.c:360
0x5618e1470710 ???
	_start+0x20:0
0xffffffffffffffff ???
	???:0

[Lagrang3]

I've noticed that under the hood, the previous command actually makes an RPC call to bkpr-listaccountevents and that one fails
inmediately:

$ lightning-cli bkpr-listaccountevents 
lightning-cli: reading response: socket closed

stack

sql: plugins/sql.c:1749: refresh_by_created_index: Assertion `td->refresh_needs != REFRESH_UNNECESSARY' failed.
sql: FATAL SIGNAL 6 (version v26.04rc2-modded)
0x55ca0c0f63e9 send_backtrace
	common/daemon.c:38
0x55ca0c0f649f crashdump
	common/daemon.c:83
0x7f0a8782304f ???
	./signal/../sysdeps/unix/sysv/linux/x86_64/libc_sigaction.c:0
0x7f0a87871eec __pthread_kill_implementation
	./nptl/pthread_kill.c:44
0x7f0a87822fb1 __GI_raise
	../sysdeps/posix/raise.c:26
0x7f0a8780d471 __GI_abort
	./stdlib/abort.c:79
0x7f0a8780d394 __assert_fail_base
	./assert/assert.c:94
0x7f0a8781bec1 __GI___assert_fail
	./assert/assert.c:103
0x55ca0c0ef141 refresh_by_created_index
	plugins/sql.c:1749
0x55ca0c0ebd28 one_refresh_done
	plugins/sql.c:579
0x55ca0c0ef0e4 limited_list_done
	plugins/sql.c:1738
0x55ca0c0f26b8 handle_rpc_reply
	plugins/libplugin.c:1093
0x55ca0c0f3590 rpc_conn_read_response
	plugins/libplugin.c:1398
0x55ca0c1012c7 next_plan
	ccan/ccan/io/io.c:60
0x55ca0c101a94 do_plan
	ccan/ccan/io/io.c:422
0x55ca0c101af7 io_ready
	ccan/ccan/io/io.c:439
0x55ca0c10311b io_loop
	ccan/ccan/io/poll.c:470
0x55ca0c0f616b plugin_main
	plugins/libplugin.c:2461
0x55ca0c0f08f4 main
	plugins/sql.c:2219
0x7f0a8780e249 __libc_start_call_main
	../sysdeps/nptl/libc_start_call_main.h:58
0x7f0a8780e304 __libc_start_main_impl
	../csu/libc-start.c:360
0x55ca0c0ea710 ???
	_start+0x20:0
0xffffffffffffffff ???
	???:0
Got error result to sql: '{"jsonrpc": "2.0","id": "sql/bookkeeper:init/#13","error": {"code":-4, "message":"Plugin terminated before replying to RPC call."}}'

Which in turn calls internally another RPC listchannelmoves.
Maybe bkpr-listaccountevents fail because there are 11k entries in listchannelmoves:

$ lightning-cli listchannelmoves | jq '.channelmoves | length'
11984

bkpr-listaccountevents does not make lightningd crash in v26.04rc1, I suppose this PR #8914 introduced the bug somehow

[Lagrang3]

The assertion is triggered if a table that refreshes by refresh_by_created_index at a certain point needs refreshing (more likely at init rather than update) and the items returned by the RPC are limited by LIMIT_PER_LIST. In that case one_refresh_done will be called with was_limited = true and try to call refresh_by_created_index a second time.