--- 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"("FACKTIMESTAMP", "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