Hello Helen - hope all is well with you.

>We have experienced out first database corruption from any of our customers
>in 15 years of Interbase and Firebird!
>
>I would like some clues as to what may have caused it.
>
>FB 1.5.3, windows server 2003, superserver
>
>The databases appeared to backup without problem. On restore they would
>fall over with 'string truncation . . . ' error on 2 foreign keys and
>violation of not null constraint on a primary index. Dropping and

replacing

>the indexes didn't fix it

/What did you do to "drop and replace the indexes", since we are talking
/where about indices that can't be dropped...did you drop the three
/constraints? or did you do some operation on the system tables?

Sorry for the sloppy wording - I dropped the constraints which dropped the
indices that went with them. Didn't touch the system tables!

>and there where no null values in the table with the primary key problem.

/Were the two FKs referencing the same (problem) PK?
no

/How were the FKs defined, viz., what ON UPDATE/ON DELETE actions?
NO ACTION/CASCADE

>I forgot about GFIX having never had to use it before so I have no idea if
>it would have helped.

/gfix has several tools for validating a database. "Would have helped"
/implies you think it won't help now - maybe just because you were able to
/fix things by the knife-and-fork method. If the same problem rears again,
/it would be well worth taking a **copy** of the database (not the backup)
/and running progressively through the gfix/gbak utility options. If you
/have The Firebird Book, there's an entire Appendix walking you through it.
/Otherwise, there's a HowTo here:

/http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_db_corr
I do have a copy of the databases so I shall do this anyway and learn ;-)

>I was able to fix the databases by backing up, restoring with 'deactivate
>indexes' and 'don't enforce validity conditions', then running a small
>program to activate the indices one at a time. When it reached a bad one

it

>reported it so I could then remove it and use the program again until all
>good ones where active. I then reinstated the removed indexes and was able
>to backup and restore normally.

/Well, you'll know yourself now which indices were affected.
Yep

>At first I thought this might have caused by ill advised windows copying of
>the live database files which may have been locking mid way through index
>pages ( quite prepared to admit I could be talking rubbish here ) but the
>strange thing is that there are 2 databases with identical structures and
>different data. However since one primary key and 2 foreign keys where
>commonly corrupted in both databases it seems a less likely candidate (and

I

>have no evidence copying happens anyway).

/I still don't think your report is all that clear about whether you had
/broken indices or broken constraints, originally.
Looking at the ibexpert view of a FK it shows constraint name and index
name, it was those indexes that appeared corrupt. (Fell over on restore and
couldn't be activated) Their names appeared in my list of inactive indexes
(taken from RDB$INDICES where TDB$INDEX_INACTIVE=1). When an index failed to
activate (string truncation etc) I would drop the related constraint, only
re-creating it after all the other indexes were active.

/And you seem to be leaving an assumption that, after all this activity with
/knives and forks, you were able to perform a completely normal backup and
/restore afterwards.
It looked perfectly normal - could I ask for more?

>Another thought was updating database structures using the scripts created
>by ib_expert database comparer. However one of the indexes corrupted in
>both databases hasn't been changed for many years. Whether this could have
>been cross contaminated by something else I have no idea. There is no
>access to files for the virus checker, there has been no messing with

system

>tables (other than a few tricks in the database comparer script) and there
>is plenty of spare disk space.
>
>Any idea what could have happened to the indexes?

/I don't know about this "database comparer script" - are you able to see
/it, or does it run behind closed doors? The problem *smells like*
/something nefarious being done to a system table.
I didn't create or run the script myself but I shall take a good look at
the next few. We've been using them for years without any problem and they
don't normally access the system tables, only for things like changing
the fields order or occasionally changing a varchar size (not in this
case!)

>Would GFIX have made the fix easier?

/It might have, provided you followed the sequence closely. But, as I
/already mentioned, it's not very clear what was broken, what you fixed, or
/precisely what you did to fix it. The validation report in firebird.log
/could have been helpful but it's possibly not something gfix could have
/done anything about: it does appear to have involved corrupt (or
/invalidated) data/busted referential logic rather than broken structure.
I don't understand how these thing are built so cant distinguish
between logic and structure. The strangest thing was that dropping
and recreating the constraints in the broken database did not allow a
restore after backup - especially as I assume this rebuilt the logic and
the structure.

Thanks Helen I shall poke around as per your comments.
Regards
Paul