backup: cli tool failed to restore backup

marsu-dev commented 3 years ago

Setup:

network testnet
docker - alpine:3.11 (amd64)
c-lighning v0.9.1
plugins version df4b422
Python 3.8.2
pip freeze:

Click==7.0 flaky==3.7.0 Mako==1.1.0 MarkupSafe==1.1.1 psutil==5.7.3 pyln-client==0.8.2 tqdm==4.51.0

SQLite

lib sqlite-dev 3.30.1
python sqlite3.sqlite_version: 3.30.1

Steps to reproduce:

backup-cli init performed
backup plugin is working (db version >2000)
lightningd is stopped
file /var/lib/lightning/testnet/lightningd.sqlite3 is removed.

backup-cli restore file:///var/lib/lightning/backup/testnet.db /var/lib/lightning/testnet/lightningd.sqlite3

Error:

The lightningd.sqlite3 file is not restored, backup-cli crashed with error:

unrecognized token: "1891733WHERE"

Traceback:

662it [01:24,  7.85it/s]
Traceback (most recent call last):
  File "/usr/local/libexec/c-lightning/plugins/backup/backup-cli", line 76, in <module>
    cli()
  File "/usr/lib/python3.8/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/usr/lib/python3.8/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/usr/lib/python3.8/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/lib/python3.8/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/lib/python3.8/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/libexec/c-lightning/plugins/backup/backup-cli", line 64, in restore
    backend.restore(restore_destination)
  File "/usr/local/libexec/c-lightning/plugins/backup/backup.py", line 122, in restore
    self._restore_transaction(c.transaction)
  File "/usr/local/libexec/c-lightning/plugins/backup/backup.py", line 101, in _restore_transaction
    cur.execute(q.decode('UTF-8'))
sqlite3.OperationalError: unrecognized token: "1891733WHERE"

m-schmoock commented 3 years ago

@michel-foucault

Since this is a testnet reproducable error for you, do you mind sharing the backup destination file?
Is this still reproducable using latest version of plugin?

cdecker commented 3 years ago

This is the result of an error that is fixed in c-lightning with #4090.

The problem was that when expanding the DB statement in order to pass it to the db_write hook we would expand them without checking that they are still valid SQL.

However, if the backup was created before the fix was applied they might still have incorrectly expanded statements. So we can either build a tool to update the queries contained in the backup (iterate through the backup file and copy over statements to a new file, then swap it into place with the original file) or we rewrite on-the-fly while restoring.

FWIW, there is just a space missing between 1891733 and the WHERE, so depending on the size of the file this could be done manually as well. @michel-foucault if you're happy to share the backup (for testnet that should not be much of a concern, and we can't actually move coins without hsm_secret) we can build the rewrite tool and provide a durable solution for you :-)

cdecker commented 3 years ago

I added a fix in the form of PR #172 which will rewrite the statements that have been expanded wrongly on the fly, so once you update to that branch backup-cli restore should work out of the box.

@michel-foucault: could you try that branch and see if it fixes the issue?

marsu-dev commented 3 years ago

Great, Thanks for the fix.

Since I have used Postgres backend, with multiple database for multiple nodes, that cover my needs for the backup in production.

I have the backup and I will try to upgrade the code and restore from the backup (w & w/o patching it) for report.

lightningd / plugins