Sqlite issues with HTC Desire HD

Question

Recently I have been getting a lot of complaints about the HTC Desire series and it failing while invoking sql statements. I have received reports from users with log snapshots that contain the following.

I/Database( 2348): sqlite returned: error code = 8, msg = statement aborts at 1: [pragma journal_mode = WAL;] 
E/Database( 2348): sqlite3_exec to set journal_mode of /data/data/my.app.package/files/localized_db_en_uk-1.sqlite to WAL failed

followed by my app basically burning in flames because the call to open the database results in a serious runtime error that manifests itself as the cursor being left open. There shouldn't be a cursor at this point as we are trying to open it.

This only occurs with the HTC Desire HD and Z. My code basically does the following (changed a little to isolate the problem area).

SQLiteDatabase db;
String dbName;

public SQLiteDatabase loadDb(Context context) throws IOException{
   //Close any old db handle
   if (db != null && db.isOpen()) {
      db.close();
   } 
  // The name of the database to use from the bundled assets.
  String dbAsset = "/asset_dir/"+dbName+".sqlite";
  InputStream myInput = context.getAssets().open(dbAsset, Context.MODE_PRIVATE);

  // Create a file in the app's file directory since sqlite requires a path
  // Not ideal but we will copy the file out of our bundled assets and open it
  // it in another location.
  FileOutputStream myOutput = context.openFileOutput(dbName, Context.MODE_PRIVATE);

  byte[] buffer = new byte[1024];
  int length;
  while ((length = myInput.read(buffer)) > 0) {
      myOutput.write(buffer, 0, length);
  }

  // Close the streams
  myOutput.flush();
  // Guarantee Write!
  myOutput.getFD().sync();
  myOutput.close();
  myInput.close();
  // Not grab the newly written file
  File fileObj = context.getFileStreamPath(dbName);
  // and open the database
  return db = SQLiteDatabase.openDatabase(fileObj.getAbsolutePath(), null, SQLiteDatabase.OPEN_READONLY | SQLiteDatabase.NO_LOCALIZED_COLLATORS);
}

Sadly this phone is only available in the UK and I don't have one in my inventory. I am only getting reports of this type from the HTC Desire series. I don't know what changed as this code has been working without any problem. Is there something I am missing?

Answer 1

Short answer: try removing SQLiteDatabase.OPEN_READONLY.

Longer answer:

The "WAL" is the write-ahead log, a relatively new feature in SQLite as I understand it. The SQLite docs on WAL say "It is not possible to open read-only WAL databases." Now, that appears to be more in the context of read-only media, but it might hold true for OPEN_READONLY.

I'd be somewhat surprised if this helps, as it presumes that:

• WAL is not used in standard Android
• HTC enabled WAL in those two devices
• Something special about your environment (e.g., the binary database you're schlepping out of assets) is causing this problem where an ordinary read-only database still works fine, as I cannot imagine that those devices would have passed compatibility tests with broken read-only database support

But, I would think it is at least worth a shot.

You might also consider switching from packaging the binary database to packaging the SQL statements to build/populate the database and executing them. While this will be slower (much slower if you don't use transactions), it might be less prone to database file-specific issues.