Thank you for the answer.
> Your custom collation function would be my prime suspect here.
Yes, it was. Some time ago we really corrected a bug in the collation, which
resulted in decreased number of user reports. Even later we switched to the
ICU library, which - I suppose - should be relatively safe.
I know that the last statement may not be 100% true (under Windows I found
some exotic strings that violate CompareString() transitivity; I can't
remember if this confirmed for ICU), but:
a) I got access to the content that caused the corruption and found that no
unusual strings are used. For example one indexed column with a corrupted
index contained only ascii strings.
b) Suppose there was a bug in the collation. Could that bug cause a
corruption of an index that does not use that collation? Here is a real-life
example:
CREATE TABLE [account]
(
[accountid] UNIQUEIDENTIFIER NOT NULL CONSTRAINT PK_account PRIMARY
KEY
ROWGUIDCOL DEFAULT (newid()),
[address1_city] NVARCHAR(160) NULL COLLATE NOCASE,
[address1_country] NVARCHAR(160) NULL COLLATE NOCASE,
[address1_latitude] FLOAT NULL,
[address1_line1] NVARCHAR(500) NULL COLLATE NOCASE,
[address1_line2] NVARCHAR(500) NULL COLLATE NOCASE,
[address1_line3] NVARCHAR(500) NULL COLLATE NOCASE,
[address1_longitude] FLOAT NULL,
[address1_postalcode] NVARCHAR(40) NULL COLLATE NOCASE,
[address1_stateorprovince] NVARCHAR(100) NULL COLLATE NOCASE,
[createdon] DATETIME NULL,
[defaultpricelevelid] UNIQUEIDENTIFIER NULL,
[emailaddress1] NVARCHAR(200) NULL COLLATE NOCASE,
[fax] NVARCHAR(100) NULL COLLATE NOCASE,
[modifiedon] DATETIME NULL,
[name] NVARCHAR(320) NULL COLLATE NOCASE,
[ownerid] UNIQUEIDENTIFIER NULL,
[statuscode] INT NOT NULL DEFAULT(1),
[telephone1] NVARCHAR(100) NULL COLLATE NOCASE,
[transactioncurrencyid] UNIQUEIDENTIFIER NULL,
[websiteurl] NVARCHAR(400) NULL COLLATE NOCASE,
[defaultpricelevelidTarget] NVARCHAR(100) NULL COLLATE NOCASE,
[owneridTarget] NVARCHAR(100) NULL COLLATE NOCASE,
[transactioncurrencyidTarget] NVARCHAR(100) NULL COLLATE NOCASE
);
CREATE INDEX [FK_account_defaultpricelevelid] ON
[account](defaultpricelevelid);
CREATE INDEX [FK_account_ownerid] ON [account](ownerid);
CREATE INDEX [FK_account_transactioncurrencyid] ON
[account](transactioncurrencyid);
CREATE INDEX [FK_account_name] ON [account](name COLLATE NOCASE);
If NOCASE is a custom collation, could a bug in that collation explain
corruption of indexes FK_account_transactioncurrencyid and
FK_account_ownerid? (Both have NUMERIC affinity and store blobs such as
X'001C2300C5DF8BEA11DF8834FBDCD77E'.)
So far I supposed that the answer is NOT and consequently excluded a
collation bug.
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77554.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
