Subject RE: [ib-support] Re: Duplicate value in primary index error at restore
Author Javier Castro
Hi Alex,

First of all, thanks for answering.

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

Indeed we have found the existance of duplicates in the table. The question
for us is how the duplicates get in the table in the first place. The
primary key constraint is never removed. May be as you say the index
supporting the PK gets corrupted, but why does it get corrupted? Because it
is always the same table and the same index. The only feature of this index
that calls my attention is that has a date field in it. Perhaps this
faclitates corrupcion? Does anybody know?

> 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 :)

Thanks. We already know how to fix it. We are trying to prevent this from

> 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

No, no null-filled records are found.

> ...

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

I do not think a), b) or c) apply here. I do not know about d). The problem
arises at many installations with different hardware and OS. Always IB 5.6

> Best regards, Alexander V.Nevsky.

Thank you very much for helping.


Javier Castro
El Priorato Marin, S.L.