[sqlite] Android database corruption

[cliff 2]

Hi,

Hopefully someone here can help where the android guys haven't been able to.
We are having an issue with our sqlite database running on the android
platform. We are accessing the db from the sdcard on an android phone
running android 2.1. 

It works perfectly 99% of the time, but every now and again, we get database
corruption appear randomly. We have not changed any pragmas, so synchronous
is set to full and journal_mode delete. It does not correspond to any power
failures, or application crashes, and will succeed in pulling back data from
the database for hundreds of calls, and then fail on a query that has worked
many times before. It can occur in a number of native calls. We have
performed integrity checks on the db in question and they return ok (prior
to the corruption - androids response to a corrupt db is to delete it, so we
can't see its state after corruption).

The database in question is downloaded as a complete binary and then
accessed read-only using android's rawQuery call.

A couple of typical stack traces for when the corruption occurs:

  03-26 14:09:50.572 E/DatabaseHelper( 1253): 
android.database.sqlite.SQLiteDatabaseCorruptException: database disk image
is malformed: , while compiling: SELECT o.FieldId,o.Reference, o.Category,
o.OIndex,o.Description, c.Choice, c.Derivative FROM Option o, Choice c WHERE
o.FieldId = c.FieldId and o.Reference = ? ORDER BY o.Option, o.OIndex
  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
android.database.sqlite.SQLiteProgram.native_compile(Native Method)
  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
android.database.sqlite.SQLiteProgram.compile(SQLiteProgram.java:110)
  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:59)
  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:49)
  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:49)
  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1221)
  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1194)

We can run the exact same query hundreds of times and it is fine. Then all
of a sudden it fails. Another typical stack trace:

  03-24 14:54:04.678 E/AsyncTask( 6828): RuntimeException while executing
background thread
  1045  03-24 14:54:04.678 E/AsyncTask( 6828):
android.database.sqlite.SQLiteDatabaseCorruptException: database disk image
is malformed
  1046  03-24 14:54:04.678 E/AsyncTask( 6828):  at
android.database.sqlite.SQLiteQuery.native_fill_window(Native Method)
  1047  03-24 14:54:04.678 E/AsyncTask( 6828):  at
android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:75)
  1048  03-24 14:54:04.678 E/AsyncTask( 6828):  at
android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:288)
  1049  03-24 14:54:04.678 E/AsyncTask( 6828):  at
android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:269)
  1050  03-24 14:54:04.678 E/AsyncTask( 6828):  at
android.database.AbstractCursor.moveToPosition(AbstractCursor.java:171)
  1051  03-24 14:54:04.678 E/AsyncTask( 6828):  at
android.database.AbstractCursor.moveToFirst(AbstractCursor.java:248)

The one above is while trying to move to the first row in a cursor.

(android method native_fill_window source available here:
http://www.netmite.com/android/mydroid/frameworks/base/core/jni/android_database_SQLiteQuery.cpp
 
and native_compile
http://www.netmite.com/android/mydroid/frameworks/base/core/jni/android_database_SQLiteProgram.cpp)

We have been looking for some way we could be corrupting the database
through incorrect use, however we are now out of options. We have triple
checked we are opening and closing all cursors and connections correctly and
checked there aren't multiple threads accessing the db at the same time and
nothing seems out of the ordinary when corruption occurs.

As all we are doing is reading the database, I really can't see how we can
be corrupting it. I was wondering if anyone on this forum had experienced
anything similar in the past, or could suggest things we could do to track
down what is causing the corruption. From what I've read on the SQLite site
this type of corruption should be all but impossible, however it is
definitely happening. 

If you need any more information to help track down the problem, please let
me know. Thanks in advance for any tips, or advice to help solve this. 


Cliff
-- 
View this message in context: 
http://old.nabble.com/Android-database-corruption-tp28044218p28044218.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Android database corruption

[cliff 2]

Thanks very much for the quick response! Unfortunately, the android
SQLiteDatabase class deletes the database when it hits this, so it doesn't
give me a chance to do that. However I do have a channel to the android db
developers, so I will forward this onto them, and hopefully they can do
something with it.

Thanks again for your help.



D. Richard Hipp wrote:
> 
> 
> On Mar 26, 2010, at 12:10 PM, Shane Harrelson wrote:
> 
>> On Fri, Mar 26, 2010 at 11:38 AM, cliff 2  
>> <bailey.cliff...@gmail.com> wrote:
>>
>>>
>>> Hi,
>>>
>>> Hopefully someone here can help where the android guys haven't been  
>>> able
>>> to.
>>> We are having an issue with our sqlite database running on the  
>>> android
>>> platform. We are accessing the db from the sdcard on an android phone
>>> running android 2.1.
>>>
>>> As all we are doing is reading the database, I really can't see how  
>>> we can
>>> be corrupting it.
> 
>> You're probably hitting a cache corruption issue caused by a mutex  
>> issue
>> previously fixed.  More information here:
>>
>> http://www.sqlite.org/src/timeline?n=8=2010-01-31
>>
>> Not sure at this point what kind of work-arounds are available to you.
> 
> 
> To amplify what Shane said, the corruption is probably just in the  
> page cache, not in the database file itself.  So if you are able to  
> close and reopen the database connection when you run into problems,  
> that might provide a work-around (assuming we are guessing correctly  
> at the root cause of your problem.)
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Android-database-corruption-tp28044218p28045494.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Android database corruption

[cliff 2]

cliff 2 wrote:
> 
> 
> Thanks very much for the quick response! Unfortunately, the android
> SQLiteDatabase class deletes the database when it hits this, so it doesn't
> give me a chance to do that. However I do have a channel to the android db
> developers, so I will forward this onto them, and hopefully they can do
> something with it.
> 
> Thanks again for your help.
> 
> 
> 
> D. Richard Hipp wrote:
>> 
>> 
>> ...
>>>
>>> Not sure at this point what kind of work-arounds are available to you.
>> 
>> 
>> To amplify what Shane said, the corruption is probably just in the  
>> page cache, not in the database file itself.  So if you are able to  
>> close and reopen the database connection when you run into problems,  
>> that might provide a work-around (assuming we are guessing correctly  
>> at the root cause of your problem.)
>> 
>> D. Richard Hipp
>> d...@hwaci.com
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> 

We changed the db in question to only open in readonly mode when doing a
select rather than using a writable connection all the time. This has
greatly reduced the frequency of this error occurring, though it still
occurs. Given that the closing and opening of the db when this happens isn't
possible. Is there anything else you can think of that will further reduce
the frequency of this occurring? Is there an operation that we can avoid for
example? 

Even if the android guys can fix their end, there will still be a large
number of devices deployed with the current version of android and sqlite,
therefore anything we can do to minimize this would be useful.
-- 
View this message in context: 
http://old.nabble.com/Android-database-corruption-tp28044218p28151650.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users