Disabling sqlite Write-Ahead logging in Android Pie

Question

In Android Pie sqlite Write-Ahead logging (WAL) has been enabled by default. This is causing errors for my existing code only in Pie devices. I have been unable to turn off WAL successfully using SQLiteDatabase.disableWriteAheadLogging() or PRAGMA journal_mode due to the way I access the database. I would like to disable WAL completely with an Android setting called db_compatibility_wal_supported :

Compatibility WAL (Write-Ahead Logging) for Apps

Does anyone know how to configure this? I don't know if this file can be altered programmatically at startup or if it is changed manually.

---

Further Details about the problem

I have a sqlite database (20mb+ / 250k records) in my app. This db is generated using plain java on my server. It contains a food database and the user of the app can add to the database (and the server is updated). This is stored in the assets folder in android. During first installation the database is copied from assets to the app folder so that it can be written to, using effectively this method :

Copy SQLite database from assets folder

Unfortunately, once I start writing to the database using SqlDroid wal is enabled and the tables which were in the original db have vanished and only any newly created tables remain. The size of the database however is still 20mb+. All the database errors are due to the missing tables. The table copying and writing method works perfectly in versions of Android prior to Pie.

Goshawk

What in the world are you doing that WAL causes errors? – Taro

If you are using a subclass of SQLiteOpenHelper then try overidding the onConfigure method and issuing the pragma in that method. – Hardihood

I am connecting to my database using SQLDroid since i use the same database access code in my App and my server. connection = new org.sqldroid.SQLDroidDriver().connect() – Goshawk

Related? #43244591 if you can use Root permission.. – Beck

Answer 1

I finally found the answer. It seems that the database errors I was receiving is not directly related to WAL. It is because the widely used code to copy a database from assets has a bug in it where the database is left open during the copy operation. This only started causing a problem in Android P. The solution is to close the database after you get the database file name.

SQLiteDatabase destinationDatabase = sqLiteOpenHelper.getWritableDatabase();
String dbFileName=destinationDatabase.getPath();
destinationDatabase.close();
// Now write the destinationDatabase using the dbFileName

This is detailed more here : Android P - 'SQLite: No Such Table Error' after copying database from assets

Goshawk

that answer just does not match the question, at all. meta.stackexchange.com/questions/16065/… – Opia

I realise that - if you view other related answers you can see that wal is the culprit for some people. It was a red-herring in my case since wal being enabled was a major change in Android Pie at the same time a change in the way database files are kept open was made. – Goshawk

well, this might have to do with exclusive file-access when opening the file in R/W mode - flag OPEN_READONLY might still change the behavior - especially when the given situation only requires R/O access. even if properly calling .close() has the same effect, the question did not provide any code, therefore it was quite impossible to spot the issue. – Opia

Answer 2

The best and simplest way to disable WAL mode in your Database is as follows:

public class MyDbHelper extends SQLiteOpenHelper {

    //...

    @Override
    public void onOpen(SQLiteDatabase db) {
        db.disableWriteAheadLogging();  // Here the solution
        super.onOpen(db);
    }

    //...
}

This way, all access to your database will be with WAL mode disabled. As much as you open and close multiple connections throughout the implementation of your App

Kinny

I agree that this Answer best fits the question. That being said, I would also update the onCreate method in your database helper class to also disable Write-Ahead Logging there: db.disableWriteAheadLogging(); – Outhaul

How about for ORMs (like Room) where the database isn't exposed directly? – Auria

using db.disableWriteAheadLogging() method, we can avoid 'No such Table' error but now we cannot update any column in DB when using Pie (9) version. NOTE: Its working fine in Marshmallow(6), I can write changes to DB here, please suggest me how to write changes to DB in Pie verison – Wargo

@Wargo Do you have a solution for cannot update database in Pie verison and higher yet? – Lachman

Answer 3

If you are using Room, you won't have direct access to the database but you can instead set the journal mode when you are building/opening the database:

db = Room.databaseBuilder(context, Database.class, "Database")
         .setJournalMode(JournalMode.TRUNCATE)
         .build();

Auria

Is truncate the same as disabling write ahead logging? – Furr

Yes it worked, it disable write ahead logging. According to official document: Let Room choose the journal mode. This is the default value when no explicit value is specified. The actual value will be TRUNCATE when the device runs API Level lower than 16 or it is a low-RAM device. Otherwise, WRITE_AHEAD_LOGGING will be used. – Ground

Answer 4

one cannot use SQLDroidDriver.ADDITONAL_DATABASE_FLAGS, simply because there is no constant available, which would negate flag ENABLE_WRITE_AHEAD_LOGGING.

WAL can still be disabled by creating either of these scenarios:

a) set flag OPEN_READONLY (applies to situations where R/O access does suffice).

b) run PRAGMA journal_mode=DELETE as the first query, in order to override PRAGMA journal_mode=WAL.

c) file an issue against SQLDroidConnection.java, in order to have .enableWriteAheadLogging() and .disableWriteAheadLogging() supported on the driver-level.

Opia

From extensive reading I don't believe it is actually possible to change the wal setting in the way I requested in my question. There was a sony runtime resource overlay feature available in some versions on Android which I cannot confirm is even possible in Pie. – Goshawk

Another possibility I found was overlaying values in config.xml but that would only work in a custom build of Android : #49250203 – Goshawk

option "a" helped with onConfigure() combination. +1 – Hepburn

Answer 5

@Rockvole please share error that you are facing, that help us to find appropriate solution.

Mean while, i understand that you want to close that WAL in android pie and you are using "SQLDroid" lib to create Sqlite DB.

This lib internally using "SQLiteDatabase" to store data locally, I think you need to call "SQLiteDatabase.disableWriteAheadLogging()" in "SQLiteDatabase" class where DB instance created the package name is "package org.sqldroid;"

or Get internal SQLiteDatabase instance and call disableWriteAheadLogging().

Second solution is create "config.xml" inside values folder and wirte "<bool name="db_compatibility_wal_supported">false</bool>" and run and check its work.

Solutrean

During sqlite initialization in my app I use the standard SQLiteDatabase instructions. In there I can disable WAL fine - it works and I have verified that. Unfortunately disabling WAL in this way is not sticky. So when I make further database commands in sqldroid WAL is turned back on. – Goshawk

If I turn WAL off in SQLDroid with a PRAGMA it is still too late because WAL has already been turned on before I issue the PRAGMA. I believe it would require a disableWriteAheadLogging() method in SQLDroid to work correctly. – Goshawk

I tried adding a config.xml into the values directory with db_compatibility_wal_supported set to false and it did nothing. – Goshawk