Updating database from version 37 to 50 db_migrate fails

[brunoaduarte]

Hi,

Not able to start my lightning node, always give me this error.

I've already pulled latest version.

2018-03-20T14:50:14.338Z lightningd(1): Not guessing addresses: manually set
2018-03-20T14:50:14.340Z lightningd(1): testing /usr/bin/lightning_channeld
2018-03-20T14:50:14.343Z lightningd(1): testing /usr/bin/lightning_closingd
2018-03-20T14:50:14.346Z lightningd(1): testing /usr/bin/lightning_gossipd
2018-03-20T14:50:14.348Z lightningd(1): testing /usr/bin/lightning_hsmd
2018-03-20T14:50:14.350Z lightningd(1): testing /usr/bin/lightning_onchaind
2018-03-20T14:50:14.351Z lightningd(1): testing /usr/bin/lightning_openingd
2018-03-20T14:50:14.353Z lightningd(1): Updating database from version 37 to 50
db_migrate:SQL logic error or missing database:CREATE TABLE db_upgrades (upgrade_from INTEGER, lightning_version TEXT);:table db_upgrades already exists

[cdecker]

It would appear that the db migration is out of sync. Could you send us the output of the following command:

sqlite3 $HOME/.lightning/lightningd.sqlite3 ".schema"

[brunoaduarte]

CREATE TABLE version (version INTEGER);
CREATE TABLE outputs (        prev_out_tx CHAR(64),                 prev_out_index INTEGER,                 value INTEGER,                      type INTEGER,                       status INTEGER,                     keyindex INTEGER, channel_id INTEGER, peer_id BLOB, commitment_point BLOB,                  PRIMARY KEY (prev_out_tx, prev_out_index)     );
CREATE TABLE vars (name VARCHAR(32), val VARCHAR(255), PRIMARY KEY (name));
CREATE TABLE shachains (                           id INTEGER,                      min_index INTEGER,                  num_valid INTEGER,                  PRIMARY KEY (id));
CREATE TABLE shachain_known (                                             shachain_id INTEGER REFERENCES shachains(id) ON DELETE CASCADE,          pos INTEGER,                                idx INTEGER,                                hash BLOB,                                  PRIMARY KEY (shachain_id, pos));
CREATE TABLE channels (  id INTEGER,  peer_id INTEGER REFERENCES peers(id) ON DELETE CASCADE,  short_channel_id BLOB,  channel_config_local INTEGER,  channel_config_remote INTEGER,  state INTEGER,  funder INTEGER,  channel_flags INTEGER,  minimum_depth INTEGER,  next_index_local INTEGER,  next_index_remote INTEGER,  next_htlc_id INTEGER,   funding_tx_id BLOB,  funding_tx_outnum INTEGER,  funding_satoshi INTEGER,  funding_locked_remote INTEGER,  push_msatoshi INTEGER,  msatoshi_local INTEGER,  fundingkey_remote BLOB,  revocation_basepoint_remote BLOB,  payment_basepoint_remote BLOB,  htlc_basepoint_remote BLOB,  delayed_payment_basepoint_remote BLOB,  per_commit_remote BLOB,  old_per_commit_remote BLOB,  local_feerate_per_kw INTEGER,  remote_feerate_per_kw INTEGER,  shachain_remote_id INTEGER,  shutdown_scriptpubkey_remote BLOB,  shutdown_keyidx_local INTEGER,  last_sent_commit_state INTEGER,  last_sent_commit_id INTEGER,  last_tx BLOB,  last_sig BLOB,  closing_fee_received INTEGER,  closing_sig_received BLOB, first_blocknum INTEGER, last_was_revoke INTEGER,  PRIMARY KEY (id));
CREATE TABLE peers (  id INTEGER,  node_id BLOB UNIQUE,  address TEXT,  PRIMARY KEY (id));
CREATE TABLE channel_configs (  id INTEGER,  dust_limit_satoshis INTEGER,  max_htlc_value_in_flight_msat INTEGER,  channel_reserve_satoshis INTEGER,  htlc_minimum_msat INTEGER,  to_self_delay INTEGER,  max_accepted_htlcs INTEGER,  PRIMARY KEY (id));
CREATE TABLE channel_htlcs (  id INTEGER,  channel_id INTEGER REFERENCES channels(id) ON DELETE CASCADE,  channel_htlc_id INTEGER,  direction INTEGER,  origin_htlc INTEGER,  msatoshi INTEGER,  cltv_expiry INTEGER,  payment_hash BLOB,  payment_key BLOB,  routing_onion BLOB,  failuremsg BLOB,  malformed_onion INTEGER,  hstate INTEGER,  shared_secret BLOB,  PRIMARY KEY (id),  UNIQUE (channel_id, channel_htlc_id, direction));
CREATE TABLE invoices (  id INTEGER,  state INTEGER,  msatoshi INTEGER,  payment_hash BLOB,  payment_key BLOB,  label TEXT, expiry_time INTEGER, pay_index INTEGER, msatoshi_received INTEGER, paid_timestamp INTEGER,  PRIMARY KEY (id),  UNIQUE (label),  UNIQUE (payment_hash));
CREATE UNIQUE INDEX invoices_pay_index  ON invoices(pay_index);
CREATE TABLE payments (  id INTEGER,  timestamp INTEGER,  status INTEGER,  payment_hash BLOB,  destination BLOB,  msatoshi INTEGER, payment_preimage BLOB, path_secrets BLOB, route_nodes BLOB, route_channels TEXT,  PRIMARY KEY (id),  UNIQUE (payment_hash));
CREATE TABLE htlc_sigs (channelid INTEGER REFERENCES channels(id) ON DELETE CASCADE, signature BLOB);
CREATE INDEX channel_idx ON htlc_sigs (channelid);
CREATE TABLE db_upgrades (upgrade_from INTEGER, lightning_version TEXT);

[cdecker]

Thanks, that is really strange there seems to be an off-by-one error somewhere. We currently don't guarantee backward compatibility, but I'll try to look for the missing migration. For now you can probably fix your DB with the following:

sqlite3 $HOME/.lightning/lightningd.sqlite3 "UPDATE versions SET version=38;"

This'll skip the duplicate migration. If that doesn't work try with 39 or 40.

[brunoaduarte]

Ok, it didn't worked with 38, but worked with 39.

Thanks !

ps: just a correction, table name is "version" not "versions".

sqlite3 $HOME/.lightning/lightningd.sqlite3 "UPDATE version SET version=39;"

[cdecker]

Great :+1: