SQLite Forum

Should we recreate database for these error codes SQLITE_INTERNAL/SQLITE_FORMAT/SQLITE_EMPTY/SQLITE_NOLFS ?

Should we recreate database for these error codes SQLITE_INTERNAL/SQLITE_FORMAT/SQLITE_EMPTY/SQLITE_NOLFS ?

(1) By Shikha on 2021-06-30 17:35:19 [link] [source]

Hello Everyone,

We are using sqlite version 3.22.0 in our application and I am seeing that we are recreating the database whenever we get below error codes :


We are capturing the error string by calling this function sqlite3_errstr(int error_code) but for all the above mentioned error codes, I am getting null string because of the way mapping is done in the code currently. Hence not able to figure out yet what is the actual error code that we are hitting. While we figure out that, I wanted to know for what all error codes (mentioned above), we should be recreating the database.

P.S. : We want to avoid recreating database to prevent data loss.

Thank you.

(2) By Larry Brasfield (larrybr) on 2021-06-30 20:21:28 in reply to 1 [link] [source]

Those errors are indicative of database corruption. However, absence of those errors is not a reliable indicator of absence of corruption. The problem is that not all corruption produces detectable inconsistency in the data structures SQLite maintains. Some corruption may just alter your data.

I can hardly condone a remedy for an application which corrupts the DB which is to just rebuild the DB when SQLite happens to have detected corruption. In fact, I reject the premise underlying your P.S. You are probably suffering data loss (which includes its illegitimate alteration) even when SQLite does not detect the corruption. I think your best course of action is to use those errors, and other indicators such as "pragma integrity_check" failures, to guide your debugging efforts to find out why your application is corrupting the DB.

(3) By Shikha on 2021-07-01 03:35:36 in reply to 2 [source]

Hi @larry,

Thanks for your response to my query.

We are doing below checks during application initialisation if we have db which was created before:

PRAGMA integrity_check SELECT name FROM sqlite_master WHERE type = 'table' pragma table_info('<tableName>') pragma index_list('<tableName>')' pragma index_info('<indexName>')

and later if we get above mentioned error codes and other error codes like SQLITE_CORRUPT/SQLITE_NOTADB/SQLITE_READONLY/SQLITE_ERROR, we recreate the database where we end up losing data.

I guess we are not over reporting the case of database corruption by considering these cases for database corruption.


(4) By Simon Slavin (slavin) on 2021-07-01 12:39:24 in reply to 3 [link] [source]

Anything that corrupts those databases the first time is likely to corrupt the new databases you create to replace them. Rather than spend time on deleting your useful data, spend the time figuring out what's causing the corruption.

The differences between the result codes you quote are useful, and give clues to whatever's causing the corruption. Since the differences between them matter only to a programmer, and a programmer has access to SQLite's own descriptions of what those numbers mean, it's pointless to write code to interpret the meanings of those different numbers.

Have your code display or store those result code somewhere a programmer can see it, and quit. Then have a human figure out the cause of the corruption.