SQLiteReadOnlyDatabaseException: attempt to write

问题:

So in some rare instances, I'm seeing the "attempt to write a readonly database" message, and I can't figure out where the problem lies. I'll start with the stacktrace in my logcat... as you can see from the timestamp I'm checking db.isReadOnly() only 1ms before I attempt the write. (isOpen=true, readOnly=false)

01-29 13:47:49.115: D/AWT(11055): #479.Got writable database (230537815): isOpen: (true) isReadOnly: (false) inTransaction: (false)
01-29 13:47:49.116: D/AWT(11055): #479.in transaction: Got writable database (230537815): isOpen: (true) isReadOnly: (false) inTransaction: (true)
01-29 13:47:49.116: E/SQLiteLog(11055): (1032) statement aborts at 15: [INSERT INTO Events(col1,col2,col3,col4) VALUES (?,?,?,?)] 
01-29 13:47:49.117: E/SQLiteDatabase(11055): Error inserting data="scrubbed"
01-29 13:47:49.117: E/SQLiteDatabase(11055): android.database.sqlite.SQLiteReadOnlyDatabaseException: attempt to write a readonly database (code 1032)
01-29 13:47:49.117: E/SQLiteDatabase(11055):    at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
01-29 13:47:49.117: E/SQLiteDatabase(11055):    at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:780)
01-29 13:47:49.117: E/SQLiteDatabase(11055):    at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
01-29 13:47:49.117: E/SQLiteDatabase(11055):    at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
01-29 13:47:49.117: E/SQLiteDatabase(11055):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1471)
01-29 13:47:49.117: E/SQLiteDatabase(11055):    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
01-29 13:47:49.117: E/SQLiteDatabase(11055):    at com.company.DbHelper.insertBatch(EventsDbHelper.java:174)
01-29 13:47:49.117: D/AWT(11055): #479.finalizing transaction: Got writable database (230537815): isOpen: (true) isReadOnly: (false) inTransaction: (true)
01-29 13:47:49.118: W/SQLiteLog(12120): (28) file unlinked while open: /data/user/0/com.company.app/databases/MyDatabase.db

From my source:

public void insertBatch(LinkedList<WriteQueue.DatabaseRecord> writeQueue) throws Exception {
    Log.d("AWT", "EventsDbHelper->insertBatch()");

    if (writeQueue == null) {
        return;
    }

    Iterator<DatabaseRecord> it = writeQueue.iterator();

    SQLiteDatabase db = this.getWritableDatabase();

    Log.d("AWT", String.format("Got writable database (%s): isOpen: (%s) isReadOnly: (%s) inTransaction: (%s)",
            db.hashCode(), db.isOpen(), db.isReadOnly(), db.inTransaction()));

    try {
        db.beginTransaction();

        while (it.hasNext()) {
            DatabaseRecord record = it.next();

            ContentValues initialValues = new ContentValues();
            initialValues.put(col1, val1);
            initialValues.put(col2, val2);
            initialValues.put(col3, val3);
            initialValues.put(col4, val4);

            Log.d("AWT", String.format("in transaction: Got writable database (%s): isOpen: (%s) isReadOnly: (%s) inTransaction: (%s)",
                    db.hashCode(), db.isOpen(), db.isReadOnly(), db.inTransaction()));

            db.insert(DBTBL, null, initialValues);
        }
        Log.d("AWT", String.format("finalizing transaction: Got writable database (%s): isOpen: (%s) isReadOnly: (%s) inTransaction: (%s)",
                db.hashCode(), db.isOpen(), db.isReadOnly(), db.inTransaction()));
        db.setTransactionSuccessful();

    } catch (Exception e) {
        Log.e(TAG, "Error inserting batch record into database.", e);
    } finally {
        try {
            db.endTransaction();
            db.close();
        } catch (Exception e) {
            Log.e(TAG, Global.DB_ERROR, e);
        }
    }
}

So I think that maybe one of two things is happening.

1. The DB really is being closed/set to "readonly" in that 1ms between the check and the attempted batch insert.
2. isReadOnly is lying to me and not accurately reporting the state of the database.
3. Database is being deleted partway through my insert! See the last line of the log above. I turned on strict logging for SQLite and noticed the above. I have a suspicion that a third party library might be dropping all of my databases.

