Re: [sqlite] A question about how to interpret pragma integrity_check

[Scott Hess]

On Tue, Sep 15, 2009 at 8:11 PM, Tito Ciuro  wrote:
> On Sep 15, 2009, at 8:04 PM, Scott Hess wrote:
>> Do you have any reason to believe that your database had exactly a
>> single corruption?
>
> What do you mean by 'single corruption'? This particular database is
> prone to index corruption. We just don't know why yet. Why I reported
> is what I obtain by running pragma integrity_check. I don't know how
> else to look for additional corruption.

What I mean is that SQLite can't do better than the underlying system
can provide.  So unless you have a strong reason to believe that there
is only a single bit of corruption involved, you should probably
assume that the database is corrupt in more ways than you realize.
PRAGMA integrity_check can provide definite evidence of corruption,
but just because it says that the index is corrupt doesn't mean that
the table is _not_ corrupt.

>> My experience is that once you've found one bit of corrupt data, it's
>> highly likely that you'll find others.  Earlier, you said your index
>> was corrupt.  The index is how SQLite enforces things like primary key
>> uniqueness, so if your index is corrupt, you can lose that uniqueness
>> guarantee.
>
> I understand, but this begs the question: how does the index get
> corrupted in the first place? Is there a known reason? If I know where
> to look, perhaps I could find the culprit.

http://www.sqlite.org/atomiccommit.html , especially the section about
"Things that can go wrong".  To a great degree, figuring out how
something becomes corrupt can be a really big problem.  For the most
part, SQLite arranges things so that so long as your OS doesn't crash,
things are recoverable without corruption, and SQLite is pretty good
at what it does.  For the most part, the instances of corruption I've
seen seem to relate to things like losing power when there's unwritten
data in the disk cache (deploy to enough laptops and this will simply
happen).

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

Re: [sqlite] A question about how to interpret pragma integrity_check

[Simon Slavin]

On 16 Sep 2009, at 4:11am, Tito Ciuro wrote:

> What do you mean by 'single corruption'? This particular database is
> prone to index corruption.

So use the sqlite3 command-line tool to dump it to a text file, then  
read it back in again.  See if the new database is prone to corruption  
too.

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

Re: [sqlite] A question about how to interpret pragma integrity_check

[Scott Hess]

On Tue, Sep 15, 2009 at 7:04 PM, Tito Ciuro  wrote:
> On Sep 15, 2009, at 6:59 PM, P Kishor wrote:
>
>> On Tue, Sep 15, 2009 at 8:50 PM, Tito Ciuro  wrote:
>>> On Sep 15, 2009, at 6:42 PM, P Kishor wrote:
>>>
 Could it be that those seemingly identical multiple rows actually
 have
 trailing spaces or some other non-visible character? Check for their
 length.
>>>
>>> Here we go:
>>>
 sqlite> SELECT clientName, entityName, length(entityName) FROM
 MyDBState
 ORDER BY entityName;
 com.apple.AddressBook|com.apple.contacts.CalendarURI|30
 com.apple.AddressBook|com.apple.contacts.Contact|26
 com.apple.AddressBook|com.apple.contacts.Date|23
 com.apple.AddressBook|com.apple.contacts.Email Address|32
 com.apple.AddressBook|com.apple.contacts.Group|24
 com.apple.AddressBook|com.apple.contacts.IM|21
 com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo|42
 com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo|42
 com.apple.AddressBook|com.apple.contacts.Phone Number|31
 com.apple.AddressBook|com.apple.contacts.Phone Number|31
 com.apple.AddressBook|com.apple.contacts.Related Name|31
 com.apple.AddressBook|com.apple.contacts.Related Name|31
 com.apple.AddressBook|com.apple.contacts.SmartGroup|29
 com.apple.AddressBook|com.apple.contacts.SmartGroup|29
 sqlite>
