Subject Re: Automatically deleting bad duplicate records
Author Adam
--- In, "robertgilland"
<robert_gilland@b...> wrote:
> We have a database at one of our sites that
> cannot restore because there are duplicate records
> in primary indices.
> Does anyone know how this can happen?

A bug, or corruption.

Has the field always been the primary key, or is it a field that had
no declared unique constraint that one day you decided to make a
primary key?

Does the database have forced writes on?

Have you ever copied the database using the normal [insert OS] Copy
mechanism? If so, it is possible the header page was copied flagging
the transaction as uncommitted, then by the time the OS copy got to
the data page the transaction was committed.

It is of course all speculation at this point. You will need to
provide the exact OS and Firebird version in use. Otherwise it is
just guesswork.

> Also how can we automatically clean all these records from
> the database so we can backup and restore it.


select id, count(*)
from tablewithproblems
group by id
having count(*) > 1