Workaround for Nexus 9 SQLite file write operations on external dirs?

Question

My team has discovered a bug on the Nexus 9, where our app is rendered unusable because it cannot access databases in writable mode on the external file directories. It seems to only happen if the app utilizes JNI, and only if you do not include an arm64-v8a version in the code.

Our current theory is that the Nexus 9 incorporates some alternate version of native libraries if arm64-v8a is not included, in order to be backwards compatible with apps that only have armeabi or armeabi-v7a libraries. It seems that there is a bug in some of those alternate SQLite libraries that prevents the operation above.

Has anyone found any workarounds for this issue? Re-building all our native libraries in arm64 is our current track and the most complete solution, but that will take us time (some of our libraries are external) and we prefer a quicker turnaround if possible to fix the app for our Nexus 9 users.

---

You can easily see this issue with this simple sample project (you need the latest Android NDK).

1. Add files below to project.
2. Install latest Android NDK if you don't have it.
3. Run ndk-build in the project directory.
4. Refresh, build, install, and run.
5. If you change Android.mk or Application.mk, clean the project by deleting the libs and obj folders before you run ndk-build again. You also need to manually refresh your project after each ndk-build.

Notice that the "broken" build on the Nexus 9 still works with internal files, but not with external files.

src/com/example/dbtester/DBTesterActivity.java

package com.example.dbtester;

import java.io.File;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.TextView;

public class DBTesterActivity extends Activity {

    protected static final String TABLE_NAME = "table_timestamp";

    static {
        System.loadLibrary("DB_TESTER");
    }

    private File mDbFileExternal;

    private File mDbFileInternal;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        setContentView(R.layout.dbtester);

        mDbFileExternal = new File(getExternalFilesDir(null), "tester_ext.db");
        mDbFileInternal = new File(getFilesDir(), "tester_int.db");

        ((Button)findViewById(R.id.button_e_add)).setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                addNewTimestamp(true);
            }
        });

        ((Button)findViewById(R.id.button_e_del)).setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                deleteDbFile(true);
            }
        });

        ((Button)findViewById(R.id.button_i_add)).setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                addNewTimestamp(false);
            }
        });

        ((Button)findViewById(R.id.button_i_del)).setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                deleteDbFile(false);
            }
        });

        ((Button)findViewById(R.id.button_display)).setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                setMessageView(getNativeMessage());
            }
        });
    }

    private void addNewTimestamp(boolean external) {
        long time = System.currentTimeMillis();

        File file;

        if (external) {
            file = mDbFileExternal;
        } else {
            file = mDbFileInternal;
        }

        boolean createNewDb = !file.exists();

        SQLiteDatabase db = SQLiteDatabase.openDatabase(file.getAbsolutePath(), null,
                SQLiteDatabase.CREATE_IF_NECESSARY | SQLiteDatabase.NO_LOCALIZED_COLLATORS
                        | SQLiteDatabase.OPEN_READWRITE);

        if (createNewDb) {
            db.execSQL("CREATE TABLE " + TABLE_NAME + "(TIMESTAMP INT PRIMARY KEY)");
        }

        ContentValues values = new ContentValues();
        values.put("TIMESTAMP", time);
        db.insert(TABLE_NAME, null, values);

        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
        setMessageView("Table now has " + cursor.getCount() + " entries." + "\n\n" + "Path:  "
                + file.getAbsolutePath());
    }

    private void deleteDbFile(boolean external) {
        // workaround for Android bug that sometimes doesn't delete a file
        // immediately, preventing recreation

        File file;

        if (external) {
            file = mDbFileExternal;
        } else {
            file = mDbFileInternal;
        }

        // practically guarantee unique filename by using timestamp
        File to = new File(file.getAbsolutePath() + "." + System.currentTimeMillis());

        file.renameTo(to);
        to.delete();

        setMessageView("Table deleted." + "\n\n" + "Path:  " + file.getAbsolutePath());
    }

    private void setMessageView(String msg) {
        ((TextView)findViewById(R.id.text_messages)).setText(msg);
    }

    private native String getNativeMessage();
}

res/layout/dbtester.xml

<GridLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:columnCount="1" >

    <Button
        android:id="@+id/button_e_add"
        android:text="Add Timestamp EXT" />

    <Button
        android:id="@+id/button_e_del"
        android:text="Delete DB File EXT" />

    <Button
        android:id="@+id/button_i_add"
        android:text="Add Timestamp INT" />

    <Button
        android:id="@+id/button_i_del"
        android:text="Delete DB File INT" />

    <Button
        android:id="@+id/button_display"
        android:text="Display Native Message" />

    <TextView
        android:id="@+id/text_messages"
        android:text="Messages appear here." />

