Subject Re: Duplicate value in primary index error at restore
Author alex_vnru
--- In ib-support@y..., "Javier Castro" <priorato@j...> wrote:
> gbak: ERROR: attempt to store duplicate value (visible to active
> transactions)
> in unique index "RDB$PRIMARY177"
> gbak: Index "RDB$PRIMARY177" failed to activate because:
> gbak: The unique index has duplicate values or NULLs.
> gbak: Delete or Update duplicate values or NULLs, and activate
index with
> action cancelled by trigger (3) to preserve data integrity
> -Cannot deactivate primary index

Javier, source database is corrupted in one of two types:

a) Broken system index used to support PK on this table - in this case
there are duplicates which can be found using explicit plan like

Select ID, Count(*) From This_Table
Group by ID
having Count(*)>1
Plan (This_Table natural)

To fix it delete redundant records if they are full duplicates or, if
there are different attributes values, update PK columns to inexistant
in the table value using unique definition by other attributes in
Where clause or rdb$db_key (I have not enough experience on this
subject but know it's possible). Don't forget to shift generator :)

b) Table contains one or more null-filled records. You can't find them
without pre-processing gdb by gfix. Try gfix -v -f, gfix -m and then

Select * From This_Table Where ID=0
Plan (This_Table natural)

or null, can't remember what is after gfix. Unfortunately we can't
rule plan in Delete statement, so you can delete them using only
non-indexed attributes in Where clause or using rdb$db_key.

When you fix the problem you can make backup/restore. Can't shurely
speak about reasons for such a corruptions, I encountered them VERY
seldom. My personal suspictions are
a) Harware problems, power loss etc.
b) OS crash.
c) IBServer crash.
d) Bug in IB garbage collection.

Best regards, Alexander V.Nevsky.