Take the 2-minute tour ×

I have an application that frequently accesses a sqlite database. It works great most of the time but occasionally in one of my database functions fails and returns:

file is encrypted or is not a database

I don't have any encryption enabled but I can't pinpoint how this is happening. It's not consistently reproducible and from the crash logs, it happens on the main thread.

Thanks in advance.

@synchronized(self) {
    sqlite3 *database = mydb;
    int result = 0;

    static sqlite3_stmt *stmt = nil;
    if (stmt == nil) {
        const char *sql = "select sum(not isAvailable) from table1 e inner join table2 f on e.key=f.pk where f.pk=? AND e.isDeleting=0;";
        if (sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) != SQLITE_OK) {
            NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));

    sqlite3_bind_int(stmt, 1, obj.primaryKey);

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        int val = sqlite3_column_int(stmt, 0);
        result = val;
    } else {
        [NSException raise:@"SQL Fail" format:@"SQL Failed: %s", sqlite3_errmsg(database)];
    // Reset the statement for future reuse.

    return result;
share|improve this question
Version mismatch perhaps? –  CodaFi Feb 16 '13 at 7:45
i asked google and the most popular answer is version mismatch but i don't understand how this can happen only sometimes for this query...updated post with sample code –  VTS12 Feb 17 '13 at 15:39
@CodaFi -- I'm pretty sure that the SQLite file version hasn't changed in an incompatible fashion in a number of years. "Version mismatch" is the usual explanation offered, but it doesn't explain anything. –  Hot Licks Feb 17 '13 at 15:47
are you opening/closing the file rapidly? then Id guess it is IO related! –  Daij-Djan Feb 17 '13 at 15:49
the only time i call close the file is in applicationWillTerminate: –  VTS12 Feb 17 '13 at 16:28

4 Answers 4

To create a new encrypted SQLite database or to open an existing encrypted SQLite database you have to call the function sqlite3_key or to execute a "pragma key=" command immediately after opening the database before performing any other database operation. I suspect that you tried to open an existing database, but not encrypted SQLite database and expected to encrypt it by using one of the above methods. This doesn't work but results in the error message you experienced. To encrypt an existing not encrypted SQLite database you have to use function sqlite3_rekey or "pragma rekey=" command. To change the encryption key of an existing encrypted SQLite database you have to open the database, then to use sqlite3_key (or "pragma key=") and then to apply sqlite3_rekey (or "pragma rekey=").

share|improve this answer

The error occurs when the DB file somehow becomes garbled. Exactly why this might occur, though, is a bit of a puzzle.

If you are writing other files in your app, you may have accidentally overwritten the DB file. And if you were to open the same DB file twice or do two "simultaneous" operations on the DB you might be able to create this scenario (but I think that most versions of SQLite are compiled with the code to detect this and error out if there's a conflict). (Question: Do you ever get the "database is locked" error instead?)

share|improve this answer
i'm also writing to a log file...but the weird thing is after i get this error, the app runs fine on next launch as if nothing happened...i haven't seen database is locked either –  VTS12 Feb 17 '13 at 16:30
@HotLicks How is "somehow garbled" any less vague (in principle) than a version mismatch? A garbled database can be caused by something as simple as not committing a transaction, and something as complicated as botching a migration to a new version. –  CodaFi Feb 17 '13 at 16:57
@CodaFi - A version mismatch (if even possible) wouldn't occur unless a new version of either DB or the SQLite lib is installed, so could never "just happen" with a previously-running-fine app. And I have to believe that there is a separate error message for version mismatch, vs the encrypted message. –  Hot Licks Feb 17 '13 at 19:14
@VTS12 - If it's fine the next time you run, that suggests that there is a problem with heap management and the SQLite control structures or buffers are getting clobbered, not the DB itself. –  Hot Licks Feb 17 '13 at 19:15
@CodaFi - And the main problem with calling it a "version mismatch" is that that is even less helpful than saying "haven't the foggiest", since it sends you looking in the wrong direction. –  Hot Licks Feb 17 '13 at 19:19

As unhelpful as the "version mismatch" advice often is, I was experiencing the same symptoms and found that the problem was fixed by updating the database from version 2 to 3. Maybe one of the reasons this problem occurs is that the file extension for the database doesn't necessarily correctly reflect the real version number.

I could be mistaken, but when I created my first SQLite database using SQLite Administrator version and accepted the default "Save as type" of SQLite3 DB, the file it created was apparently a version 2 DB despite having a .s3db name. Under the Databases menu, the item "Migrate to SQLite3" was enabled but I figured this was a mistake in the GUI. However, after employing that feature, my problem disappeared.

Subsequent use of the Database|New option in SQLite Administrator creates a DB of the version I choose, and the file-extension matches the convention for that version. The erroneous behavior of SQL Administrator only occurred the first time I used the Database|New option.

share|improve this answer

I had this same problem today.

It turned out to be that, I was creating a column called Time in my database. Changed that to TimeStamp and it worked. I thought that somehow my file had been corrupt so i was looking at the sql code to generate the table (to redo it on a new file) when i noticed "Time" highlighted in blue indicating it was a reserved word.

share|improve this answer

Your Answer


