Re: [sqlite] Strange - query returned duplicates, issue fixed after executing 'VACUUM'

[Richard Hipp]

On 3/1/17, Simon Slavin  wrote:
>
> If you have a case where VACUUM does not fix a faulty index, that would be
> very interesting.

Not necessarily.  VACUUM does not recreate the indexes, it just copies
them, row by row.  So if the index is self-consistent but it does not
match its table (it has extra rows and/or is missing rows) then VACUUM
won't fix it.

REINDEX does rebuild the indexes from scratch.  If the problem is just
indexes that are missing entries or have extra entries, REINDEX will
fix it.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange - query returned duplicates, issue fixed after executing 'VACUUM'

[Simon Slavin]

On 1 Mar 2017, at 12:28pm, Edwin Yip  wrote:

> B - The "VACCUM" only fix the issue for a table, but the very same issue
> retains for another table.

If you have a case where VACUUM does not fix a faulty index, that would be very 
interesting.  However, we would need a copy of the unfixed version of the 
database.

Crashes in your program should not be able to corrupt your database.  I’m not 
saying that it’s completely impossible, just that it’s very unexpected.  Are 
you doing any of the things mentioned here ?



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

Re: [sqlite] Strange - query returned duplicates, issue fixed after executing 'VACUUM'

[Edwin Yip]

Hello Domingo,

Thanks for your reply.

I'm not sure, but I guess it's the program (that uses the DB) crashes that
might caused this issue.

So far the recreation of the indexes fixed the issue for two tables. I'll
report back to here if I've got further findings.


On Wed, Mar 1, 2017 at 8:41 PM, Domingo Alvarez Duarte 
wrote:

> Hello Edwin!
>
> One thing that bugs me is how the indexes could becoming corrupt ?
>
> - Sqlite bug ?
>
> - Hard drive fault ?
>
> Cheers !
>
>
>
> On 01/03/17 09:28, Edwin Yip wrote:
>
>> Update 1 after another hours of checking:
>>
>> A - ' seems that the issue is related to indexes - "pragma
>> integrity_check"
>> would result in errors like:
>>
>> row 23465 missing from index IndexRetailItemLastModTime
>> row 24187 missing from index IndexRetailItemLastModTime
>>
>> B - The "VACCUM" only fix the issue for a table, but the very same issue
>> retains for another table.
>>
>> C - Re-creating the indexes seem to have fixed the problem, so far...
>>
>>
>> On Wed, Mar 1, 2017 at 5:59 PM, Edwin Yip 
>> wrote:
>>
>> Hello,
>>>
>>> I need some insights for an issue I spent hours finding out - was it DB
>>> file corruption or anything else? Details below:
>>>
>>> The table is called ProductType, "select count(*) from ProductType"
>>> returns 47 rows, which is correct.
>>>
>>> There is a column called "LastModTime" and the  COLLATE is ISO8601.
>>> "select count(*) from ProductType where (LastModTime >
>>> "1899/12/30T09:23:21")" would return 60 rows, which is **wrong**.
>>>
>>> After hours trying, I executed "VACUUM", now everything backs to normal.
>>>
>>> This is wired, is it just an occasional DB file corruption or anything
>>> worth mentioning, like any possible mistakes  might have done to the DB?
>>>
>>> Table schema:
>>> CREATE TABLE ProductType (
>>>  ID  INTEGER PRIMARY KEY AUTOINCREMENT,
>>>  MachineId   TEXTCOLLATE SYSTEMNOCASE,
>>>  _ModificationTime   INTEGER,
>>>  _ServerId   INTEGER,
>>>  _UserId INTEGER,
>>>  ParentIdINTEGER,
>>>  TypeNr  TEXTCOLLATE SYSTEMNOCASE,
>>>  TypeNameTEXTCOLLATE SYSTEMNOCASE,
>>>  LastModTime TEXTCOLLATE ISO8601,
>>>  PendingTransferTargetServer TEXTCOLLATE SYSTEMNOCASE
>>> );
>>>
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> Best Regards,
>>> Edwin Yip
>>>
>>>
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best Regards,
Edwin Yip
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange - query returned duplicates, issue fixed after executing 'VACUUM'

[Domingo Alvarez Duarte]

Hello Edwin!

One thing that bugs me is how the indexes could becoming corrupt ?

- Sqlite bug ?

- Hard drive fault ?

Cheers !


On 01/03/17 09:28, Edwin Yip wrote:

Update 1 after another hours of checking:

A - ' seems that the issue is related to indexes - "pragma integrity_check"
would result in errors like:

row 23465 missing from index IndexRetailItemLastModTime
row 24187 missing from index IndexRetailItemLastModTime

B - The "VACCUM" only fix the issue for a table, but the very same issue
retains for another table.