Out of ideas at this point though but I'm willing to try anything suggested.

回答1:

So the root cause of this, at first glance, appears that a third party library. Unless I'm mistaken, Tagit by Mobeix is deleting the database at app startup. I added some detailed SQLite logging, including these policies:

    StrictMode.setVmPolicy(new StrictMode.VmPolicy.Builder()
    .detectLeakedSqlLiteObjects()
    .detectLeakedClosableObjects()
    .penaltyLog()
    .penaltyDeath()
    .build());

I noticed in the log that my database is being unlinked after I create and open it. More detailed logging indicates it occurs when the Mobeix library is being initialized. The offending line in question:

01-29 13:47:49.118: W/SQLiteLog(12120): (28) file unlinked while open: /data/user/0/com.company.app/databases/MyDatabase.db

So my database file is unlinked. Weird. The next call to getWritableDatabase() recreates it again and then it's fine until the app is killed and re-launched, at which point it gets deleted and recreated.

I'll update this if I ever figure out exactly what's causing the unlink.

回答2:

I'm stuck with more or less the exact same issue and I found a an open defect on the matter that makes sense...

https://code.google.com/p/android/issues/detail?id=174566

My workaround - albeit not the best solution - is to never step the database revision and track this myself, thus never calling onUpgrade(), and manually do an upgrade when updating the app.

Alternatively if you have a small DB which is read only, you can trigger the copy of the db in assets on every onCreate() in DBHelper class, but this might give unwanted problems if the filesystem is full so only do this whilst looking for a better solution.

@Override
public void onCreate(SQLiteDatabase db) {
    // Workaround for Issue 174566
    myContext.deleteDatabase(DB_NAME);
    try {
        copyDataBase();
    }
    catch(IOException e) {
        System.out.println("IOException " + e.getLocalizedMessage());
    }
}

My app now upgrades as it should with my workaround and by judging how long time it is since this defect was raised originally it might never be fixed at all...

I'm sorry this isn't a full solution to the problem, but it's a way forward at least.

回答3:

I've had a similar issue. However, I was purposefully deleting the current database as part of a restore.

What I think is happening is that SQLite flags the database as read only in order to offer protection against the file unlinked while open:.

After the restore any updates would fail with the attempt to write a readonly database (code 1032).

My solution would be to reinstantiate the DBHelper. I've done this by adding a reopen method that I call.

e.g.

    public static void reopen(Context context) {
        instance = new DBHelper(context);
    }

I then call/invoke this using

                if(copytaken && origdeleted && restoredone) {
                    DBHelper.reopen(context);
                    DBHelper.getHelper(context).expand(null,true);
                }

The call to the expand method is my equivalent/getaround for onUpgrade/versions. It adds tables and columns according to a pseudo schema being compared against the actual database.

The full DBHelper is :-

/**
 * DBHelper
 */
@SuppressWarnings("WeakerAccess")
class DBHelper extends SQLiteOpenHelper {

    private static final String LOGTAG = "SW-DBHelper";
    private static final String DBNAME = DBConstants.DATABASE_NAME;
    private static final String dbcreated =
            "001I Database " + DBNAME + " created.";
    private static final String dbunusable =
            "002E Database " + DBNAME +
            " has been set as unusable (according to schema).";
    private   static final String dbexpanded =
            "003I Database " + DBNAME + " expanded.";
    private static final String dbexpandskipped =
            "004I Database " + DBNAME + " expand skipped - nothing to alter.";
    private static final String dbbuildskipped =
            "005I Database" + DBNAME + " build skipped - no tables to add";
    public static final String THISCLASS = DBHelper.class.getSimpleName();

    /**
     * Consrtuctor
     *
     * @param context activity context
     * @param name    database name
     * @param factory cursorfactory
     * @param version database version
     */
    DBHelper(Context context, @SuppressWarnings("SameParameterValue") String name, @SuppressWarnings("SameParameterValue") SQLiteDatabase.CursorFactory factory, @SuppressWarnings("SameParameterValue") int version) {
        super(context, name, factory, version);
    }

