| Subject | Re: How to repair a database with damaged index ? |
|---|---|
| Author | Adam |
| Post date | 2006-06-30T00:00:04Z |
--- In firebird-support@yahoogroups.com, Carsten Schäfer
<ca_schaefer@...> wrote:
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
index
PLAN SORT ((table NATURAL))
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.
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.
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.
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)
Adam
<ca_schaefer@...> wrote:
>The index supporting the primary key constraint is missing a record.
> 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 ?
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
index
PLAN SORT ((table NATURAL))
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 abortedThe restore creates the tables, puts all the data in, then adds the
> restore ?
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.
Adam