>>>
>>
>> punk...@lucknow ~/Desktop/stuff$sqlite3
>> SQLite version 3.6.11
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> CREATE TABLE MyDBState (clientName TEXT, entityName
>> TEXT,propertyNames BLOB, PRIMARY KEY (clientName, entityName));
>> sqlite> INSERT INTO MyDBState (clientName, entityName) VALUES
>> ('com.apple.AddressBook', 'com.apple.contacts.SmartGroup');
>> sqlite> INSERT INTO MyDBState (clientName, entityName) VALUES
>> ('com.apple.AddressBook', 'com.apple.contacts.SmartGroup');
>> SQL error: columns clientName, entityName are not unique
>>
>> SQLite should stop you from inserting duplicate PK right away, like
>> above.
>>
>>
>> By the way, in your original post, you gave a different name for your
>> table... see below --
>>
>>> CREATE TABLE MyDBState (clientName TEXT, entityName TEXT,
>>> propertyNames BLOB, PRIMARY KEY (clientName, entityName))
>>>
>>> If I output the data using sqlite3, I get:
>>>
>>> sqlite> SELECT clientName, entityName FROM SyncState ORDER BY
>>> entityName;
>>
>> Where did SyncState come from? Is that different from MyDBState? Or,
>> was that just a typo?
>>
>>
>> Maybe some of the folks knowing more about the innards of the software
>> can help. Which version are you using?
>
> It was a typo. I'm using Mac OS X 10.6.1, so the version seems to be
> 3.6.12
>
> Yeah, it's puzzling how this can be happening. We really don't know
> why... we're looking.

Do you have any reason to believe that your database had exactly a
single corruption?

My experience is that once you've found one bit of corrupt data, it's
highly likely that you'll find others.  Earlier, you said your index
was corrupt.  The index is how SQLite enforces things like primary key
uniqueness, so if your index is corrupt, you can lose that uniqueness
guarantee.

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

Re: [sqlite] A question about how to interpret pragma integrity_check

[Tito Ciuro]

On Sep 15, 2009, at 6:59 PM, P Kishor wrote:

> On Tue, Sep 15, 2009 at 8:50 PM, Tito Ciuro  wrote:
>> On Sep 15, 2009, at 6:42 PM, P Kishor wrote:
>>
>>> Could it be that those seemingly identical multiple rows actually  
>>> have
>>> trailing spaces or some other non-visible character? Check for their
>>> length.
>>
>> Here we go:
>>
>>> sqlite> SELECT clientName, entityName, length(entityName) FROM  
>>> MyDBState
>>> ORDER BY entityName;
>>> com.apple.AddressBook|com.apple.contacts.CalendarURI|30
>>> com.apple.AddressBook|com.apple.contacts.Contact|26
>>> com.apple.AddressBook|com.apple.contacts.Date|23
>>> com.apple.AddressBook|com.apple.contacts.Email Address|32
>>> com.apple.AddressBook|com.apple.contacts.Group|24
>>> com.apple.AddressBook|com.apple.contacts.IM|21
>>> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo|42
>>> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo|42
>>> com.apple.AddressBook|com.apple.contacts.Phone Number|31
>>> com.apple.AddressBook|com.apple.contacts.Phone Number|31
>>> com.apple.AddressBook|com.apple.contacts.Related Name|31
>>> com.apple.AddressBook|com.apple.contacts.Related Name|31
>>> com.apple.AddressBook|com.apple.contacts.SmartGroup|29
>>> com.apple.AddressBook|com.apple.contacts.SmartGroup|29
>>> sqlite>
>>
>
> punk...@lucknow ~/Desktop/stuff$sqlite3
> SQLite version 3.6.11
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE MyDBState (clientName TEXT, entityName
> TEXT,propertyNames BLOB, PRIMARY KEY (clientName, entityName));
> sqlite> INSERT INTO MyDBState (clientName, entityName) VALUES
> ('com.apple.AddressBook', 'com.apple.contacts.SmartGroup');
> sqlite> INSERT INTO MyDBState (clientName, entityName) VALUES
> ('com.apple.AddressBook', 'com.apple.contacts.SmartGroup');
> SQL error: columns clientName, entityName are not unique
>
> SQLite should stop you from inserting duplicate PK right away, like  
> above.
>
>
> By the way, in your original post, you gave a different name for your
> table... see below --
>
>> CREATE TABLE MyDBState (clientName TEXT, entityName TEXT,
>> propertyNames BLOB, PRIMARY KEY (clientName, entityName))
>>
>> If I output the data using sqlite3, I get:
>>
>> sqlite> SELECT clientName, entityName FROM SyncState ORDER BY
>> entityName;
>
> Where did SyncState come from? Is that different from MyDBState? Or,
> was that just a typo?
>
>
> Maybe some of the folks knowing more about the innards of the software
> can help. Which version are you using?