    /**
     * Instantiates a new Db helper.
     *
     * @param context the context
     */
    DBHelper(Context context) {
        super(context, DBConstants.DATABASE_NAME, null, 1);
    }

    private static DBHelper instance;

    /**
     * Gets helper.
     *
     * @param context the context
     * @return the helper
     */
    static synchronized DBHelper getHelper(Context context) {
        if(instance == null) {
            instance = new DBHelper(context);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        expand(db, false);
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldversion, int newversion) {

    }

    /**
     * expand create database tables
     *
     * @param db             SQLIte Database, if null then instance is used
     * @param buildandexpand to attempt both create and expand
     */
    void expand(SQLiteDatabase db, boolean buildandexpand) {

        String mode = "Create Mode.";
        if (buildandexpand) {
            mode = "Expand Mode.";
        }
        String msg = mode;
        String methodname = new Object(){}.getClass().getEnclosingMethod().getName();
        LogMsg.LogMsg(LogMsg.LOGTYPE_INFORMATIONAL,LOGTAG,msg,THISCLASS,methodname);
        // if no database has been passed then get the database
        if(db == null) {
            db = instance.getWritableDatabase();
        }
        // Build Tables to reflect schema (SHOPWISE) only if schema is usable
        if(DBConstants.SHOPWISE.isDBDatabaseUsable()) {
            // Check to see if any tables need to be added
            ArrayList<String> buildsql = DBConstants.SHOPWISE.generateDBBuildSQL(db);
            if (!buildsql.isEmpty()) {
                DBConstants.SHOPWISE.actionDBBuildSQL(db);
                msg = dbcreated + buildsql.size() + " tables added.";
                LogMsg.LogMsg(LogMsg.LOGTYPE_INFORMATIONAL,LOGTAG,msg,THISCLASS,methodname);
            } else {
                msg = dbbuildskipped;
                LogMsg.LogMsg(LogMsg.LOGTYPE_INFORMATIONAL,LOGTAG,msg,THISCLASS,methodname);
            }
            if(buildandexpand) {
                ArrayList<String> altersql = DBConstants.SHOPWISE.generateDBAlterSQL(db);
                if(!altersql.isEmpty()) {
                    msg = dbexpanded + altersql.size() + " columns added.";
                    LogMsg.LogMsg(LogMsg.LOGTYPE_INFORMATIONAL,LOGTAG,msg,THISCLASS,methodname);
                    DBConstants.SHOPWISE.actionDBAlterSQL(db);
                }  else {
                    msg = dbexpandskipped;
                    LogMsg.LogMsg(LogMsg.LOGTYPE_INFORMATIONAL,LOGTAG,msg,THISCLASS,methodname);
                }
            }
        } else {
            msg = dbunusable + "\n" +
                    DBConstants.SHOPWISE.getAllDBDatabaseProblemMsgs();
            LogMsg.LogMsg(LogMsg.LOGTYPE_ERROR,LOGTAG,msg,THISCLASS,methodname);
        }
    }
    public static void reopen(Context context) {
        instance = new DBHelper(context);
    }
}

回答4:

I'm having a similar issue, which is really annoying is that it happends at any time, it's difficult to replicate the exact conditions that make it happen. I only close the DDBB in the ondestroy method of the MainActivity class. What I have done is to add a try / catch in every use of the db and add the following catch, in this case it is in the middle of a while loop, in other functions I call the function again once:

catch (SQLException e) {
    e.printStackTrace();
    Log.d(TAG, "MainService: error in AccSaveToDB with "+mainDB.getPath()+" in iteration "+j+". Closing and re-opening DB");
    DBHelper.close();
    mainDB.close();
    j--;
}

And this at the begining of each function that access to the database:

if (mainDB==null || !mainDB.isOpen()) {
    DBHelper = DefSQLiteHelper.getInstance(getApplicationContext(), "Data.db", null, 1);
    mainDB = DBHelper.getWritableDatabase();
}

So far I still have sometime this error, I couldn't figure out the cause yet, but at least my app doesn't crash and it resumes what it has to do. I can't see if the file is being deleted, but this solutions is working for me

回答5:

I had the same problem. I found the easiest way is to use enableWriteAheadLogging() on database object.

databaseObject.enableWriteAheadLogging()