Sqlite3 recreates empty database from dump file

Question

I have a problem with sqlite database.

First of all, my database crashed somehow and whatever I'm trying to do with it, it says: "database disk image is malformed". It is probably because of I am writing huge texts with lots of "\n" to two columns in a table.

So I've read a few topics here and on the other sites and I waneted to dump db and recreates it, but it said something like "the line is too long (max 1000 chars)" - now I cannot recreate this error. But the weird thing is that I am using 3.19.3 version of sqlite3, and I read on another thread here, that in the previous version, the problem with "\n" characters was resolved.

Now the other approach of mine is that I looked into dump file and using regex toget rid of all of this texts. Now my dump file reduces its size from 750 Mb to 3,5 Mb. When I'm trying to recreate db from the new dump file and the old (this big one), the result is an empty file. A console does not provide any sort of result or error for me. So below I am posting my every step in the process.

sqlite3> .open testDatabase.sqlite
sqlite3> .mode insert
sqlite3> .output dump.sql
sqlite3> .exit

mv testDatabase.sqlite corruptDatabase.sqlite

sqlite3> .open testDatabase.sqlite
sqlite3> .read dump.sql

OR

*gets rid of huge text values*
sqlite3> .open testDatabase.sqlite
sqlite3> .read small_dump.sql

Both resulting in an empty testDatabase.sqlite file. As I mentioned before, there is no error, so I have no idea what is going on.

Please help, I don't know what to do with this.

Answer 1

When the .dump command encounters a corrupt database, it outputs SQL commands (as far as it can generate them), but then outputs a ROLLBACK command to prevent an incomplete database from being created.

If you really want to get the incomplete data from the dump file, edit it and change the last line from

ROLLBACK; -- due to errors

to

COMMIT;

---

In recent SQLite versions, you can use the .recover command instead.