Database gets corrupted since version 4.1.3

Hi,

I have severe issues with databases becoming unreadable since updating sqlcipher to version 4.1.3.
We have two Android apps that have been using SQL Cipher for years on version 3.3.1-2.
A few weeks back, I moved to 4.1.3 and had to make some changes to the code to support both old existing database 3 versions and new 4 versions. I could not get the database migration from 3 to 4 to work since cipher_migrate always returned an error so I created code to test the version and then automatically set cipher_compatibility before opening.

This worked fine, but in the field, the database seems to corrupt at some point after which it cannot be opened any more. Problem is, I cannot reproduce it myself and my app users are all very far away. I can therefore not debug the issue. They are working in the app after which at some point they just can’t open the database anymore. In the code below it will throw a “No readable database” error meaning that the database could not be opened with either version 3 or 4 of sqlcipher.

The only solution after this is to delete the database and start over until the problem occurs again.

Does anyone see similar behaviour? Is there a know issue that corrupts the database from time to time?

Code:

public class BrooklynDBHelper extends SQLiteOpenHelper {
private static BrooklynDBHelper sInstance;
private static Context c;

private BrooklynDBHelper(Context context, int cipherVersion) {
    super(context, BrooklynDB.DATABASE_NAME, null, BrooklynDB.DATABASE_VERSION, new setSQLCipherVersion(cipherVersion));
}

public static synchronized BrooklynDBHelper getInstance(Context context, String schleuteltje) {
    if (sInstance == null) {
        c = context.getApplicationContext();
        SQLiteDatabase.loadLibs(c);
        int cipherVersion = testSqlCipherVersion(schleuteltje);
        sInstance = new BrooklynDBHelper(c, cipherVersion);
    }
    return sInstance;
}

private static int testSqlCipherVersion(String schleutol) {
    File dbFile = c.getDatabasePath(BrooklynDB.DATABASE_NAME);
    if (!dbFile.exists()) { return 0; } // No existing db, return 0

    // Try current version
    try {
        SQLiteDatabase db = SQLiteDatabase.openDatabase(
                dbFile.getAbsolutePath(),
                schleutol,
                null,
                SQLiteDatabase.OPEN_READONLY,
                new setSQLCipherVersion(0) // Defines current version
        );
        db.close();
        return 0;
    } catch (Exception ignore) {
    }

    // Try version 3
    try {
        SQLiteDatabase db = SQLiteDatabase.openDatabase(
                dbFile.getAbsolutePath(),
                schleutol,
                null,
                SQLiteDatabase.OPEN_READONLY,
                new setSQLCipherVersion(3) // Defines version 3
        );
        db.close();
        return 3;
    } catch (Exception ignore) {
    }

    Log.e("BrooklynDB", "Database SQL Cipher version not recognized! We should NOT continue without a database.");
    throw new RuntimeException("No readable database");
}

public void close() {
    sInstance.close();
}

static class setSQLCipherVersion implements SQLiteDatabaseHook {

    private int version; // 0: current version, 3: 1st used version, 4 currently default per May 2019
    private setSQLCipherVersion(int version) { this.version = version; }

    @Override
    public void preKey(SQLiteDatabase database) { }

    @Override
    public void postKey(SQLiteDatabase database) {
        if (version > 0) {
            database.rawExecSQL("PRAGMA cipher_compatibility=" + String.valueOf(version) + ";");
        }
    }
}

}

Thanks a lot!!

Fortunately I was just able to finally reproduce the issue.
I created a loop condition that did constant write, read, update, deletes on the database.
After running for about 15 minutes it started throwing the same error.

The error is:
net.sqlcipher.database.SQLiteException: attempt to write a readonly database: , while compiling: select count(*) from sqlite_master;

Quite weird since I’m actually opening the database READONLY and it’s clearly doing a read and not a write action.

Hello @bbreukelen - thanks for getting in touch. A few questions:

  1. So, you are saying that now that you received that error you are unable to open that database any more?
  2. Are you opening and running multiple simultaneous connections across threads in the test application?
  3. Can you remove the database from the device, and try to open it with the command line shell or a SQLCipher-compatible tool like DB Browser for SQLite?
  4. What was the code running that received the error?
  5. What was the last statement to run successfully prior to receiving the error?
  6. If the database does not contain sensitive information, can you send us the database file and key for analysis to support@zetetic.net?

And also, a few other general questions:

  1. Why are you opening the database readonly?
  2. When you were trying to cipher_migrate, were you doing it on a readonly connection? If so, that would explain why it was failing, as the library can’t rewrite the database file to upgrade it on a readonly connection.

Hi,

Thanks for your reply.

  1. Correct. It never opens it again.
  2. I used just one single connection.
  3. I haven’t been able to get the database from the device so far.
  4. It’s not the same code every time. It changes from time to time.
  5. I don’t know
  6. See 3

General answers

  1. I’m not opening the database readonly. I always open it readwrite.
  2. No, it was always in readwrite.

Actually searching by the error I got from the reproduction, I found a workaround for the issue.
Apparently, there is a stuck journal file. As long as the file is there and the connection is opened, it throws an error and does not open the database.
In my code I added a check where if it doesn’t open the db and the file exists, I remove the journal file and then retry to open the database.

It works but feels like a dirty solution. Not sure why the database won’t open when there’s a journal file and not sure why sometimes this journal file doesn’t get removed.

Thanks

Hello @bbreukelen - that explains almost everything here. First, you are using a readonly connection, from your code above:

The problem is that if a journal file is present and you are attempting to open the database read only the journal recovery can’t occur. This results in all for your testSqlCipherVersion calls failing.

It is dangerous to delete the journal file as a workaround, you shouldn’t do that because it can result in corruption of the database. Instead, you should always open the database read write, so that if necessary the recovery from journal file can occur.

It would also be worth looking into why the journal file is there in the first place. It would usually be left in place if a crash occurred during a transaction. Are you noticing anything like that?

Hi Stephen,

Of course, that makes total sense now.
I open the database readwrite later on in the code, but when opening it to check the version I am indeed opening it in readonly mode. I have changed it now and that fixed the issue.

Indeed, the journal file is written when the app crashes of when it’s force killed.

Thank you so much for your help!

Bo