C - Re-creating the indexes seem to have fixed the problem, so far...


On Wed, Mar 1, 2017 at 5:59 PM, Edwin Yip 
wrote:


Hello,

I need some insights for an issue I spent hours finding out - was it DB
file corruption or anything else? Details below:

The table is called ProductType, "select count(*) from ProductType"
returns 47 rows, which is correct.

There is a column called "LastModTime" and the  COLLATE is ISO8601.
"select count(*) from ProductType where (LastModTime >
"1899/12/30T09:23:21")" would return 60 rows, which is **wrong**.

After hours trying, I executed "VACUUM", now everything backs to normal.

This is wired, is it just an occasional DB file corruption or anything
worth mentioning, like any possible mistakes  might have done to the DB?

Table schema:
CREATE TABLE ProductType (
 ID  INTEGER PRIMARY KEY AUTOINCREMENT,
 MachineId   TEXTCOLLATE SYSTEMNOCASE,
 _ModificationTime   INTEGER,
 _ServerId   INTEGER,
 _UserId INTEGER,
 ParentIdINTEGER,
 TypeNr  TEXTCOLLATE SYSTEMNOCASE,
 TypeNameTEXTCOLLATE SYSTEMNOCASE,
 LastModTime TEXTCOLLATE ISO8601,
 PendingTransferTargetServer TEXTCOLLATE SYSTEMNOCASE
);


Thanks.


--
Best Regards,
Edwin Yip






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

Re: [sqlite] Strange - query returned duplicates, issue fixed after executing 'VACUUM'

[Edwin Yip]

Update 1 after another hours of checking:

A - ' seems that the issue is related to indexes - "pragma integrity_check"
would result in errors like:

row 23465 missing from index IndexRetailItemLastModTime
row 24187 missing from index IndexRetailItemLastModTime

B - The "VACCUM" only fix the issue for a table, but the very same issue
retains for another table.

C - Re-creating the indexes seem to have fixed the problem, so far...


On Wed, Mar 1, 2017 at 5:59 PM, Edwin Yip 
wrote:

> Hello,
>
> I need some insights for an issue I spent hours finding out - was it DB
> file corruption or anything else? Details below:
>
> The table is called ProductType, "select count(*) from ProductType"
> returns 47 rows, which is correct.
>
> There is a column called "LastModTime" and the  COLLATE is ISO8601.
> "select count(*) from ProductType where (LastModTime >
> "1899/12/30T09:23:21")" would return 60 rows, which is **wrong**.
>
> After hours trying, I executed "VACUUM", now everything backs to normal.
>
> This is wired, is it just an occasional DB file corruption or anything
> worth mentioning, like any possible mistakes  might have done to the DB?
>
> Table schema:
> CREATE TABLE ProductType (
> ID  INTEGER PRIMARY KEY AUTOINCREMENT,
> MachineId   TEXTCOLLATE SYSTEMNOCASE,
> _ModificationTime   INTEGER,
> _ServerId   INTEGER,
> _UserId INTEGER,
> ParentIdINTEGER,
> TypeNr  TEXTCOLLATE SYSTEMNOCASE,
> TypeNameTEXTCOLLATE SYSTEMNOCASE,
> LastModTime TEXTCOLLATE ISO8601,
> PendingTransferTargetServer TEXTCOLLATE SYSTEMNOCASE
> );
>
>
> Thanks.
>
>
> --
> Best Regards,
> Edwin Yip
>



-- 
Best Regards,
Edwin Yip
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange - query returned duplicates, issue fixed after executing 'VACUUM'

[Edwin Yip]

Hello,

I need some insights for an issue I spent hours finding out - was it DB
file corruption or anything else? Details below:

The table is called ProductType, "select count(*) from ProductType" returns
47 rows, which is correct.

There is a column called "LastModTime" and the  COLLATE is ISO8601.
"select count(*) from ProductType where (LastModTime >
"1899/12/30T09:23:21")" would return 60 rows, which is **wrong**.

After hours trying, I executed "VACUUM", now everything backs to normal.

This is wired, is it just an occasional DB file corruption or anything
worth mentioning, like any possible mistakes  might have done to the DB?

Table schema:
CREATE TABLE ProductType (
ID  INTEGER PRIMARY KEY AUTOINCREMENT,
MachineId   TEXTCOLLATE SYSTEMNOCASE,
_ModificationTime   INTEGER,
_ServerId   INTEGER,
_UserId INTEGER,
ParentIdINTEGER,
TypeNr  TEXTCOLLATE SYSTEMNOCASE,
TypeNameTEXTCOLLATE SYSTEMNOCASE,
LastModTime TEXTCOLLATE ISO8601,
PendingTransferTargetServer TEXTCOLLATE SYSTEMNOCASE
);


Thanks.


-- 
Best Regards,
Edwin Yip
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users