At 07:36 PM 7/04/2009, you wrote:

>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 here about indices that can't be dropped...did you drop the three constraints? or did you do some operation on 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?

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

>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 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.

>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. 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.

>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.

>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.

./heLen