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



Jordi.



________________________________

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas
Steinmaurer
Sent: Thursday, July 19, 2007 4:08 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: Attempt to store duplicate value



Helen,

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

When someone mentions duplicate values in e.g. a primary key, this
always reminds me to the following issue:
http://tracker.firebirdsql.org/browse/CORE-104
<http://tracker.firebirdsql.org/browse/CORE-104>

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





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