--- In firebird-support@yahoogroups.com, "robertgilland"
<robert_gilland@...> wrote:

>
> Firebird unique constraints are being violated when forced writes

are

> turned off.
>
> The following is the restoration log:
> gbak: activating and creating deferred index RDB$PRIMARY50
> gbak: activating and creating deferred index RDB$PRIMARY49
> gbak:cannot commit index RDB$PRIMARY49
> gbak: ERROR:attempt to store duplicate value (visible to active
> transactions) in unique index "RDB$PRIMARY49"
> gbak: ERROR:action cancelled by trigger (3) to preserve data

integrity

> gbak: ERROR: Cannot deactivate index used by a PRIMARY/UNIQUE
> constraint
> gbak:Exiting before completion due to errors
>
>
> We have close to 400 customers with Firebird installed.
> Below are the examples of this issue we have found;
>
> #1;
> Database = Firebrid 1.51, Superserver
> OS = Windows 2000pro
> UPS = NO
>
> #2;
> Database = Firebird RC1, Classic
> OS = Windows 2003 server
> UPS = YES
>
> #3;
> Database = Firebird RC2, Superserver
> OS = Windows 2003 server
> UPS = YES
>
> #4;
> Database = Firebird RC2, Classic
> OS = Windows 2000 server
> UPS = YES
>
> I am 99% sure that all these database have had force writes off. We
> are yet to find an example with force writes on.
>
> Does anybody know the cause of this problem and how to prevent it?

Forced Writes Off is unsafe by definition, you mention that you are
using some RC builds but fail to mention the version. FB 1 / 1.5 /
2?? Do you notice a performance gain switching it off?

If Firebird happens to write the data to the data page before
populating the index page (is this possible??) and there is a problem
such as hardware failure before the index is updated then I can see
this happenning.

Have you (or someone else) been using a tool like xcopy to move the
database around, because I can imagine that can quite easily cause
this. Make sure appropriate exceptions are in place in any virus
scanning package.

>
> Could this be a firebird bug? If so we do have the the GDB from
> example #1 and a GBK from example #4.

Rename to gdb to fdb or you will get into trouble in some windows
platforms. With the gdb, you can easily identify the problem records
by

select ID + 0
from table
group by 1
having count(*) > 1

This will not use the index so it should show up duplicates.

Adam