It was a typo. I'm using Mac OS X 10.6.1, so the version seems to be  
3.6.12

Yeah, it's puzzling how this can be happening. We really don't know  
why... we're looking.

Thanks again,

-- Tito

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

Re: [sqlite] A question about how to interpret pragma integrity_check

[P Kishor]

On Tue, Sep 15, 2009 at 8:50 PM, Tito Ciuro  wrote:
> On Sep 15, 2009, at 6:42 PM, P Kishor wrote:
>
>> Could it be that those seemingly identical multiple rows actually have
>> trailing spaces or some other non-visible character? Check for their
>> length.
>
> Here we go:
>
>> sqlite> SELECT clientName, entityName, length(entityName) FROM MyDBState
>> ORDER BY entityName;
>> com.apple.AddressBook|com.apple.contacts.CalendarURI|30
>> com.apple.AddressBook|com.apple.contacts.Contact|26
>> com.apple.AddressBook|com.apple.contacts.Date|23
>> com.apple.AddressBook|com.apple.contacts.Email Address|32
>> com.apple.AddressBook|com.apple.contacts.Group|24
>> com.apple.AddressBook|com.apple.contacts.IM|21
>> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo|42
>> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo|42
>> com.apple.AddressBook|com.apple.contacts.Phone Number|31
>> com.apple.AddressBook|com.apple.contacts.Phone Number|31
>> com.apple.AddressBook|com.apple.contacts.Related Name|31
>> com.apple.AddressBook|com.apple.contacts.Related Name|31
>> com.apple.AddressBook|com.apple.contacts.SmartGroup|29
>> com.apple.AddressBook|com.apple.contacts.SmartGroup|29
>> sqlite>
>

punk...@lucknow ~/Desktop/stuff$sqlite3
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE MyDBState (clientName TEXT, entityName
TEXT,propertyNames BLOB, PRIMARY KEY (clientName, entityName));
sqlite> INSERT INTO MyDBState (clientName, entityName) VALUES
('com.apple.AddressBook', 'com.apple.contacts.SmartGroup');
sqlite> INSERT INTO MyDBState (clientName, entityName) VALUES
('com.apple.AddressBook', 'com.apple.contacts.SmartGroup');
SQL error: columns clientName, entityName are not unique

SQLite should stop you from inserting duplicate PK right away, like above.


By the way, in your original post, you gave a different name for your
table... see below --

> CREATE TABLE MyDBState (clientName TEXT, entityName TEXT,
> propertyNames BLOB, PRIMARY KEY (clientName, entityName))
>
> If I output the data using sqlite3, I get:
>
> sqlite> SELECT clientName, entityName FROM SyncState ORDER BY
> entityName;

Where did SyncState come from? Is that different from MyDBState? Or,
was that just a typo?


Maybe some of the folks knowing more about the innards of the software
can help. Which version are you using?


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

Re: [sqlite] A question about how to interpret pragma integrity_check

[Tito Ciuro]

On Sep 15, 2009, at 6:42 PM, P Kishor wrote:

> Could it be that those seemingly identical multiple rows actually have
> trailing spaces or some other non-visible character? Check for their
> length.

Here we go:

> sqlite> SELECT clientName, entityName, length(entityName) FROM  
> MyDBState ORDER BY entityName;
> com.apple.AddressBook|com.apple.contacts.CalendarURI|30
> com.apple.AddressBook|com.apple.contacts.Contact|26
> com.apple.AddressBook|com.apple.contacts.Date|23
> com.apple.AddressBook|com.apple.contacts.Email Address|32
> com.apple.AddressBook|com.apple.contacts.Group|24
> com.apple.AddressBook|com.apple.contacts.IM|21
> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo|42
> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo|42
> com.apple.AddressBook|com.apple.contacts.Phone Number|31
> com.apple.AddressBook|com.apple.contacts.Phone Number|31
> com.apple.AddressBook|com.apple.contacts.Related Name|31
> com.apple.AddressBook|com.apple.contacts.Related Name|31
> com.apple.AddressBook|com.apple.contacts.SmartGroup|29
> com.apple.AddressBook|com.apple.contacts.SmartGroup|29
> sqlite>


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

