Re: [sqlite] bad data in a database file or bug?
On Wed, Nov 18, 2009 at 8:39 AM, Vasu Nori vn...@google.com wrote: On Wed, Nov 18, 2009 at 4:06 AM, D. Richard Hipp d...@hwaci.com wrote: On Nov 17, 2009, at 9:49 PM, D. Richard Hipp wrote: On Nov 17, 2009, at 9:45 PM, Vasu Nori wrote: sqlite SELECT _id, typeof(_id) FROM feeds; 1|integer 2|integer 3|integer 4|integer 5|integer 6|integer 7|integer 8|integer 9|integer 10|integer 11|integer 0|integer 13|integer 14|integer Please email your database directly to me for analysis. d...@hwaci.com The database file is corrupt, but in a way that PRAGMA integrity_check does not detect. A single byte at an offset of 13568 into the file seems to have been changed from 0x0c into 0x00. wondering how difficult it is to detect this kind of corruption - the kind where a single byte corruption can be pretty bad. in this case, it meant that the entire row went un-retrievable with the query select * from feeds where _id = 0; if it is such corruption can be detected and be included as part of pragma integrity-check command, then it could have proved quite useful in my case. any opinions? and any sqlite-committers willing to do this? if not, how about helping me with a few pointers to get me started on implementing this? thanks How was this database created? Can you recreate this problem from scratch? database is created by an android application on a phone (running android). nothing extraordinary about this application's database creation. It is not reproducible that easily. any theories or hints on how one could debug this? 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bad data in a database file or bug?
On Nov 17, 2009, at 9:49 PM, D. Richard Hipp wrote: On Nov 17, 2009, at 9:45 PM, Vasu Nori wrote: sqlite SELECT _id, typeof(_id) FROM feeds; 1|integer 2|integer 3|integer 4|integer 5|integer 6|integer 7|integer 8|integer 9|integer 10|integer 11|integer 0|integer 13|integer 14|integer Please email your database directly to me for analysis. d...@hwaci.com The database file is corrupt, but in a way that PRAGMA integrity_check does not detect. A single byte at an offset of 13568 into the file seems to have been changed from 0x0c into 0x00. How was this database created? Can you recreate this problem from scratch? 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] bad data in a database file or bug?
On Wed, Nov 18, 2009 at 4:06 AM, D. Richard Hipp d...@hwaci.com wrote: On Nov 17, 2009, at 9:49 PM, D. Richard Hipp wrote: On Nov 17, 2009, at 9:45 PM, Vasu Nori wrote: sqlite SELECT _id, typeof(_id) FROM feeds; 1|integer 2|integer 3|integer 4|integer 5|integer 6|integer 7|integer 8|integer 9|integer 10|integer 11|integer 0|integer 13|integer 14|integer Please email your database directly to me for analysis. d...@hwaci.com The database file is corrupt, but in a way that PRAGMA integrity_check does not detect. A single byte at an offset of 13568 into the file seems to have been changed from 0x0c into 0x00. How was this database created? Can you recreate this problem from scratch? database is created by an android application on a phone (running android). nothing extraordinary about this application's database creation. It is not reproducible that easily. any theories or hints on how one could debug this? 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] bad data in a database file or bug?
hi all attached is a database file with a strange behavior. it has a table feeds. has the following data (just selected 2 columns for discussion purpose) sqlite select _id, feed from feeds; _id feed -- - 1 http://www.google.com/calendar/feeds/1acpbk5jj328tc9up029ml6nog%40group.calendar.google.com/private/full 2 http://www.google.com/calendar/feeds/1hpeekrpepcq41if0r6cnrf3nc%40group.calendar.google.com/private/full 3 http://www.google.com/calendar/feeds/43p69tkmn5k339b10e9f1rrqgo%40group.calendar.google.com/private/full 4 http://www.google.com/calendar/feeds/9rdtjbibdsve143hvcbrf2ru1c%40group.calendar.google.com/private/full 5 http://www.google.com/calendar/feeds/fpbubjo06bb9t86o2bm9c3456c%40group.calendar.google.com/private/full 6 http://www.google.com/calendar/feeds/m76b3e2ssuos4qm6qijb7o2pak%40group.calendar.google.com/private/full 7 http://www.google.com/calendar/feeds/tijtufmt5st0m4rnvjhug7f8r8%40group.calendar.google.com/private/full 8 http://www.google.com/calendar/feeds/tomastaylor%40gmail.com/private/full 9 http://www.google.com/calendar/feeds/ttevp5oljm14v1m176spijqiag%40group.calendar.google.com/private/full 10 http://www.google.com/calendar/feeds/usa%40holiday.calendar.google.com/private/full 11 http://www.google.com/calendar/feeds/uu7524qmpvorujaq6hnfnvfrtg%40group.calendar.google.com/private/full 0 https://mail.google.com/mail/g/?client=1256578631218 13 http://www.google.com/m8/feeds/groups/tomastay...@gmail.com/base2_property-android?v=3.0 database file doesn't 'seem' to be corrupt sqlite pragma integrity_check; integrity_check --- ok but can't select the row _id = 0 sqlite select count(*) from feeds where _id = 0; count(*) -- 0 what gives? any insights into this interesting behavior? thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bad data in a database file or bug?
On Nov 17, 2009, at 8:09 PM, Vasu Nori wrote: but can't select the row _id = 0 What does the following query show: SELECT _id, typeof(_id) FROM feeds; 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] bad data in a database file or bug?
if I vacuum it, database file sems to have correct data. sqlite vacuum ... ; sqlite select count(*) from feeds where _id = '0'; count(*) -- 1 I can't tell if this is a bug in sqlite3 or if the database file is corrupt but sqlite3 can't recognize it when I do pragma integrity_check. On Tue, Nov 17, 2009 at 5:09 PM, Vasu Nori vn...@google.com wrote: hi all attached is a database file with a strange behavior. it has a table feeds. has the following data (just selected 2 columns for discussion purpose) sqlite select _id, feed from feeds; _id feed -- - 1 http://www.google.com/calendar/feeds/1acpbk5jj328tc9up029ml6nog%40group.calendar.google.com/private/full 2 http://www.google.com/calendar/feeds/1hpeekrpepcq41if0r6cnrf3nc%40group.calendar.google.com/private/full 3 http://www.google.com/calendar/feeds/43p69tkmn5k339b10e9f1rrqgo%40group.calendar.google.com/private/full 4 http://www.google.com/calendar/feeds/9rdtjbibdsve143hvcbrf2ru1c%40group.calendar.google.com/private/full 5 http://www.google.com/calendar/feeds/fpbubjo06bb9t86o2bm9c3456c%40group.calendar.google.com/private/full 6 http://www.google.com/calendar/feeds/m76b3e2ssuos4qm6qijb7o2pak%40group.calendar.google.com/private/full 7 http://www.google.com/calendar/feeds/tijtufmt5st0m4rnvjhug7f8r8%40group.calendar.google.com/private/full 8 http://www.google.com/calendar/feeds/tomastaylor%40gmail.com/private/full 9 http://www.google.com/calendar/feeds/ttevp5oljm14v1m176spijqiag%40group.calendar.google.com/private/full 10 http://www.google.com/calendar/feeds/usa%40holiday.calendar.google.com/private/full 11 http://www.google.com/calendar/feeds/uu7524qmpvorujaq6hnfnvfrtg%40group.calendar.google.com/private/full 0 https://mail.google.com/mail/g/?client=1256578631218 13 http://www.google.com/m8/feeds/groups/tomastay...@gmail.com/base2_property-android?v=3.0 database file doesn't 'seem' to be corrupt sqlite pragma integrity_check; integrity_check --- ok but can't select the row _id = 0 sqlite select count(*) from feeds where _id = 0; count(*) -- 0 what gives? any insights into this interesting behavior? thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bad data in a database file or bug?
in one case you do: select count(*) from feeds where _id = '0'; in the other you do: select count(*) from feeds where _id = 0; --- note the missing quotes this shouldn't make a difference (since SQLite is typeless), but I wonder if it is in this case. what is the type of _id? can you run the query that DRH suggested? SELECT _id, typeof(_id) FROM feeds; -Shane On Tue, Nov 17, 2009 at 8:57 PM, Vasu Nori vn...@google.com wrote: if I vacuum it, database file sems to have correct data. sqlite vacuum ... ; sqlite select count(*) from feeds where _id = '0'; count(*) -- 1 I can't tell if this is a bug in sqlite3 or if the database file is corrupt but sqlite3 can't recognize it when I do pragma integrity_check. On Tue, Nov 17, 2009 at 5:09 PM, Vasu Nori vn...@google.com wrote: hi all attached is a database file with a strange behavior. it has a table feeds. has the following data (just selected 2 columns for discussion purpose) sqlite select _id, feed from feeds; _id feed -- - 1 http://www.google.com/calendar/feeds/1acpbk5jj328tc9up029ml6nog%40group.calendar.google.com/private/full 2 http://www.google.com/calendar/feeds/1hpeekrpepcq41if0r6cnrf3nc%40group.calendar.google.com/private/full 3 http://www.google.com/calendar/feeds/43p69tkmn5k339b10e9f1rrqgo%40group.calendar.google.com/private/full 4 http://www.google.com/calendar/feeds/9rdtjbibdsve143hvcbrf2ru1c%40group.calendar.google.com/private/full 5 http://www.google.com/calendar/feeds/fpbubjo06bb9t86o2bm9c3456c%40group.calendar.google.com/private/full 6 http://www.google.com/calendar/feeds/m76b3e2ssuos4qm6qijb7o2pak%40group.calendar.google.com/private/full 7 http://www.google.com/calendar/feeds/tijtufmt5st0m4rnvjhug7f8r8%40group.calendar.google.com/private/full 8 http://www.google.com/calendar/feeds/tomastaylor%40gmail.com/private/full 9 http://www.google.com/calendar/feeds/ttevp5oljm14v1m176spijqiag%40group.calendar.google.com/private/full 10 http://www.google.com/calendar/feeds/usa%40holiday.calendar.google.com/private/full 11 http://www.google.com/calendar/feeds/uu7524qmpvorujaq6hnfnvfrtg%40group.calendar.google.com/private/full 0 https://mail.google.com/mail/g/?client=1256578631218 13 http://www.google.com/m8/feeds/groups/tomastay...@gmail.com/base2_property-android?v=3.0 database file doesn't 'seem' to be corrupt sqlite pragma integrity_check; integrity_check --- ok but can't select the row _id = 0 sqlite select count(*) from feeds where _id = 0; count(*) -- 0 what gives? any insights into this interesting behavior? thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bad data in a database file or bug?
in one case you do: select count(*) from feeds where _id = '0'; in the other you do: select count(*) from feeds where _id = 0; --- note the missing quotes this shouldn't make a difference (since SQLite is typeless), but I wonder if it is in this case. what is the type of _id? can you run the query that DRH suggested? SELECT _id, typeof(_id) FROM feeds; integer type.. query below sqlite SELECT _id, typeof(_id) FROM feeds; 1|integer 2|integer 3|integer 4|integer 5|integer 6|integer 7|integer 8|integer 9|integer 10|integer 11|integer 0|integer 13|integer 14|integer -Shane On Tue, Nov 17, 2009 at 8:57 PM, Vasu Nori vnori at google.com http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users wrote: * if I vacuum it, database file sems to have correct data.** sqlite vacuum** ... ;** sqlite select count(*) from feeds where _id = '0';** count(*)** --** 1** I can't tell if this is a bug in sqlite3 or if the database file is corrupt** but sqlite3 can't recognize it when I do pragma integrity_check.** On Tue, Nov 17, 2009 at 5:09 PM, Vasu Nori vnori at google.com http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users wrote: hi all** ** attached is a database file with a strange behavior.** it has a table feeds. has the following data (just selected 2 columns** for** discussion purpose)** ** sqlite select _id, feed from feeds;** _id feed** ** --** ** -** ** 1** ** http://www.google.com/calendar/feeds/1acpbk5jj328tc9up029ml6nog%40group.calendar.google.com/private/full** 2** ** http://www.google.com/calendar/feeds/1hpeekrpepcq41if0r6cnrf3nc%40group.calendar.google.com/private/full** 3** ** http://www.google.com/calendar/feeds/43p69tkmn5k339b10e9f1rrqgo%40group.calendar.google.com/private/full** 4** ** http://www.google.com/calendar/feeds/9rdtjbibdsve143hvcbrf2ru1c%40group.calendar.google.com/private/full** 5** ** http://www.google.com/calendar/feeds/fpbubjo06bb9t86o2bm9c3456c%40group.calendar.google.com/private/full** 6** ** http://www.google.com/calendar/feeds/m76b3e2ssuos4qm6qijb7o2pak%40group.calendar.google.com/private/full** 7** ** http://www.google.com/calendar/feeds/tijtufmt5st0m4rnvjhug7f8r8%40group.calendar.google.com/private/full** 8** ** http://www.google.com/calendar/feeds/tomastaylor%40gmail.com/private/full** 9** ** http://www.google.com/calendar/feeds/ttevp5oljm14v1m176spijqiag%40group.calendar.google.com/private/full** 10** ** http://www.google.com/calendar/feeds/usa%40holiday.calendar.google.com/private/full** 11** ** http://www.google.com/calendar/feeds/uu7524qmpvorujaq6hnfnvfrtg%40group.calendar.google.com/private/full** 0 https://mail.google.com/mail/g/?client=1256578631218** ** 13** ** http://www.google.com/m8/feeds/groups/tomastay...@gmail.com/base2_property-android?v=3.0** ** database file doesn't 'seem' to be corrupt** ** sqlite pragma integrity_check;** integrity_check** ---** ok** ** ** but can't select the row _id = 0** ** sqlite select count(*) from feeds where _id = 0;** count(*)** --** 0** ** what gives? any insights into this interesting behavior?** ** thanks** ** ** ___** sqlite-users mailing list** sqlite-users at sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users** http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bad data in a database file or bug?
attached. thanks for your time. On Tue, Nov 17, 2009 at 6:49 PM, D. Richard Hipp d...@hwaci.com wrote: On Nov 17, 2009, at 9:45 PM, Vasu Nori wrote: sqlite SELECT _id, typeof(_id) FROM feeds; 1|integer 2|integer 3|integer 4|integer 5|integer 6|integer 7|integer 8|integer 9|integer 10|integer 11|integer 0|integer 13|integer 14|integer Please email your database directly to me for analysis. d...@hwaci.com 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
