At 07:42 PM 19/07/2007, Lele Gaifax wrote:

>...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...

During restore, activating the constraint indexes is one of the last
things to happen. If the restore fails at that point, the restore
will abort. You will have your database but it will be in an
uncertain state with regard to any of your indexes. Make sure you
create a verbose log of restores and **check it** before deleting the
old database and kicking in the new one. (Please don't say you are
using gbak -R for restores...)

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

If you are talking about an index on a character column then No. If
the index for the UNIQUE constraint is active, then somehow you are
letting in data that contains subtle differences. One space
character will do it. On CHAR fields, one *trailing* space will do it.

> > 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!

I'm sure that using SPs isn't causing the duplicates, per se. The
disabled constraint index would certainly, regardless of whether your
inserts were happening via SPs or DSQL. So would lost index pages
(thanks to forced writes being off). And, if you've got user-entered
data in constraint columns, don't use CHAR types. One tap on the
space bar will cause a duplicated value to become a
non-duplicate...only one problem that goes along with non-atomic keys.

Upgrading your database to ODS 11 is strongly recommended; then run
SET STATISTICS to enable the database to use the new optimizations.

But - you need to start somewhere with a clean database. I would
want to restore a copy somewhere then do whatever it takes to clean
up your damaged indexes, i.e. drop and recreate the
constraints. And, if that cleanup process reveals duplicates then I
would want to rethink your choice to run with asynchronous
writes...address the performance issue by some other means if you
have to run your databases on Windows.

./heLen