Database loading issue

[joelmackie]

I am getting this message when I try to run lightningd

"database: Creating database Error executing statement: wallet/db.c:1236: CREATE TABLE version (version INTEGER): database disk image is malformed"

My server lost power suddenly and have not been able to start lightningd back up. Bitcoind works perfectly though. Any help would be greatly appreciated.

[vincenzopalazzo]

This looks like a DB malformed as the error suggested, and to my knowledge, the only way to recover this error is using the backup generated in some way suggested in the docs.

Maybe others know some trick to recover from this error without a backup?

[ricriga]

I have a similar error: Error executing statement: wallet/wallet.c:3774: DELETE FROM blocks WHERE height > ?: database disk image is malformed I tried restoring from sqlite3 backup but it gives me the same error. I tried to repair lightningd.sqlite3 following https://support.storj.io/hc/en-us/articles/360029309111-How-to-fix-a-database-disk-image-is-malformed- New lightningd.sqlite3 doesn't give any error in CLN but it's missing some tables such as "channel" and lightning-cli listfunds returns 0 0. When I analyzed the corrupt lightningd.sqlite3 PRAGMA integrity_check show this errors: (11) database corruption at line 66843 of [1b256d97b5] (11) database corruption at line 66993 of [1b256d97b5] (11) database corruption at line 66843 of [1b256d97b5] (11) database corruption at line 66993 of [1b256d97b5] (11) database corruption at line 66843 of [1b256d97b5] (11) database corruption at line 66993 of [1b256d97b5] (11) database corruption at line 66843 of [1b256d97b5] (11) database corruption at line 66993 of [1b256d97b5] (11) statement aborts at 547: [PRAGMA integrity_check;] database disk image is malformed (11) database corruption at line 66843 of [1b256d97b5] (11) database corruption at line 66993 of [1b256d97b5] (11) database corruption at line 66843 of [1b256d97b5] (11) database corruption at line 66993 of [1b256d97b5] (11) statement aborts at 547: [PRAGMA integrity_check;] database disk image is malformed Anyone has any suggestion on how to fix sqlite file? how you can imagine I am quite desperate to recover the funds any help would be greatly appreciated Thx

[ricriga]

In the end, I solved it this way:

• CLN off
• renamed corrupt lightningd.sqlite3 to lightningd.sqlite3.break
• deleted lightningd.sqlite3-journal
• with Sqlite 3.29.0 or higher run a similar command (depends on sqlite3 path): sqlite3 lightningd.sqlite3.break ".recover" | sqlite3 lightningd.sqlite3