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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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                   TEXT    COLLATE SYSTEMNOCASE,
    _ModificationTime           INTEGER,
    _ServerId                   INTEGER,
    _UserId                     INTEGER,
    ParentId                    INTEGER,
    TypeNr                      TEXT    COLLATE SYSTEMNOCASE,
    TypeName                    TEXT    COLLATE SYSTEMNOCASE,
    LastModTime                 TEXT    COLLATE ISO8601,
    PendingTransferTargetServer TEXT    COLLATE SYSTEMNOCASE
);


Thanks.


--
Best Regards,
Edwin Yip
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]>
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                   TEXT    COLLATE SYSTEMNOCASE,
>     _ModificationTime           INTEGER,
>     _ServerId                   INTEGER,
>     _UserId                     INTEGER,
>     ParentId                    INTEGER,
>     TypeNr                      TEXT    COLLATE SYSTEMNOCASE,
>     TypeName                    TEXT    COLLATE SYSTEMNOCASE,
>     LastModTime                 TEXT    COLLATE ISO8601,
>     PendingTransferTargetServer TEXT    COLLATE SYSTEMNOCASE
> );
>
>
> Thanks.
>
>
> --
> Best Regards,
> Edwin Yip
>



--
Best Regards,
Edwin Yip
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]>
> 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                   TEXT    COLLATE SYSTEMNOCASE,
>>      _ModificationTime           INTEGER,
>>      _ServerId                   INTEGER,
>>      _UserId                     INTEGER,
>>      ParentId                    INTEGER,
>>      TypeNr                      TEXT    COLLATE SYSTEMNOCASE,
>>      TypeName                    TEXT    COLLATE SYSTEMNOCASE,
>>      LastModTime                 TEXT    COLLATE ISO8601,
>>      PendingTransferTargetServer TEXT    COLLATE SYSTEMNOCASE
>> );
>>
>>
>> Thanks.
>>
>>
>> --
>> Best Regards,
>> Edwin Yip
>>
>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]>
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 <[hidden email]>
>> 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                   TEXT    COLLATE SYSTEMNOCASE,
>>>      _ModificationTime           INTEGER,
>>>      _ServerId                   INTEGER,
>>>      _UserId                     INTEGER,
>>>      ParentId                    INTEGER,
>>>      TypeNr                      TEXT    COLLATE SYSTEMNOCASE,
>>>      TypeName                    TEXT    COLLATE SYSTEMNOCASE,
>>>      LastModTime                 TEXT    COLLATE ISO8601,
>>>      PendingTransferTargetServer TEXT    COLLATE SYSTEMNOCASE
>>> );
>>>
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> Best Regards,
>>> Edwin Yip
>>>
>>>
>>
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Best Regards,
Edwin Yip
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Simon Slavin-3
In reply to this post by Edwin Yip

On 1 Mar 2017, at 12:28pm, Edwin Yip <[hidden email]> 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 ?

<https://www.sqlite.org/howtocorrupt.html>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Richard Hipp-3
On 3/1/17, Simon Slavin <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Simon Slavin-3

On 1 Mar 2017, at 1:52pm, Richard Hipp <[hidden email]> wrote:

> On 3/1/17, Simon Slavin <[hidden email]> 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.

Ah.  Okay, thanks for the correction.  Edwin, ignore that bit.  Sorry to waste your time.

So I learned something today: VACUUM does not guarantee an uncorrupt database.  Interesting.

Simon.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Edwin Yip
In reply to this post by Richard Hipp-3
@Richard,

I didn't know sqlite has a "REINDEX" statement - I used "drop index" and
"create index" pair instead. Very helpful, thanks!

@Simon,
No, you didn't waste my time, as I said above, I wonder if Richard would
have mentioned the "REINDEX" command to me if you hadn't commented :)


Thank you guys!

On Wed, Mar 1, 2017 at 9:52 PM, Richard Hipp <[hidden email]> wrote:

> On 3/1/17, Simon Slavin <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Best Regards,
Edwin Yip
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...