Il giorno mer, 11/07/2007 alle 00.45 +0000, Adam ha scritto:

> If you have declared a unique constraint, it is 'not your fault' if
> the database engine lets you put multiple records that violate that
> constraint. It is either a bug, evidence of corruption in the database
> file (garbage in, garbage out). Obviously a user could disable the
> constraint, but I am pretty sure Firebird would prevent it from being
> enabled with those duplicates.

Argh! We detected other two dbs with the same problem, running under
different condition. Of the three, two are under WinXP (one multifile,
one not) while the other is under GNU/Linux. This latter in particular
has been running great since several years ago, but the check revealed
hundreds of dups in one of the tables. Amazingly, in this case the index
was found disactivated (a condition that obviously allows dups given the
nature of the code that insert them, ie "INSERT ... WHEN SQLCODE -803
UPDATE ..."), but there's no trace of who/when/why disactivated it. It
most probably happened since last backup/restore cycle, as the previous
copy of it does not present the problem and the index is active...

In the other cases the unique contraint index is active, but it
nevertheless allowed the insertion of dups.

> I am not aware of any specific bug in 2.0 that would cause corruption
> in indices. As long as you are careful to never use a file system
> level copy utility (except in conjunction with nbackup whilst the
> database file is in a stable state), and as long as you have forced
> writes enabled, I don't see how it happenned.

No, we have an home-grown set of scripts that perform the backup/restore
cycles, still using the gbak tool.

And no, forced write is Not active, under Win32, as it degrades the
performance. At first I thought this could be the reason, but once I
found the broken under GNU/Linux... I could not support it anymore.

As this is quickly becoming a *very* serious problem, I'm following two
roads:

a) change SP code, replacing the current idiom (that served me so well
for years!) that uses "WHEN SQLCODE -803 ..." with a plain

IF (EXISTS (SELECT * FROM table WHERE pk=:pk)) THEN
UPDATE table SET .... WHERE pk=:pk
ELSE
INSERT INTO table (...)

even if I can foresee a little penality in performance, this should
work even in case the index is deactivated or not present at all

b) setting up a FB2.1beta machine, trying to replicate the problem there
(using current SPs) to see if the problem is fixed there.

Any hint will be great and appreciated!

Thank you in advance,
bye, lele.