Lightning: Funding Tx not broadcasted and unable to withdraw

Created on 28 Jan 2019 · 8Comments · Source: ElementsProject/lightning

Issue and Steps to Reproduce

Running the following sql command, finds 6 funding tx not broadcasted:
sqlite3 $HOME/.lightning/lightningd.sqlite3 "SELECT count(1) FROM transactions WHERE blockheight is null;"

When trying to broadcast them again using the hex(rawtx) I get the error "missing inputs" on all of them.
Same when trying to withdraw.
Example of a funding tx not broadcasted: 9c420a9ad6f3633aa3f72fafcd1f5f5d99b7c5f2d281c8faa57dd61d21a21c60

Unfortunately I also have a mixed situation in the dabase with both testnet and mainnet tx.
I'm now taking into account only mainnet tx.

Not sure if possible to recover from such situation... any help is appreciated.

`listpeers` output

{
  "peers": [
    {
      "id": "02d28c3aac4b4f36746052a735831afbe65bc5698a7be5bd41b42fd1ddf2a1a358", 
      "connected": false, 
      "channels": [
        {
          "state": "AWAITING_UNILATERAL", 
          "scratch_txid": "1e0e5efc53b28295331adf476473dea7ab3f69469063dd766ea8ee30742e06f4", 
          "short_channel_id": "1261498:30:0", 
          "channel_id": "0d64321a1c926f51ab91834b3bb3d1a42a5ea234722b36da28c796dab6479ebd", 
          "funding_txid": "bd9e47b6da96c728da362b7234a25e2aa4d1b33b4b8391ab516f921c1a32640d", 
          "private": false, 
          "msatoshi_to_us": 9900000, 
          "msatoshi_to_us_min": 9900000, 
          "msatoshi_to_us_max": 9900000, 
          "msatoshi_total": 10000000, 
          "dust_limit_satoshis": 546, 
          "max_htlc_value_in_flight_msat": 18446744073709551615, 
          "their_channel_reserve_satoshis": 0, 
          "our_channel_reserve_satoshis": 100, 
          "spendable_msatoshi": 9800000, 
          "htlc_minimum_msat": 0, 
          "their_to_self_delay": 6, 
          "our_to_self_delay": 4, 
          "max_accepted_htlcs": 483, 
          "status": [
            "AWAITING_UNILATERAL:Attempting to reconnect"
          ], 
          "in_payments_offered": 0, 
          "in_msatoshi_offered": 0, 
          "in_payments_fulfilled": 0, 
          "in_msatoshi_fulfilled": 0, 
          "out_payments_offered": 0, 
          "out_msatoshi_offered": 0, 
          "out_payments_fulfilled": 0, 
          "out_msatoshi_fulfilled": 0, 
          "htlcs": [
          ]
        }
      ]
    }, 
    {
      "id": "0260a9711694672b4fd55add53def7cdbd18d767f455b052e0ff63e651548c6c56", 
      "connected": false, 
      "channels": [
        {
          "state": "CHANNELD_NORMAL", 
          "scratch_txid": "4347b900ee41d301a51e544d9b41162d79e28013306b009a229c218f2874c61c", 
          "short_channel_id": "1261503:25:0", 
          "channel_id": "f5b8fa2c373aaa8b71edb60a82c2c01797441ee5fb943dd0cf89fbab70f211e1", 
          "funding_txid": "e111f270abfb89cfd03d94fbe51e449717c0c2820ab6ed718baa3a372cfab8f5", 
          "private": false, 
          "msatoshi_to_us": 200000000, 
          "msatoshi_to_us_min": 200000000, 
          "msatoshi_to_us_max": 200000000, 
          "msatoshi_total": 200000000, 
          "dust_limit_satoshis": 546, 
          "max_htlc_value_in_flight_msat": 18446744073709551615, 
          "their_channel_reserve_satoshis": 0, 
          "our_channel_reserve_satoshis": 2000, 
          "spendable_msatoshi": 198000000, 
          "htlc_minimum_msat": 0, 
          "their_to_self_delay": 6, 
          "our_to_self_delay": 144, 
          "max_accepted_htlcs": 483, 
          "status": [
            "CHANNELD_NORMAL:Attempting to reconnect"
          ], 
          "in_payments_offered": 0, 
          "in_msatoshi_offered": 0, 
          "in_payments_fulfilled": 0, 
          "in_msatoshi_fulfilled": 0, 
          "out_payments_offered": 0, 
          "out_msatoshi_offered": 0, 
          "out_payments_fulfilled": 0, 
          "out_msatoshi_fulfilled": 0, 
          "htlcs": [
          ]
        }
      ]
    }, 
    {
      "id": "02f057867392ba8e9fe2b62b8a21ee36be00a3338d5d34cb1a2ffab3d57d8a2aea", 
      "connected": false, 
      "channels": [
        {
          "state": "CHANNELD_NORMAL", 
          "scratch_txid": "1ba68d867367d9458ed73773d0aa22e6138bc7c26f70d5359107e60622bd8a59", 
          "short_channel_id": "1261692:327:0", 
          "channel_id": "16102989b80d40586e1052802afc2bac4136ed90fad495dbb328e3499c12f873", 
          "funding_txid": "73f8129c49e328b3db95d4fa90ed3641ac2bfc2a8052106e58400db889291016", 
          "private": false, 
          "msatoshi_to_us": 2000000000, 
          "msatoshi_to_us_min": 2000000000, 
          "msatoshi_to_us_max": 2000000000, 
          "msatoshi_total": 2000000000, 
          "dust_limit_satoshis": 546, 
          "max_htlc_value_in_flight_msat": 18446744073709551615, 
          "their_channel_reserve_satoshis": 0, 
          "our_channel_reserve_satoshis": 20000, 
          "spendable_msatoshi": 1980000000, 
          "htlc_minimum_msat": 0, 
          "their_to_self_delay": 6, 
          "our_to_self_delay": 240, 
          "max_accepted_htlcs": 483, 
          "status": [
            "CHANNELD_NORMAL:Attempting to reconnect"
          ], 
          "in_payments_offered": 0, 
          "in_msatoshi_offered": 0, 
          "in_payments_fulfilled": 0, 
          "in_msatoshi_fulfilled": 0, 
          "out_payments_offered": 0, 
          "out_msatoshi_offered": 0, 
          "out_payments_fulfilled": 0, 
          "out_msatoshi_fulfilled": 0, 
          "htlcs": [
          ]
        }
      ]
    }, 
    {
      "id": "03f113414ebdc6c1fb0f33c99cd5a1d09dd79e7fdf2468cf1fe1af6674361695d2", 
      "connected": false, 
      "channels": [
        {
          "state": "CHANNELD_NORMAL", 
          "scratch_txid": "a449bd596ac546eb381492962754ffda32b715b6138174cbdc7cf0af6878529b", 
          "short_channel_id": "1261692:328:0", 
          "channel_id": "d016c120e8e97e2e89f2a8c7a10502b647d1b0c14dbaaef6ebb6760950ad4fe3", 
          "funding_txid": "e34fad500976b6ebf6aeba4dc1b0d147b60205a1c7a8f2892e7ee9e820c116d0", 
          "private": false, 
          "msatoshi_to_us": 300000000, 
          "msatoshi_to_us_min": 300000000, 
          "msatoshi_to_us_max": 300000000, 
          "msatoshi_total": 300000000, 
          "dust_limit_satoshis": 546, 
          "max_htlc_value_in_flight_msat": 18446744073709551615, 
          "their_channel_reserve_satoshis": 0, 
          "our_channel_reserve_satoshis": 3000, 
          "spendable_msatoshi": 297000000, 
          "htlc_minimum_msat": 0, 
          "their_to_self_delay": 6, 
          "our_to_self_delay": 144, 
          "max_accepted_htlcs": 483, 
          "status": [
            "CHANNELD_NORMAL:Attempting to reconnect"
          ], 
          "in_payments_offered": 0, 
          "in_msatoshi_offered": 0, 
          "in_payments_fulfilled": 0, 
          "in_msatoshi_fulfilled": 0, 
          "out_payments_offered": 0, 
          "out_msatoshi_offered": 0, 
          "out_payments_fulfilled": 0, 
          "out_msatoshi_fulfilled": 0, 
          "htlcs": [
          ]
        }
      ]
    }, 
    {
      "id": "02f6725f9c1c40333b67faea92fd211c183050f28df32cac3f9d69685fe9665432", 
      "connected": true, 
      "netaddr": [
        "104.198.32.198:9735"
      ], 
      "global_features": "", 
      "local_features": "88", 
      "globalfeatures": "", 
      "localfeatures": "88", 
      "channels": [
        {
          "state": "AWAITING_UNILATERAL", 
          "scratch_txid": "9ecc3656db49fd4aa3df4b3edb2995e0627a798e8800994d26a8b472ed58a97d", 
          "channel_id": "601ca2211dd67da5fac881d2f2c5b7995d5f1fcdaf2ff7a33a63f3d69a0a429c", 
          "funding_txid": "9c420a9ad6f3633aa3f72fafcd1f5f5d99b7c5f2d281c8faa57dd61d21a21c60", 
          "private": false, 
          "msatoshi_to_us": 500000000, 
          "msatoshi_to_us_min": 500000000, 
          "msatoshi_to_us_max": 500000000, 
          "msatoshi_total": 500000000, 
          "dust_limit_satoshis": 546, 
          "max_htlc_value_in_flight_msat": 18446744073709551615, 
          "their_channel_reserve_satoshis": 0, 
          "our_channel_reserve_satoshis": 5000, 
          "spendable_msatoshi": 495000000, 
          "htlc_minimum_msat": 0, 
          "their_to_self_delay": 144, 
          "our_to_self_delay": 144, 
          "max_accepted_htlcs": 483, 
          "status": [
            "AWAITING_UNILATERAL:Attempting to reconnect"
          ], 
          "in_payments_offered": 0, 
          "in_msatoshi_offered": 0, 
          "in_payments_fulfilled": 0, 
          "in_msatoshi_fulfilled": 0, 
          "out_payments_offered": 0, 
          "out_msatoshi_offered": 0, 
          "out_payments_fulfilled": 0, 
          "out_msatoshi_fulfilled": 0, 
          "htlcs": [
          ]
        }
      ]
    }, 
    {
      "id": "0217890e3aad8d35bc054f43acc00084b25229ecff0ab68debd82883ad65ee8266", 
      "connected": true, 
      "netaddr": [
        "23.237.77.11:9735"
      ], 
      "global_features": "", 
      "local_features": "82", 
      "globalfeatures": "", 
      "localfeatures": "82", 
      "channels": [
        {
          "state": "CHANNELD_NORMAL", 
          "scratch_txid": "cc3e39dad21e1a2e611e2f3e0bcdf19ff44cc91d2096412bf2a13594af08f5e1", 
          "owner": "lightning_channeld", 
          "short_channel_id": "542582:1404:0", 
          "channel_id": "5e61be57fe8ce61b1e21480080b125c346a7150c48e9c0c128a07eb0ce79ed91", 
          "funding_txid": "91ed79ceb07ea028c1c0e9480c15a746c325b1800048211e1be68cfe57be615e", 
          "private": false, 
          "msatoshi_to_us": 50000000, 
          "msatoshi_to_us_min": 50000000, 
          "msatoshi_to_us_max": 50000000, 
          "msatoshi_total": 50000000, 
          "dust_limit_satoshis": 546, 
          "max_htlc_value_in_flight_msat": 18446744073709551615, 
          "their_channel_reserve_satoshis": 546, 
          "our_channel_reserve_satoshis": 546, 
          "spendable_msatoshi": 49454000, 
          "htlc_minimum_msat": 0, 
          "their_to_self_delay": 144, 
          "our_to_self_delay": 144, 
          "max_accepted_htlcs": 483, 
          "status": [
            "CHANNELD_NORMAL:Reconnected, and reestablished.", 
            "CHANNELD_NORMAL:Funding transaction locked. Waiting for their announcement signatures."
          ], 
          "in_payments_offered": 0, 
          "in_msatoshi_offered": 0, 
          "in_payments_fulfilled": 0, 
          "in_msatoshi_fulfilled": 0, 
          "out_payments_offered": 0, 
          "out_msatoshi_offered": 0, 
          "out_payments_fulfilled": 0, 
          "out_msatoshi_fulfilled": 0, 
          "htlcs": [
          ]
        }
      ]
    }, 
    {
      "id": "0279c22ed7a068d10dc1a38ae66d2d6461e269226c60258c021b1ddcdfe4b00bc4", 
      "connected": false, 
      "channels": [
        {
          "state": "AWAITING_UNILATERAL", 
          "scratch_txid": "0cec0a9e9322f728253c14c69d7d1a481daac7a3176ffbc9d3df38f7099c353f", 
          "channel_id": "79608e0c4f9ad6b7411e1727cea8b2de9b4121db26170a6eaa40197776db8673", 
          "funding_txid": "7386db76771940aa6e0a1726db21419bdeb2a8ce27171e41b7d69a4f0c8e6079", 
          "private": false, 
          "msatoshi_to_us": 100000000, 
          "msatoshi_to_us_min": 100000000, 
          "msatoshi_to_us_max": 100000000, 
          "msatoshi_total": 100000000, 
          "dust_limit_satoshis": 546, 
          "max_htlc_value_in_flight_msat": 18446744073709551615, 
          "their_channel_reserve_satoshis": 1000, 
          "our_channel_reserve_satoshis": 1000, 
          "spendable_msatoshi": 99000000, 
          "htlc_minimum_msat": 0, 
          "their_to_self_delay": 144, 
          "our_to_self_delay": 144, 
          "max_accepted_htlcs": 483, 
          "status": [
            "AWAITING_UNILATERAL:Attempting to reconnect"
          ], 
          "in_payments_offered": 0, 
          "in_msatoshi_offered": 0, 
          "in_payments_fulfilled": 0, 
          "in_msatoshi_fulfilled": 0, 
          "out_payments_offered": 0, 
          "out_msatoshi_offered": 0, 
          "out_payments_fulfilled": 0, 
          "out_msatoshi_fulfilled": 0, 
          "htlcs": [
          ]
        }
      ]
    }, 
    {
      "id": "0320e5b9f19a1c4dc22ebb751d757eaa5843262cd55a0f1b0d13cb5d445c5c78ae", 
      "connected": false, 
      "channels": [
        {
          "state": "AWAITING_UNILATERAL", 
          "scratch_txid": "6b39947f7931d23518026febf9c6b93af5cd2ef343173d7ee2b22c8fbe8f5c12", 
          "channel_id": "39a191d0ac2f84e5794a0fcd8699a1e4444475e8ceaaa43280c46fc8f03014f6", 
          "funding_txid": "f61430f0c86fc48032a4aacee8754444e4a19986cd0f4a79e5842facd091a139", 
          "private": false, 
          "msatoshi_to_us": 3000000000, 
          "msatoshi_to_us_min": 3000000000, 
          "msatoshi_to_us_max": 3000000000, 
          "msatoshi_total": 3000000000, 
          "dust_limit_satoshis": 546, 
          "max_htlc_value_in_flight_msat": 18446744073709551615, 
          "their_channel_reserve_satoshis": 30000, 
          "our_channel_reserve_satoshis": 30000, 
          "spendable_msatoshi": 2970000000, 
          "htlc_minimum_msat": 0, 
          "their_to_self_delay": 144, 
          "our_to_self_delay": 360, 
          "max_accepted_htlcs": 483, 
          "status": [
            "AWAITING_UNILATERAL:Attempting to reconnect"
          ], 
          "in_payments_offered": 0, 
          "in_msatoshi_offered": 0, 
          "in_payments_fulfilled": 0, 
          "in_msatoshi_fulfilled": 0, 
          "out_payments_offered": 0, 
          "out_msatoshi_offered": 0, 
          "out_payments_fulfilled": 0, 
          "out_msatoshi_fulfilled": 0, 
          "htlcs": [
          ]
        }
      ]
    },

`getinfo` output

{
  "id": "02af4ac85e83a5c6a7213090f9518e7054901e84746e10e473042ea1c230f2957e", 
  "num_peers": 11, 
  "num_pending_channels": 4, 
  "num_active_channels": 8, 
  "num_inactive_channels": 0, 
  "binding": [
    {
      "type": "ipv4", 
      "address": "0.0.0.0", 
      "port": 9735
    }
  ], 
  "version": "v0.6.3", 
  "blockheight": 560525, 
  "network": "bitcoin", 
 }

funding wallet

Source

mloop1

Most helpful comment

EDIT: ah I see @cdecker gave a response, consider this redundant

Maybe this helps: listfunds shows two types of funds:

wallet outputs we _own_, like deposits, change_outputs and outputs from close/penalty tx
funds in channels (shared ownership)

So when you withdraw, you spent wallet outputs. Note also that the output status _unconfirmed_ can mean that the tx it belongs too is in the mempool waiting for confirmation.

Hmm, actually ... after a withdraw and after confirmation ... listfunds still shows that change output as unconfirmed. That looks like a bug, function json_withdraw seems to be missing some lines similar to these:
https://github.com/ElementsProject/lightning/blob/0da40540453712b836b2a2a5383c8625137964c7/wallet/walletrpc.c#L273-L283

SimonVrouwe on 31 Jan 2019

👍2

All 8 comments

This has been addressed going forward with PR #2274. In order to recover your coins I suggest updating to the latest master commit, re-compile with ./configure --enable-developer and then perform a lightning-cli dev-rescan-outputs. That should get you your outputs back in sync with bitcoind. As for the channels I'm afraid they will never confirm (since the funding transaction will not confirm).

I'd make sure that they don't confirm by withdrawing the funds to an external wallet (or even the internal lightningd wallet could work). Once the funds have arrived in your external wallets we can check together which channels still stand a chance of being confirmed (and should be kept) and which channels can be forgotten using dev-forget-channel.

cdecker on 30 Jan 2019

Hi Christian, thanks a lot.
After re-compile and rescan I was indeed able to withdraw some funds.

I think I'm still confused with the concepts of "outputs" (whose value after withdraw is now almost zero when running listfunds) and satoshi in channels (which apparently didn't change after the withdrawal).
Any resource that explains this?

Let me know how can we progress from here in order to determine which channels should be kept and which forgotten.

mloop1 on 30 Jan 2019

Basically outputs are on-chain funds, that have not been allocated to a channel (yet), and can be stocked up by transferring funds to an address created using newaddr, and can be spent using withdraw. channels are to off-chain counterpart, meaning funds that are allocated to channels, and therefore to spend those funds you'll have to close the channels first (transitioning those funds to the outputs section again, now being on-chain funds) and then transferring them away.

It is worth noting however that your wallet is now a bit out of sync, and the channels that you see as AWAITING_UNILATERAL are likely double counting funds that are also in outputs after the dev-rescan-outputs since that marked them as not used in a channel, while still leaving the channel stub around.

Now with a combination of the transactions in the transactions table in the DB we can go and see which outputs where spent by funding transactions of the stuck channels, and which we moved away, and if the outputs were successfully double-spent with the withdraw we can dev-forget-channel the stuck channels :wink:

cdecker on 31 Jan 2019

👍1

EDIT: ah I see @cdecker gave a response, consider this redundant

Maybe this helps: listfunds shows two types of funds:

wallet outputs we _own_, like deposits, change_outputs and outputs from close/penalty tx
funds in channels (shared ownership)

So when you withdraw, you spent wallet outputs. Note also that the output status _unconfirmed_ can mean that the tx it belongs too is in the mempool waiting for confirmation.

SimonVrouwe on 31 Jan 2019

👍2

Thanks for your explanations, they really clarify things.

@SimonVrouwe indeed running listfunds I still see the status unconfimed.

@cdecker indeed the channels that are in AWAITING_UNILATERAL are double counting funds that are also in outputs.

I've now run the following queries, I still need a bit of direction to understand how to determine which outputs where spent by funding transactions of the stuck channels, and which we moved away.

 "SELECT hex(id), hex(rawtx), blockheight, txindex from transactions;"
7DA958ED72B4A8264D9900888E797A62E09529DB3E4BDFA34AFD49DB5636CC9E|02000000000101601CA2211DD67DA5FAC881D2F2C5B7995D5F1FCDAF2FF7A33A63F3D69A0A429C000000000054DBCD8001819E070000000000220020A1D1E39AE5E7EDD2F321EB2805093A9B91F0D129EE7874CE9674F30FB33308400400483045022100F161529529C46B6DE29E5CCA5F90D5E0BE9139FFF5DE5013AED731E240727B3102207BDD6EF8D6FAC6730459076A08B92C4F5A5065ABC22268EF8D812FFE9DAA1E5F01483045022100EACED410E0BAB8E17AEBCBFBF6BB0B33341308A004ED3882E73948701FF5B0C902206F8210370522C4E12807BADD341E2B8C30BE3879A5183F71FD34DEEF49EE0390014752210222E268754A5EFAF7BBC759FAF892C4034F195275A4F4E162C2F284F480589F6D21022ABB6221F49CBA378BFED961E7DA53F04FA266467E1C92211F31EC8FDEFD80D452AEF5052A20||
5E61BE57FE8CE61B1E21480080B125C346A7150C48E9C0C128A07EB0CE79ED91|020000000001011EECBB25763C5F2116043C4E016A316210EBA455103825385AD39F52A1B32DD900000000171600147BE6E83555D3BA30E23CD7E6706F5A4A83177949FFFFFFFF0250C300000000000022002058A5BF240AE6A3A2F01050C92734A677E9E940E6BCC798C0759419D0BFA5A744157D0E0000000000160014ED1F3C870BA371728D7332B8787AB86DFC59D6530247304402200510690F950043957A702815A7F5E73F4BAFE75C4921B27E2661101206247C8602201A1E93C5529593A47FC27A9C7C6A2ED3CA26F326F03B5FA5B2DB46A630C011730121031DDF0F923186DD51BB7D4CEA77FABF69B333AEFB60EE4C19F01A78E378446DCC00000000|542582|1404
79608E0C4F9AD6B7411E1727CEA8B2DE9B4121DB26170A6EAA40197776DB8673|02000000000101601CA2211DD67DA5FAC881D2F2C5B7995D5F1FCDAF2FF7A33A63F3D69A0A429C0100000000FFFFFFFF02A086010000000000220020448E38491737AF3C63183CC501C395C159FB0B930C72ADD53A6A66697B77663CEE2990070000000016001435A296924816E37685EE52C3935451DC0E3AE2C102483045022100EDED72AE9E681C6C41216CD4136A05C303DCA57A503957DBA01848252F6CDD6202206C5763F16E44189E6150BAEB984541140F8E1D463BBE26CA7452028083FCEAF4012103CE4B162764A152E36B33CB9A011A02886A4D4E1E1FD1CA615DB0B95C9886BACF00000000||
3F359C09F738DFD3C9FB6F17A3C7AA1D481A7D9DC6143C2528F722939E0AEC0C|0200000000010179608E0C4F9AD6B7411E1727CEA8B2DE9B4121DB26170A6EAA40197776DB86730000000000223A9D8001E985010000000000220020E783D7B69398440C1036D107F279EEBD16C9FBB0176516430FA63B7834BD182E0400473044022017BED00D7614D118360BBD31A1687EDDE660FCEB459D6DA0B3A5A2604DC9E07002204347F9A3A32946C84BE138851E23582209AAFC4A110D39AA5F57A0E64FBD36270147304402207E038FA249B4739912B8836465904A90C0BFF16F05EC5D92DC422D5614100C55022033C11E8AA55984B3F0989B514CAAAD215914C826EF8123F97FF9BF941FAF79EE0147522103C2C119EF2A2AFD0AF0A107AC454A93B71F387242F310927734ADD8F26041D5A62103E65D17DC8D7CF49961EC4912AB02C6093117F7A240873654D52879F78FE70D9352AE45CFF220||
F4062E7430EEA86E76DD639046693FABA7DE736447DF1A339582B253FC5E0E1E|020000000001010D64321A1C926F51AB91834B3BB3D1A42A5EA234722B36DA28C796DAB6479EBD0000000000FA841680000400473044022040236927CBFE1EA1320FE96767224C228C4DA9CDECCFF29845CA117DCE52CBB5022028D04310644078F0649DC15E16AF3FC757D423A4B602411FB9D5F6C8D8B0D3CB0147304402201A7E547B6A74B3B6D15394FD0ADF6EE743835363CBA31C572848C1B70972E1D4022065C136663F47A45579444FBC8122037EB205D0BCFBEB0C6F30169EFB3A1FCDBE014752210240967ABD8E62AF16E9A7BF58564B8219154DDF88462699A68B18F9109D551EC92102C3875EE0AD453BADF33E7CA4032D9A409F3BFA73E7F39E4F33510A2BC581FEB052AEB9175720||
39A191D0AC2F84E5794A0FCD8699A1E4444475E8CEAAA43280C46FC8F03014F6|020000000001025E61BE57FE8CE61B1E21480080B125C346A7150C48E9C0C128A07EB0CE79ED910100000000FFFFFFFF79608E0C4F9AD6B7411E1727CEA8B2DE9B4121DB26170A6EAA40197776DB86730100000000FFFFFFFF02C0C62D00000000002200208AB8C9034F8F53D15BEBD70B0D55DB4012471136803103AEAA9CBD994F41B1BE0FD5700700000000160014EC9F5976A6AD77946EAC8F21024DB7368B044A7B0247304402203B0C912FEDB6FACCCACD87A1F6F5757925537D3D2DA6C35AB6CA69278A4FC244022075325292625CBA3DEBB90DB0E46AC0B2C4B560C7C7F64926E9AD8EA67157C9D10121026A235F29F714E9496EDF31A3E0F04F0846C2A1C794BDCE386D2866F49B07852A02483045022100C29A3CA97D9768104A7F7CEFA6392687C85FFB1B7224BA758FB6C6A1FC73F30502202310975C45AFFEF53BDA15A2986BD5C7BB9A0DDBE86CC8728E1B6EA5416BBEEA012103790D5AC64648B751E8DA62E171AE670A686ACD46C1010375A41488EADFEEF69200000000||
125C8FBE8F2CB2E27E3D1743F32ECDF53AB9C6F9EB6F021835D231797F94396B|0200000000010139A191D0AC2F84E5794A0FCD8699A1E4444475E8CEAAA43280C46FC8F03014F6000000000018D92980018EBD2D00000000002200206BA7F82CCF674C17240CAA601A2B2F333B7E609CE6CAC80F41D23731919600C20400483045022100DB23E0AA9FBBE5343D775D9C9DBE990A7414A6C97553C16C0A7AF584EA90BFF202206145FDB0102E1EAC04FA1BD93CA14CBF2CC5209F2E974275D2614FEC8D4D5A23014730440220544936524E034C3EEFB9B76992CFFF1BD278617FBAE74D598441B7C98D68B39B022059DF7408B33AB89FB6B0FBA310384CA4514C4716341407FB44FDC1594F3C82CD014752210225719377A2A5EDBE175F9323D2B24EAFA4414AC00AF6A66B918ED190497D66D7210387C90ECCB7124D364C9BC38E1AF156B9D13F1258AF8A2DD191AF7A20657B7E6352AEB868B020||

"SELECT hex(prev_out_tx), hex(prev_out_index), value,type,status,keyindex,channel_id,peer_id,commitment_point,confirmation_height,spend_height from outputs;"
hex(prev_out_tx)|hex(prev_out_index)|value|type|status|keyindex|channel_id|peer_id|commitment_point|confirmation_height|spend_height
5A9E3FB6E5D909A58FDCAFDA7D9320C5C01175A53E7A04395DF68ADE12E1D1DC|30|130000000|0|2|1|||||
0D64321A1C926F51AB91834B3BB3D1A42A5EA234722B36DA28C796DAB6479EBD|31|129989722|5|2|2|||||
F5B8FA2C373AAA8B71EDB60A82C2C01797441EE5FB943DD0CF89FBAB70F211E1|31|129789456|5|2|3|||||
16102989B80D40586E1052802AFC2BAC4136ED90FAD495DBB328E3499C12F873|31|127789302|5|2|4|||||
D016C120E8E97E2E89F2A8C7A10502B647D1B0C14DBAAEF6EBB6760950AD4FE3|31|127489148|5|2|10|||||
1EECBB25763C5F2116043C4E016A316210EBA455103825385AD39F52A1B32DD9|30|1000000|0|2|11||||507483|542582
601CA2211DD67DA5FAC881D2F2C5B7995D5F1FCDAF2FF7A33A63F3D69A0A429C|31|126988584|5|2|12|||||
5E61BE57FE8CE61B1E21480080B125C346A7150C48E9C0C128A07EB0CE79ED91|31|949525|5|2|13||||542582|560800
79608E0C4F9AD6B7411E1727CEA8B2DE9B4121DB26170A6EAA40197776DB8673|31|126888430|5|2|16|||||
39A191D0AC2F84E5794A0FCD8699A1E4444475E8CEAAA43280C46FC8F03014F6|31|124835087|5|2|18|||||
C3DB24981FE306E33F758551AFE5CB88C31DBC49E6050767E5ADFF9D281E98AE|31|847606|5|2|21|||||560802
7AF8868CDCFCE0C425C79BBC09BDB45B16B2C571EF371DAA0496247012C557E0|30|45687|5|2|22|||||560810
7F424C37D01020A81F5ECD2F0FE6D0B371A635D7F7A3752A91FCC2D286E993DD|30|3961|5|0|23|||||

mloop1 on 31 Jan 2019

how to determine which outputs where spent by funding transactions of the stuck channels, and which we moved away.

Yeah it is a bid confusing because the txid's in the listpeers output are shown in rpc byte-order (little endian), where in the list of transactions and outputs from the db, the rawtx and txid are shows in serialized form (network order = big endian).

For example, your first channel's
"funding_txid": "bd9e47b6da96c728da362b7234a25e2aa4d1b33b4b8391ab516f921c1a32640d" corresponds to this txid in the output list 0D64321A1C926F51AB91834B3BB3D1A42A5EA234722B36DA28C796DAB6479EBD|31|129989722|5|2|2|||||. Where prev_out_index is 1 (may be easier to skip to hex there).

EDIT: which is our change_output (we own) from funding_tx

SimonVrouwe on 3 Feb 2019

Thanks @SimonVrouwe.
I managed to dev-forget the stuck channels.
For my curiosity was wondering about the meaning of some fields in the output table: value, prev_out_index, key_index.

mloop1 on 11 Feb 2019

Thanks for the update @mloop1, I'm glad you got everything unstuck. As for the fields you asked about:

value is the number of satoshis associated with that output
prev_out_index is the output index in the transaction that created the unspent transaction output. In combination with the prev_out_tx it is used in transactions to reference where the funds came from (see Outpoint in the Bitcoin glossary)
key_index is part of the key derivation from the root secret. A value of n basically means that in order to spend this output we should use the nth keypair derived from the root secret.