Re: [sqlite] Android database corruption
Hello Cliff 2, I am not as profficient as you in android programming of SQLite but i am willing to help solve this problem. The reason why i am coming forward to help is because of comment # 451 on the following fourm: http://code.google.com/p/android/issues/detail?id=5669. There are a countless number of android users whose text messages vanish randomly. After hours of online research i have found this thread posted by you. So far, your response is the closest thing to any type of addressing-the-issue that i have found. So how can i help you solve this problem to help me use my android without the fear of loosing my data? I'm sure you'll get many more people to help you with this issue on the fourm i mentioned above. My SMS and MMS have vanished on three seperate occations and the latest one was this morning. last night i recieved an MMS notification from a friend of mine. I tried to open it and it said that it was downloading the MMS. It was taking a long time to download and the 3G data connection didn't seem to be active on the notification bar. So i shut the screen off and went about my business. At a later time i went back into the sms thread between me and my friend to retrieve that MMS but could find that MMS anywhere. It was like i never got it. But at that time, all the other threads between other friends were still accessible up until this morning. I recieved a sms from another friend was able to read it fine. An hour later I recieved a mms from my wife and it downloaded it and i got to view it too. It was a small picture slideshow or something. After viewing it, i turned the screen off and I about my business. Half an hour later i turned my phone on to send an sms to one of my friends and discovered that all my messages have vanished. I use handcent btw. From my research it seems that this problem occurs independent of the sms software being used, independent of the android build being used, independent of the brand of android phone being used, independent of the other background user-installed apps being used. I can submit a bug report log of my phone if you want but not sure if it'll help. But what i'm really trying to ask you is: is there something i and many others can do to help capture critical information when this stuff occurs that would help find what your looking for with this issue? Some people say that the dialer storage is related to sms and mms because the file size of the 'dialer storage' file changes accordingly with the deletion and addition of messages. Is the sms database and SQLite database? and is it located in the 'dialer storge' file? Some people have reported that when the 'dialer storage' file size gets too large this same sms-vanishing issue occurs. Please let me and others know (at the code.google.com fourm mentioned above) if there is something we can collectively do to help because apperently google has been ignoring to address the 1000s of customer complaints regarding the very issue. cliff 2 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. 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.init(SQLiteProgram.java:59) 03-26 14:09:50.572 E/DatabaseHelper( 1253): at android.database.sqlite.SQLiteQuery.init(SQLiteQuery.java:49) 03-26 14:09:50.572 E/DatabaseHelper( 1253): at
Re: [sqlite] Android database corruption
On 3 Dec 2010, at 4:08am, jtdrexel wrote: 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). Is there any possibility at all that you can show us a database after corruption ? Perhaps you can change Android or an Android simulator so it renames the database instead of deleting it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Android database corruption
On 3 Dec 2010, at 4:08am, jtdrexel wrote: Some people say that the dialer storage is related to sms and mms because the file size of the 'dialer storage' file changes accordingly with the deletion and addition of messages. Is the sms database and SQLite database? and is it located in the 'dialer storge' file? The people at Google who wrote Android would definitely know the answers to these questions. If they want our help they know where to post, and they're in a position to give us much more error information than you can. Some people have reported that when the 'dialer storage' file size gets too large this same sms-vanishing issue occurs. There are no file-size problems with SQLite at anything as small as a file of 1000 SMS messages. From the various reports in that thread I can guess (but it's only a guess) that the problem isn't with SQLite at all but with some lower-level system like the file system or working memory or a corrupted copy of the software. Please let me and others know (at the code.google.com fourm mentioned above) If you want help, you post here and we'll answer here. If one of the SQLite developers wants to post to that list they'll do it. if there is something we can collectively do to help because apperently google has been ignoring to address the 1000s of customer complaints regarding the very issue. Okay I've scanned the thread now. We're happy to help anyone who can provide us with debugging information but the simple news that the database has become corrupted is not enough to let us figure out what's happening. If you can provide us with a copy of a corrupted database (just before it's about to be deleted) then that will at least give us something to look at. Simon. ___ 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 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
Re: [sqlite] Android database corruption
On Apr 6, 2010, at 9:15 AM, cliff 2 wrote: 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? Statically link your application against SQLite 3.6.23 instead of using the SQLite 3.5.9 that is found on Android. The bug you are hitting was fixed in SQLite 3.6.2. 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 D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Android database corruption
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.init(SQLiteProgram.java:59) 03-26 14:09:50.572 E/DatabaseHelper( 1253): at android.database.sqlite.SQLiteQuery.init(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
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. 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.init(SQLiteProgram.java:59) 03-26 14:09:50.572 E/DatabaseHelper( 1253): at android.database.sqlite.SQLiteQuery.init(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. ___
Re: [sqlite] Android database corruption
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=8b=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
Re: [sqlite] Android database corruption
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=8b=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