Sqlite3 recover runs into out of memory error

Question

I'm trying to recover a broken sqlite database using the recover feature of sqlite3.exe:

sqlite3.exe borken.db ".recover" | sqlite3 fixed.db

The broken database has a size of 142 mb. When running the command I'll get the following error after a while:

SQL error: out of memory

Is there a known workaround for this issue?

The process doesn't seem to need a lot of value so it doesn't seem to be the real error reason.

I've also tried the x64 version of sqlite3.exe which uses a max of around 6 mb memory. So it doesn't seem to be a real memory issue.

Answer 1

Based on your command structure, I'm going to assume you're on a Linux OS.

You can use the rqlite utility if you first dump the database file to a backup. Process would look like this:

1. Open the database file in sqlite and run .output /path/to dump/file.dump

2. Once the database is dumped, install the rqlite utility here

3. Once rqlite is installed, you can run it in memory if your database is less than 2gb in total size on disk. If it's larger, you need to mount a ramdisk so that the resulting run database can be stored to disk rather than caching it in memory. Sequence of commands would look something like this:

   sudo mount -t tmpfs -o size=3072m tmpfs /mnt/ramdisk # Set size to be less than the available memory on disk

   ./rqlited -on-disk -on-disk-startup -on-disk-path /mnt/ramdisk/node1/db.sqlite /path/to/data/dir

I recommned running the above in a separate screen session. Then you can go in a separate instance/session and run rqlite as follows:

./rqlite
.restore /path/to/dumped/database/file.dump

Additional great documentation and examples about it here