Subject Re: [firebird-support] Firebird 2.0.1: Database corrupt under high load CPU load
Author Helen Borrie
At 02:34 AM 13/04/2007, you wrote:
>I'm running FB 2.0.1 on a system (Dell PowerEdge; Raid/SCSI system;
>Windows Server 2003) with about 30 concurrent client connections.
>Some of these connections are long-lasting. The system is often
>under high load (average CPU load is about 80%, but sometimes the
>server is on 100% for hours).
>Regularly the stressed DB is corrupt and I am getting a lot of the
>following or similar log messages:
>"database file appears corrupt ()
>wrong page type
>page 4970 is of wrong type (expected 7, found 5)"
>"database file appears corrupt ()
>wrong page type
>page 4970 is of wrong type (expected 7, found 3)"
>"Page 6458 is use but marked free"
>"Index 2 is corrupt on page 782 level 1. File:
>\fb2\dev\fb2R2_0_1\firebird2\src\jrd\validation.cpp, line: 1656
> in table TMSG (131)"
>"Index 2 has orphan child page at page 782 in table TMSG (131)"
>Usually it is not possible to repair or backup/restore the DB.
>The DB is set to forced-write, it is excluded from the AV scanner and
>it is not touched by any backup software.
>Does anybody know where to start to analyze these issues?

You appear to have a corrupt index on a foreign key in the table
TMSG. "Index 2" isn't overly helpful but (i guess) it means the
second index that is defined on that table...

Ann may well have a better idea but the solution that comes to me is
to take the DB offline, make a file-copy of it somewhere safe and
then attempt to fix the broken index, viz. drop the FOREIGN KEY
constraint on TMSG.

Next, try to find that orphan record in TMSG and either fix it or delete it.

select primary_key, foreign_key from TMSG
where not exists (
select parent_key from parent_table
where parent_table.primary_key = TMSG.foreign_key)

Substituting, of course, the actual identifiers for the columns !!

If you want to keep the record, rather than delete it, then make sure
you have a suitable primary key value existent in the master table,
to which you can update the value of TMSG.foreign_key on the offending record.

When you're happy with things, then reapply the foreign key
constraint, and you should be back in business.

This kind of thing shouldn't happen so it would be worth inquiring
around the users or other developers as to whether someone is messing
with data using some external tool, or to find out whether someone
has been tinkering with the metadata of the keys....such practices
should be forbidden....key definitions should never be exposed to
external intervention. As a designer, avoid keys that any humans
might have reasons to meddle with.