Re: [sqlite] A question about how to interpret pragma integrity_check

[P Kishor]

On Tue, Sep 15, 2009 at 8:34 PM, Tito Ciuro  wrote:
> Hello,
> On Sep 15, 2009, at 6:15 PM, P Kishor wrote:
>
> well, your clientName, entityName combo is not unique in the list
> above, and it should be given it is a PK. I have no idea how you
> managed to insert these rows while the PK constraint was active.
>
> Precisely.
> I cannot reproduce this easily, but this bug is there for sure.


Could it be that those seemingly identical multiple rows actually have
trailing spaces or some other non-visible character? Check for their
length.


> Thank you,
> -- Tito



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A question about how to interpret pragma integrity_check

[Tito Ciuro]

Hello,

On Sep 15, 2009, at 6:15 PM, P Kishor wrote:

> well, your clientName, entityName combo is not unique in the list
> above, and it should be given it is a PK. I have no idea how you
> managed to insert these rows while the PK constraint was active.

Precisely.

I cannot reproduce this easily, but this bug is there for sure.

Thank you,

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

Re: [sqlite] A question about how to interpret pragma integrity_check

[Simon Slavin]

On 16 Sep 2009, at 2:15am, P Kishor wrote:

> well, your clientName, entityName combo is not unique in the list
> above, and it should be given it is a PK. I have no idea how you
> managed to insert these rows while the PK constraint was active.

What happens in SQLite if you load the data first, then create a  
constraint, a UNIQUE, or whatever ?  Oh, I see.

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

Re: [sqlite] A question about how to interpret pragma integrity_check

[P Kishor]

On Tue, Sep 15, 2009 at 4:22 PM, Tito Ciuro  wrote:
> Hello,
>
> On Sep 15, 2009, at 12:55 PM, D. Richard Hipp wrote:
>
>>
>> On Sep 15, 2009, at 3:33 PM, Tito Ciuro wrote:
>>
>>> Hello,
>>>
>>> Given the following pragma integrity_check output:
>>>
 sqlite> pragma integrity_check;
 rowid 106931 missing from index sqlite_autoindex_MyDBState_1
 rowid 106933 missing from index sqlite_autoindex_MyDBState_1
 rowid 106935 missing from index sqlite_autoindex_MyDBState_1
 wrong # of entries in index sqlite_autoindex_MyDBState_1
>>>
>>> Does this mean that:
>>>
>>> 1) the index is corrupted
>>> 2) the rowids are truly missing (data loss)
>>>
>>> Running vacuum doesn't solve the problem.
>>
>> It means the index is corrupt.  Try running "REINDEX".
>
> When I run REINDEX I get the following error:
>
>> SQL error: indexed columns are not unique
>
> The table was created like this:
>
>> CREATE TABLE MyDBState (clientName TEXT, entityName TEXT,
>> propertyNames BLOB, PRIMARY KEY (clientName, entityName))
>
> If I output the data using sqlite3, I get:
>
>> sqlite> SELECT clientName, entityName FROM SyncState ORDER BY
>> entityName;
>> com.apple.AddressBook|com.apple.contacts.CalendarURI
>> com.apple.AddressBook|com.apple.contacts.Contact
>> com.apple.AddressBook|com.apple.contacts.Date
>> com.apple.AddressBook|com.apple.contacts.Email Address
>> com.apple.AddressBook|com.apple.contacts.Group
>> com.apple.AddressBook|com.apple.contacts.IM
>> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo
>> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo
>> com.apple.AddressBook|com.apple.contacts.Phone Number
>> com.apple.AddressBook|com.apple.contacts.Phone Number
>> com.apple.AddressBook|com.apple.contacts.Related Name
>> com.apple.AddressBook|com.apple.contacts.Related Name
>> com.apple.AddressBook|com.apple.contacts.SmartGroup
>> com.apple.AddressBook|com.apple.contacts.SmartGroup
>> sqlite>
>
>

well, your clientName, entityName combo is not unique in the list
above, and it should be given it is a PK. I have no idea how you
managed to insert these rows while the PK constraint was active.


