9

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?

Greg Giacovelli
  • 10,164
  • 2
  • 47
  • 64

2 Answers2

20

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.

CommonsWare
  • 986,068
  • 189
  • 2,389
  • 2,491
  • This sounds like it is worth a shot, I will definitely try this tomorrow. If it works you get a checkbox and a beer. – Greg Giacovelli Jan 18 '11 at 07:10
  • Found a guinea pig and it worked. Thanks! That was awesome and sad at the same time. – Greg Giacovelli Jan 19 '11 at 05:20
  • 1
    @Greg: "it worked"...meaning removing `SQLiteDatabase.OPEN_READONLY`? Please let me know, as that would indicate a gap in the Compatibility Test Suite that we can try to close, to help prevent this problem in the future. Thanks! – CommonsWare Jan 19 '11 at 12:45
  • Yes removing the OPEN_READONLY fixed it. Thanks again. – Greg Giacovelli Jan 19 '11 at 18:34
  • 2
    @CommonsWare Indeed Android did not use WAL and HTC enabled it recently. Some more discussion on this is here: http://forum.xda-developers.com/showthread.php?t=886999 – Kevin TeslaCoil Feb 20 '11 at 08:23
-1

The fundamental underlying issue here is that you're assuming that sqlite3 database files are portable among Android devices. This is not true. The file format -- indeed, the use of SQLite at all as a database engine -- is not part of the API. HTC could make an Android phone that uses postgres instead of sqlite3, and it could still be officially compatible.

Yes, it's a common trick to pre-populate database content by bundling a sqlite3 .db file in your application's assets, and then using it intact after install. It isn't guaranteed to work, though, not even between devices running the same version of the Android platform. The only thing you can be assured of is that a .db file created on a given device running a given version of Android will continue to be usable on the same physical device, running the same or later Android build from the same system vendor.

(Not even devices of the same model from the same vendor? No: there is no guarantee that two devices branded the same way actually run the same system build under the hood, or indeed are necessarily identical hardware at different points in time during their product lifetime.)

The only way to do this portably is to embed not the raw database file itself, but a genericised parseable representation that can be played into the public API to establish the device-appropriate database file.

ctate
  • 1,379
  • 10
  • 11
  • 3
    So what you are saying is don't transfer files from one computer to another because the file format might change? I don't think that's a good answer or a true one. Do you have proof to back up this assumption? The issue seems more that the SDK calls out sqlite, sqlite 3.4.0 to be exact : http://developer.android.com/reference/android/database/sqlite/package-summary.html and this vendor didn't really follow this. Also WAL should not be needed on a ReadOnly data base as there is no locks or journaling that needs to happen. – Greg Giacovelli Aug 05 '11 at 03:36
  • You're reading the names of classes and methods and assuming that constitutes a contract about the _implementation_ of those classes and methods. This is not actually the case, unfortunately. I agree that this stinks and yes, we'd like to address the underlying problem in a sane and portable way. That won't help people who _today_ are running into this sort of issue, though. – ctate Aug 05 '11 at 20:44
  • So should I implement my own View class too? Will a vendor override the lifecycle arbitrarily? Sorry I am sort of confused about what part of the API I should be able to trust? I have since fixed the issue but your comment is not shocking me as I can't believe such things are just expected because I read the documentation and expected them to behave as they are documented? – Greg Giacovelli Aug 06 '11 at 00:37