Subject Re: Automatically deleting bad duplicate records
Author Adam
--- In, "Alan McDonald" <alan@m...>
> > > > Does the database have forced writes on?
> > >
> > > Forced writes was not on when corruption took place, we have
> > > switched forced writes on.
> >
> > Robert,
> >
> > You need not investigate any further. Having Forced writes
switched off
> > means that when the database writes a page, the OS can cache the
> > changes. It is possible that there was a problem (my guess power
> > between the record being written to the data page and the index
> > being updated to reflect the new record. The constraint checks
rely on
> > this index to detect whether a value exists or not. Forced writes
> > forces the changes to be persisted to disk immediately as they are
> > made. This means that the record would have been long on disk
> > the power went off.
> Adam,
> I don't think this is the most likely reason. The chance of having
> writes off, suffering a duplicate PK value as you say and not a
rather nasty
> DB corruption is so slim that I don't think it's possible.

I agree that the known bug in earlier versions is a more likely
candidate, but you would be horrified to learn just how long certain
unnamed OSes leave it before flushing their cache to disk.

In fact, according to the book, FB 1.0.x does not flush the write
cache until the service is shutdown. FB 1.5 is at worst 5 seconds
between flushes (unless you change it with Firebird.conf). But the
warning still stands. Forced writes is disabled to true for a reason.
If you disable it, understand there may be nasty surprises if your
hardware (or power) fails.

I would suggest investigating whether the PK index are active for the
simple reason of preventing it happenning again. (Was there also a
bug with generators at about that version?) If the only problem is a
duplicated value, then I think he is pretty lucky.

What may be an idea (probably for a different list for a future
enhancement) is to allow a mode for gbak that allows you to restore
without any constraints / views / SPs / index etc (tables + data

Although the database would be unusable as is, the data can be easily
recovered you could pump it to a new structure.


> in 1.0.2 you *could* deactivate both primary and foreign key
> indexes...
> I assume this was fixed but it's worth checking your version to
ensure that
> PK indexes were/are active.
> Alan