> Thanks again,
>
> -- Tito
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] A question about how to interpret pragma integrity_check

[Tito Ciuro]

Hello,

On Sep 15, 2009, at 12:55 PM, D. Richard Hipp wrote:

>
> On Sep 15, 2009, at 3:33 PM, Tito Ciuro wrote:
>
>> Hello,
>>
>> Given the following pragma integrity_check output:
>>
>>> sqlite> pragma integrity_check;
>>> rowid 106931 missing from index sqlite_autoindex_MyDBState_1
>>> rowid 106933 missing from index sqlite_autoindex_MyDBState_1
>>> rowid 106935 missing from index sqlite_autoindex_MyDBState_1
>>> wrong # of entries in index sqlite_autoindex_MyDBState_1
>>
>> Does this mean that:
>>
>> 1) the index is corrupted
>> 2) the rowids are truly missing (data loss)
>>
>> Running vacuum doesn't solve the problem.
>
> It means the index is corrupt.  Try running "REINDEX".

When I run REINDEX I get the following error:

> SQL error: indexed columns are not unique

The table was created like this:

> CREATE TABLE MyDBState (clientName TEXT, entityName TEXT,  
> propertyNames BLOB, PRIMARY KEY (clientName, entityName))

If I output the data using sqlite3, I get:

> sqlite> SELECT clientName, entityName FROM SyncState ORDER BY  
> entityName;
> com.apple.AddressBook|com.apple.contacts.CalendarURI
> com.apple.AddressBook|com.apple.contacts.Contact
> com.apple.AddressBook|com.apple.contacts.Date
> com.apple.AddressBook|com.apple.contacts.Email Address
> com.apple.AddressBook|com.apple.contacts.Group
> com.apple.AddressBook|com.apple.contacts.IM
> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo
> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo
> com.apple.AddressBook|com.apple.contacts.Phone Number
> com.apple.AddressBook|com.apple.contacts.Phone Number
> com.apple.AddressBook|com.apple.contacts.Related Name
> com.apple.AddressBook|com.apple.contacts.Related Name
> com.apple.AddressBook|com.apple.contacts.SmartGroup
> com.apple.AddressBook|com.apple.contacts.SmartGroup
> sqlite>


Thanks again,

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

Re: [sqlite] A question about how to interpret pragma integrity_check

[D. Richard Hipp]

On Sep 15, 2009, at 3:33 PM, Tito Ciuro wrote:

> Hello,
>
> Given the following pragma integrity_check output:
>
>> sqlite> pragma integrity_check;
>> rowid 106931 missing from index sqlite_autoindex_MyDBState_1
>> rowid 106933 missing from index sqlite_autoindex_MyDBState_1
>> rowid 106935 missing from index sqlite_autoindex_MyDBState_1
>> wrong # of entries in index sqlite_autoindex_MyDBState_1
>
> Does this mean that:
>
> 1) the index is corrupted
> 2) the rowids are truly missing (data loss)
>
> Running vacuum doesn't solve the problem.

It means the index is corrupt.  Try running "REINDEX".

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] A question about how to interpret pragma integrity_check

[Tito Ciuro]

Hello,

Given the following pragma integrity_check output:

> sqlite> pragma integrity_check;
> rowid 106931 missing from index sqlite_autoindex_MyDBState_1
> rowid 106933 missing from index sqlite_autoindex_MyDBState_1
> rowid 106935 missing from index sqlite_autoindex_MyDBState_1
> wrong # of entries in index sqlite_autoindex_MyDBState_1

Does this mean that:

1) the index is corrupted
2) the rowids are truly missing (data loss)

Running vacuum doesn't solve the problem. The documentation states the  
following about vacuum:

> The VACUUM command cleans the main database by copying its contents  
> to a temporary database file and reloading the original database  
> file from the copy. This eliminates free pages, aligns table data to  
> be contiguous, and otherwise cleans up the database file structure

I suspect that vacuum cannot fix this issue because there's nothing  
wrong with the index (structurally speaking). Am I correct? When this  
problem appears, is there a way to recover from this state? I would  
think that rebuilding the index would help, since the new ones would  
point to the right set of data. However, if there is a real data loss,  
dropping the index would only mask the real problem.

Any ideas?

Thanks,

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