Problem

Sometimes a Storage Node Operator may encounter the "database disk image is malformed" error in their log. This could happen during unplanned shutdown or reboot. The error indicates that one or more of the sqlite3 databases may have become corrupted.

Solution

1. Firstly, we should try to verify the database with an embedded SQLite3 command. So, we need to have sqlite3 installed (v3.25.2 or later). The installation steps depend on the OS.
2. We will use Docker instead of direct installation (this option is available only for x86_64 CPUs, for arm-based boards you will need to install sqlite3 via the package manager of your OS).
3. Stop the storagenode
4. Make a backup of all the sqlite3 databases. They are located in the storage folder for your data storage. For example x:\storagenode\storage\bandwidth.db, where x:\storagenode is the data folder you had specified in the --mount type=bind,source=x:\storagenode,destination=/app/config option of the docker run command for your storagenode, or x:\storagenode\storage in case of using the Windows GUI, in the storage.path: option of the config.yaml file.
5. Check each database for errors. We will use bandwidth.db as an example in this guide.
   1. Docker (replace ${PWD} with an absolute path to the databases location, or simple switch the current location to there)

      docker run --rm -it --mount type=bind,source=${PWD},destination=/data sstc/sqlite3 find . -maxdepth 1 -iname "*.db" -print0 -exec sqlite3 '{}' 'PRAGMA integrity_check;' ';'

   2. Direct installation

      1. Linux

         sudo apt update && sudo apt install sqlite3 -y

      2. Windows
         https://www.sqlitetutorial.net/download-install-sqlite/
      3. Make sure that the version is v3.25.2 or later, otherwise the check will not work correctly. 

         sqlite3 --version

      4. perform the integrity check (perform for each database), for example for bandwidth.db:
         

         sqlite3 /path/to/storage/bandwidth.db "PRAGMA integrity_check;"

      5. Or check all databases with help of shell commands:
         1. bash (Linux)

            find /path/to/storage/ -maxdepth 1 -iname "*.db" -print0 -exec sqlite3 '{}' 'PRAGMA integrity_check;' ';'

         2. PowerShell (Windows)

            Get-ChildItem X:\storagenode\storage\*.db -File | %{$_.Name + " " + $(sqlite3.exe $_.FullName "PRAGMA integrity_check;")}

6. If you see errors in the output, then the check did not pass. We will unload all uncorrupted data and then load it back. But this could sometimes fail, too. If no errors occur here, you can skip all the following steps and start the storagenode again.

7. If you were not lucky and the check failed, then please try to fix the corrupted database(s) as shown below.

8. Open a shell

   1. Docker version of sqlite3. Open a shell Inside the container:

      docker run --rm -it --mount type=bind,source=x:\storagenode\storage,destination=/storage sstc/sqlite3 sh

      Tip. You can use tmpfs to restore your databases. It uses memory instead of disk and should take a lot less time than on HDD (you can read more about usage of tmpfs with Docker in the Use tmpfs mounts guide or this forum comment). For Windows or MacOS you must increase the allocated RAM for the docker's VM via Docker desktop application to fit a double size of the greatest corrupted database file in case of usage of tmpfs.

       
   2. You could use your shell directly if you have sqlite3 installed. In that case, use the path to your storage instead of "/storage/" across this guide below.
   3. For Windows with local sqlite3 installed, we recommend to use a PowerShell to execute the commands below. Don't forget to replace the "/storage/" folder with your local path to the folder where the databases are stored. If the sqlite3.exe executable is not in the system variable PATH, then you should specify the full path to it or run from the location of the executable.
9. Now run the following commands in the shell. You need to repeat steps 9 to 14 for each corrupted sqlite3 database:

   cp /storage/bandwidth.db /storage/bandwidth.db.bak
   sqlite3 /storage/bandwidth.db

10. You will see a prompt from sqlite3. Run this SQL script:

    .mode insert
    .output /storage/dump_all.sql
    .dump
    .exit

11. We will edit the SQL file dump_all.sql

    1. Linux or docker version:

       { echo "PRAGMA synchronous = OFF ;"; cat /storage/dump_all.sql; } | grep -v -e TRANSACTION -e ROLLBACK -e COMMIT >/storage/dump_all_notrans.sql

    2. PowerShell (Windows) with a local sqlite3 version:

       $(echo "PRAGMA synchronous = OFF ;"; Get-Content dump_all.sql) | Select-String -NotMatch "TRANSACTION|ROLLBACK|COMMIT" | Set-Content -Encoding utf8 dump_all_notrans.sql

12. Remove the corrupted database (make sure that you have a backup!)

    rm /storage/bandwidth.db

13. Now we will load the unloaded data into the new database

    sqlite3 /storage/bandwidth.db ".read /storage/dump_all_notrans.sql"

14. Check that the new database (bandwidth.db in our example) has a size larger than 0:

    1. Linux or docker version

       ls -l /storage/bandwidth.db

    2. PowerShell (Windows) with a local sqlite3 version:

       ls /storage/bandwidth.db

15. Exit from the container (skip this step, if you use a directly installed sqlite3)

    exit

16. If you are lucky and all corrupted sqlite3 databases are fixed, then you can start the storagenode again.