Hi Mark,

These symptoms I already have about 3 months ago.
Also I am having problem of corruption of the data base almost that daily.
And I am not understanding what it can be happening.

Adriano Wolff
ADML Software

----- Mensagem original ----
De: mark_gebauer <mark.gebauer@...>
Para: firebird-support@yahoogroups.com
Enviadas: Terça-feira, 17 de Abril de 2007 17:13:20
Assunto: [firebird-support] Re: Firebird 2.0.1: Database corrupt under high load CPU load

--- In firebird-support@ yahoogroups. com, Helen Borrie <helebor@... > wrote:
>...
>
> 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.
>
> ./heLen
>

Hello,

thank you for your answer. There is no foreign key or primary key
defined on that table, just indices. The index definitions:

CREATE INDEX "IMSG" ON "TMSG"("FPRIORITY" , "FTIMESTAMP" , "FTIMESTAMPMS" );
CREATE UNIQUE INDEX "IMSGGUID" ON "TMSG"("FGUID" );
CREATE INDEX "IMSGSMDEL" ON "TMSG"("FMSG" , "FRECEIVER") ;
CREATE INDEX "IMSGTIME" ON "TMSG"("FTIMESTAMP" , "FTIMESTAMPMS" );
CREATE INDEX "ITMSGQUERY" ON "TMSG"("FACKTIMESTA MP", "FRECEIVER",
"FSENDER", "FMSG");
CREATE INDEX "ITMSGQUEUE_ READY" ON "TMSG"("FSENDER" , "FACKTIMESTAMP" );
CREATE UNIQUE INDEX "ITMSG_GENID" ON "TMSG"("FGENID" );

...so I guess "Index 2" is:
CREATE UNIQUE INDEX "IMSGGUID" ON "TMSG"("FGUID" );

It's unique, and the GUIDs are generated by the Windows API and
then converted to strings, so they should be unique anyway.

The table definition is:

CREATE TABLE "TMSG"
(
"FGENID" NUMERIC(18, 0) NOT NULL,
"FGUID" VARCHAR(38) CHARACTER SET ISO8859_1 NOT NULL,
"FTIMESTAMP" TIMESTAMP NOT NULL,
"FTIMESTAMPMS" SMALLINT NOT NULL,
"FSENDER" VARCHAR(64) CHARACTER SET ISO8859_1 NOT NULL,
"FRECEIVER" VARCHAR(64) CHARACTER SET ISO8859_1 NOT NULL,
"FMSG" VARCHAR(64) CHARACTER SET ISO8859_1 NOT NULL,
"FPRIORITY" SMALLINT NOT NULL,
"FVALUE1" VARCHAR(1024) CHARACTER SET ISO8859_1,
"FVALUE2" VARCHAR(1024) CHARACTER SET ISO8859_1,
"FVALUE3" VARCHAR(1024) CHARACTER SET ISO8859_1,
"FVALUE4" VARCHAR(1024) CHARACTER SET ISO8859_1,
"FVALUE5" VARCHAR(1024) CHARACTER SET ISO8859_1,
"FTIMEOUTSEC" INTEGER NOT NULL,
"FACKTIMESTAMP" TIMESTAMP,
"FACKTIMESTAMPMS" SMALLINT
);

...and FGENID will be filled by a trigger:

CREATE TRIGGER "TR_TMSG" FOR "TMSG"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
TMSG.FGENID = GEN_ID(GMSG, 1);
END

The FB clients are using IBExpress/Delphi 2006 with the latest
Firebird Client DLLs. Maybe there are any known issues?

What other actions or constellations could cause this?
My DB design?
High cpu load?
"Zombie" client connections (caused by blocking client PCs
or network issues?
Long lasting transactions?
Hardware issues?

Thanks in advance!
Mark




__________________________________________________
Fale com seus amigos de graça com o novo Yahoo! Messenger
http://br.messenger.yahoo.com/

[Non-text portions of this message have been removed]