Subject Re: How to repair a database with damaged index ?
Author Adam
--- In, Carsten Schäfer
<ca_schaefer@...> wrote:
> When i do this query on the damaged database i get no result !
> When i do this query on the database after the aborted restore i find
> the duplicate primary key.
> Can someone explain this to me ?

The index supporting the primary key constraint is missing a record.
In this case, the optimiser is being clever, and using the index on
the PK field to come up with the following plan:

PLAN (table ORDER PK_table)

The problem is that the index PK_table is the very thing we are
suspicious of. What you need to do is to trick the optimiser into not
using this index.

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

The Firebird optimiser does not recognise that +0 is actually
equivalent to the PK field, so it doesn't try to use the questionable


Theoretically, providing the index is not corrupt these two queries
are identical returning no records. If the second query returns a
record, it is corruption.

> But what is the next step ?

Stop anyone from using the database (shutdown the service if
necessary) and take a file copy of the database.

Delete one of them or change its PK value to be unique, or create a
copy of that table structure as a different table, use the insert into
select from syntax to copy all the other records into this table and
use a data pump tool to copy it to a clean structure.

> I thought i have to repair the original database and not the aborted
> restore ?

The restore creates the tables, puts all the data in, then adds the
indices. When adding the primary key constraint's index, it fails
because the index is defined as unique, and more than one piece of
data in the table is not unique.

> But how can this happen ?

Forced writes off?
Manually playing with system tables?
Using a file system level tool like xcopy etc to copy a database while
the service is running.
A bug in FB? (although this I doubt)

> I always thought this is impossible.

Providing none of the above happenned it should be.