Subject RE: [firebird-support] Re: Attempt to store duplicate value
Author Puigsegur, Jordi
Hi Thomas,

This was probably the origin of our duplicate values ...

We shared in this list a duplicate values issue a couple of months ago,
and didn't find any coherent explanation. Basically the discussion ended
blaming either the use of a wrong CHARSET or strange characters present
in a CHAR field of the PK. However, I knew the intrinsics of the
application feeding the corrupt database and was never satisfied with
these two options. I could easily reproduce the problem executing twice
the same insert, that didn't contain any special/hidden character.

Since then, we just rebuilt our database from scratch and everything has
been working properly.

I remember that in the early days of our application we used iSQL and
had a script with DROPS and CREATES. Since the data was not relevant it
was easier to add/modify fields this way than preparing specific ALTER



[] On Behalf Of Thomas
Sent: Thursday, July 19, 2007 4:08 PM
Subject: Re: [firebird-support] Re: Attempt to store duplicate value


> 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
>>> file (garbage in, garbage out). Obviously a user could disable the
>>> constraint, but I am pretty sure Firebird would prevent it from
>>> 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
>> one not) while the other is under GNU/Linux. This latter in
>> has been running great since several years ago, but the check
>> hundreds of dups in one of the tables. Amazingly, in this case the
>> was found disactivated (a condition that obviously allows dups given
>> nature of the code that insert them, ie "INSERT ... WHEN SQLCODE -803
>> UPDATE ..."), but there's no trace of who/when/why disactivated it.
>> most probably happened since last backup/restore cycle, as the
>> 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.

When someone mentions duplicate values in e.g. a primary key, this
always reminds me to the following issue:

Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions <>

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