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 <edwin....@innovationgear.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
