backup: cli tool failed to restore backup

[marsu-dev]

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]

@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]

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]

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]

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.