UNIQUE constraint failed: utxoset.txid, utxoset.outnum

[sui77]

Issue and Steps to Reproduce

lightningd always dies after "Adding block 515255", guess it's trying to insert some duplicate dataset into sqlite or something? log => https://gist.github.com/sui77/6aace6500b5d3244553b26f2d755a7ea

getinfo output

{ "id" : "0318971cae109776f58101f07a6c1d1e68c4780a05960b5cbfff1620d3c06cb50c", "port" : 9735, "address" :
[
{ "type" : "ipv4", "address" : "148.251.19.39", "port" : 9735 },
{ "type" : "ipv6", "address" : "2a01:4f8:201:8026::2", "port" : 9735 } ], "version" : "v0.5.2-2016-11-21-2368-g489d8d0", "blockheight" : 513764, "network" : "bitcoin" }

[cdecker]

That shouldn't happen. Can you share the outputs of this in a gist:

sqlite3 $HOME/.lightning/lightningd.sqlite3 "SELECT hex(txid), outnum, blockheight, spendheight FROM utxoset"

Hopefully that tells us if there is a real duplicate and where it came from.

[sui77]

https://gist.github.com/sui77/81999a79fbfef30e8b6b83227ce40ab3

[danielalexiuc]

I'm getting the same thing today, can provide the same dump if it helps.

[cdecker]

@danielalexiuc are you getting the error at the same blockchain height?

[sui77]

I wanted to add some log_debug()'s to log the affected txid but I've no experience in C. Can you advice? https://gist.github.com/sui77/6f8dd646d6d14d301aeb3dcf60b17767

Anyways, at least I logged the amount so I can assume it's failing with this tx: https://blockchain.info/de/tx/466b8ee63d1f150088f4eb5ee534e9528d6ec4bb9ad3dcad9d4c3cfff1deebb5 but that txid is not in my lightningd.sqlite database...

2018-03-27T13:31:21.573Z lightningd(21843): Adding block 515256: 0000000000000000003cf0db073748440360c56367a6376f5e211a7625dfb12a
2018-03-27T13:31:21.574Z lightningd(21843): j: 1
2018-03-27T13:31:21.574Z lightningd(21843): height: 515256
2018-03-27T13:31:21.574Z lightningd(21843): i: 37
2018-03-27T13:31:21.574Z lightningd(21843): script:
2018-03-27T13:31:21.574Z lightningd(21843): amount: 936736
2018-03-27T13:31:21.574Z lightningd(21843): wallet_utxoset_add: UNIQUE constraint failed: utxoset.txid, utxoset.outnum
2018-03-27T13:31:21.574Z lightningd(21843): FATAL SIGNAL 6 RECEIVED

[cdecker]

That's excellent work @sui77, it appears that when adding tx outppint 515256:37:1, it fails since the DB already contains that outpoint. The tx hash is 466b8ee63d1f150088f4eb5ee534e9528d6ec4bb9ad3dcad9d4c3cfff1deebb5, but in the sqlite3 output you need to look for b5ebdef1ff3c4c9daddcd39abbc46e8d52e934e55eebf48800151f3de68e6b46 since we store hashes in their non-inverted form.

Is this the first block that is being added or does it crash later in the process? We are using foreign key constraints to cascade block deletions to outpoints when rewinding the blocks:

https://github.com/ElementsProject/lightning/blob/master/wallet/db.c#L220-L228

What version of sqlite3 are you compiling against?

[sui77]

sqlite3 $HOME/.lightning/lightningd.sqlite3 "SELECT hex(txid), txindex, satoshis, outnum, blockheight, spendheight FROM utxoset where hex(txid) like 'B5EB%'"
B5EBDEF1FF3C4C9DADDCD39ABBC46E8D52E934E55EEBF48800151F3DE68E6B46|27|936736|1|515256|

same amount but txid=27 instead of 37 as in the logs. if i'm not wrong the 27th tx in that block is b67efb33a464482f09b9f8081cfbd8b08c13af8ba6d6a461ed700b280c2ee96e (how do i figure out the other doubleSHAwhatever form?) - looks quite impossible to have that row in the db :)

What version of sqlite3 are you compiling against?
libsqlite3-dev is already the newest version (3.11.0-1ubuntu1). (?)

[sui77]

I think all txs with height 515256 (https://gist.github.com/sui77/81999a79fbfef30e8b6b83227ce40ab3#file-lightningsql-L3024-L3057) were somehow saved wrong, deleting them all fixed it. Thanks for helping @cdecker 👍

[cdecker]

Happy it worked, but I'd still like to understand why that happened, especially because @danielalexiuc reported the same issue.

[cdecker]

For context: I think this may be built against a sqlite3 version prior to 3.6.9 which introduced foreign key support including the support for cascade, or the library was compiled without foreign key support (very unlikely I think).

[danielalexiuc]

@cdecker Yes, at the same height:

https://gist.github.com/danielalexiuc/2ec39b7f5f88f74fd2af54d41a342f2e