</GridLayout>

jni/Android.mk

LOCAL_PATH := $(call my-dir)

include $(CLEAR_VARS)

LOCAL_CFLAGS += -std=c99
LOCAL_LDLIBS := -L$(SYSROOT)/usr/lib -llog

LOCAL_MODULE    :=  DB_TESTER
LOCAL_SRC_FILES :=  test.c

include $(BUILD_SHARED_LIBRARY)

jni/Application.mk (BROKEN)

APP_ABI := armeabi-v7a

jni/Application.mk (WORKING)

APP_ABI := armeabi-v7a arm64-v8a

jni/test.c

#include <jni.h>

JNIEXPORT jstring JNICALL Java_com_example_dbtester_DBTesterActivity_getNativeMessage
          (JNIEnv *env, jobject thisObj) {
   return (*env)->NewStringUTF(env, "Hello from native code!");
}

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.dbtester"
    android:versionCode="10"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="16"
        android:targetSdkVersion="21" />

    <application>
        <activity
            android:name="com.example.dbtester.DBTesterActivity"
            android:label="DB Tester" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

If you run the broken build on the Nexus 9, you will see SQLiteLog error messages in your LogCat like the following:

     SQLiteLog:  (28) file renamed while open: /storage/emulated/0/Android/data/com.example.dbtester/files/tester.db
SQLiteDatabase:  android.database.sqlite.SQLiteReadOnlyDatabaseException: attempt to write a readonly database (code 1032)

*Interestingly enough, if you store database files in the internal file directory, databases ARE accessible in writable mode. However, we have some large databases and it is undesired to move them all to the internal folders.

*External file directory accessed are {sdcard}/Android/data/com.example.dbtester and all subfolders, including Context.getExternalFilesDir(null) and Context.getExternalCacheDir() folders. The read/write permissions are not required anymore on Lollipop to access those folders, but I've tested it thoroughly with those permission on and off.

Answer 1

Unfortunately I don't have any workaround to suggest, but I managed to debug the issue and figure out the actual root cause at least.

On Android 32 bit ABIs, the data type ino_t (which is intended for returning/storing inode numbers) is 32 bit, while the st_ino field in struct stat (which returns inode numbers for files) is unsigned long long (which is 64 bit). This means that struct stat can return inode numbers that are truncated when stored in an ino_t. On normal linux, both the st_ino field in struct stat and ino_t are 32 bit when in 32 bit mode, so both are truncated similarly.

As long as Android has run on 32 bit kernels, this hasn't been any issue since all actual inode numbers have been 32 bit anyway, but now when running on 64 bit kernels, the kernel can use inode numbers that don't fit in ino_t. This seems to be what's happening for your files on the sdcard partition.

sqlite stores the original inode value in an ino_t (which is truncated) and later compares it what stat returns (see the fileHasMoved function in sqlite) - this is what triggers degrading to read-only mode here.

I'm not familiar with sqlite in general though; the only workaround would probably be to find a codepath which doesn't try to call fileHasMoved.

I submitted two possible solutions for the issue, and reported it as a bug:

• https://android-review.googlesource.com/115351
• https://android-review.googlesource.com/115263
• https://code.google.com/p/android/issues/detail?id=79994

Hopefully either fix is merged, and backported to the release branch and included in a (yet another) firmware update soon.

Answer 2

The DB cannot be opened:

SQLiteDatabase.openOrCreateDatabase(dbFile, null);
and
SQLiteDatabase.openDatabase(
    dbFile.getAbsolutePath(), 
    null, 
    SQLiteDatabase.CREATE_IF_NECESSARY);

The DB can be opened:(Using MODE_ENABLE_WRITE_AHEAD_LOGGING flag)

Context.openOrCreateDatabase( 
            dbFile.getAbsolutePath(),
            Context.MODE_ENABLE_WRITE_AHEAD_LOGGING, null);

Just maybe the following code might work.

SQLiteDatabase.openDatabase(
    dbFile.getAbsolutePath(), 
    null, 
    SQLiteDatabase.MODE_ENABLE_WRITE_AHEAD_LOGGING
    | SQLiteDatabase.CREATE_IF_NECESSARY);

We have not understood why it works when you use this flag. *The our app has "armeabi-v7a libs(32bit).