[yunqa.de] Integrity check bug?

  • From: "Clyde England" <clyde@xxxxxxxx>
  • To: yunqa@xxxxxxxxxxxxx
  • Date: Sun, 16 Aug 2009 10:27:01 +0800


I have a rather bizarre issue that I have finally been able to track down the 
cause but not sure how to fix "pragmatically"

I am the author of a program for a niche market that enjoys a modest number of 
users, but at least we are talking in the thousands.

I use Sqlite (DiSqlite3) for all my database needs and my install ships a small 
database used for "lookups". This database has about 8 tables and 10 indexes. 
Nothing very special, the tables are quite small and the whole database is 
under 1mb in size. Just the sort of thing SQLite is great for. This database is 
only for "read" and is never written to.

I am a little on the cautious side, so on program start up I always do an 
integrity check on this lookup database ("Pragma Integrity_check"), just to 
make sure all is well.

I have a *few* users (Windows XP) report that they *always* get an integrity 
check error on program start.  The problem seems to be with the indexes and 
they see many rows with the message something like "rowid 3059 missing from 
index .....". I understand this is typical of what you would expect to see if 
the indexes were corrupt on a SQLite database.

To rule out it was not *just* my program I got these users to download 
SQLiteSpy and open this database and do the integrity check. Sure enough, they 
got exactly the same list of errors using SQLiteSpy

Now, here come the weird part. I thought that perhaps this file had somehow 
been corrupted on their computers, so I got them to email me this database. 
When I do the Integrity check on the sent databases there are *no* errors!.

To cut a long story short, I was finally able to duplicate the error (thanks to 
a "Swedish" user), but I don't know how to fix under program control. It may 
well be a DiSqlite/Sqlite/Windows bug.

I can supply the database if needed but I believe you can probably duplicate 
this with any database that has non ASCII characters, and indexes over columns 
that contain these non ASCII characters. To duplicate the problem I did the 
following steps (Windows XP):

1. Go into Control panel, and select "Regional and language options".
2. On the "Regional Options" tab select "Swedish"
3. On the "Advanced" tab select "Swedish" for "Language for non Unicode 

This I believe is the standard setup for a "Swedish" Windows user.

Now start SQLiteSpy, open the database, and do a "Pragma Integrity_check"

The database is now corrupt with many rows showing the "rowid 3059 missing from 
index ....." type messages

If you go back into control panel and change back to English US or UK ( but 
needs a computer reboot)  and repeat the steps, you no longer get the 
corruption error messages.

To my simple mind I would have thought the worst case scenario would be that 
perhaps some of the characters would not have displayed correctly. I would not 
have thought that  a SQLite database that passes the integrity check on one 
system would fail on another just because they have different language settings.

I haven't tested fully, but I should also point out that I believe the issue 
may only be with Scandinavian languages . I say this because I did the same 
test with a non native English language of "Germany" and the integrity check 
worked fine. I then tried again with Norwegian, and again I got the corrupt 
index errors.

So is this a known issue?

A bug in DiSqlite3 or Sqlite?


Delphi Inspiration mailing list

Other related posts: