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 <[email protected]> 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 <[email protected]> >> 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 > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Best Regards, Edwin Yip _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
