Hi,



We had a similar problem (reported in this list several weeks ago). We
encountered duplicated values although the unique index (primary key)
appeared to be active and working. We recreated the database and since
then everything seems to be working correctly. We haven't been able to
reproduce it again with a new database.



Just try to insert a duplicate value in the old database and see if it
gets through ...



We used FB 2.0 superserver on Windows 2003 server.



Jordi.



________________________________

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Lele Gaifax
Sent: Tuesday, July 10, 2007 4:06 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Attempt to store duplicate value



Hi all,

I'm trying to investigate a strange problem, that's not the first time
it appears, and which still seems unesplicable to me.

I have a set of statistical tables, that gets updated by SPs every once
in while. They share a similar structure, with a compound UK, say
something like

CREATE TABLE StatByABC (
A INTEGER NOT NULL,
B INTEGER NOT NULL,
C INTEGER NOT NULL,
SUMOFX DOUBLE PRECISION NOT NULL,
SUMOFY DOUBLE PRECISION NOT NULL,

CONSTRAINT StatByABC_UK UNIQUE (A,B,C)
);

The involved SPs uniformely use the following idiom to populate the
tables

INSERT INTO StatByABC (A,B,C,SUMOFX,SUMOFY)
VALUES (:A, :B, :C, :SUMOFX, :SUMOFY)
WHEN SQLCODE -803 DO
UPDATE StatByABC
SET SUMOFX = SUMOFX + :SUMOFX,
SUMOFY = SUMOFY + :SUMOFY
WHERE A=:A AND B=:B AND C=:C;

that works great since records are mostly insert-only, update happens
but they're not as frequent.

The tables in question may be fed also by an importer, that copies
equivalent tables content from a remote database, where the same set of
procedures are doing the same job, reasonably with a completely
different "namespace" wrt the UK tuple (A,B,C above), that is the
imported records (almost) never collide with those computed/inserted
locally.

The whole thing is working great since years now.

Now we spotted a very few cases where, at restore time, the engine
groans about duplicated records: the restore succeds, but the UK index
is deactivated. Effectively, there are about ten duplicated UK tuples,
both in the original (still running) production DB and in the
backedup/restored one.

The FB server is running under Windows Server 2003 on a Dual Core AMD
Opteron, and it's a Classic 2.0.1. The database itself is a multifile
(1.9Gb+2.3Gb), restored from a backup in March. Inspecting it does not
reveal any strangeness: the indexes are there and all of them are
active, but nevertheless an appropriate SELECT ... GROUP BY ...
HAVING ... emits the duplicated entries!

As said, this is not the first time we meet this problem: the previous
one was on a different table, and a completely different customer even.

Do you have any hint or check I could perform? What can trick the server
to store duplicated keys in presence of a UNIQUE constraint? Is it a
synthom of some corruption in the indexes?

Thanks in advance for any suggestion,
bye, lele.





[Non-text portions of this message have been removed]