--- In firebird-support@yahoogroups.com, "Pierre Y." <zedalaye@...> wrote:

>
> Hello world,
>
> Consider a corrupted database where gfix -validate -full report errors
> on index pages and database pages :
>
> gbak -b : works nice
> gbak -c : fails because it cannot reactivate index RDB$PRIMARY1 due to
> duplicate primary key values or "NULLS"
>

Um, I would say the -b didn't work because you cant create the
database. Lesson: Always do a test restore of a backup file before
trusting it.

> this kind of requests fails too :
>
> select pk_field1, pk_field2, count(*) from thetable
> group by pk_field1, pk_field2
> having count(*) > 1

I would suggest this query is using the PK index to do its job, you
could force it to not use an index. The reason the duplicate got in
there in the first place is because when the second one was to be
inserted, the first one although in the table was not in the index.
(This probably deserves some investigation in its own right, because
it should never happen).

The following query stops the index from being useful

select pk_field1+0, pk_field2+0, count(*) from thetable
group by pk_1, 2
having count(*) > 1

(Substitute +0 for || '' for varchar based keys)

> The second is to gbak -c -i, index are created but not activated.
>
> gbak -c -i succeeds.
>
> select ... having count(*) > 1 succeeds
> delete from (using rdb$db_key because primary key values are duplicates
> ?) succeeds
>

At this point here, a 1000x easier solution is to do a datapump.
Download IBDataPump from clevercomponents. Do a metadata only backup
(-m) from a database with the same structure (the original database
file with the duplicates is fine).

Restore this metadata only backup which becomes your 'target'. Then
pump from your manually cleaned up database to the target and make a
coffee while it does the hard work for you.

Adam