Alexander,

Many thanks, a very clear explanation. I've filed this away in case the
problem happens again.

Regards & thank again

Alexander V.Nevsky wrote:

> --- In firebird-support@yahoogroups.com, Bradley Tate <btate@o...>
> wrote:
> > > What kind of select did you do? Something like
> > >
> > > Select Count(*), ID
> > > >From This_Table
> > > Group By ID
> > > Having Count(*)>1 ?
> > >
> > > Don't forget to add Plan (This_Table Natural) to such a query.
> Most
> > > probably engine used index and this index have no link to
> duplicated
> > > row. Check this table for NULLs in PK too, again forcing natural
> scan.
> >
> > Any and all queries work on the "original" database. There is no
> > indication of any problem.
> > It is only on the restored gdb that any select returns nulls,
> regardless
> > of table (of which there are about 200)
> > Am I misunderstanding?
>
> Seems so. I speak about how to find duplicates in PK of problem
> table in original database. They are "invisible" for your normal
> queries which use index RDB$PRIMARY388 because this index is broken in
> original database. I encountered 2 different corruptions of this kind
> a) duplicates were inserted after index was broken and internal system
> trigger which checks this could'nt see existing record with the same
> value of PK
> b) null-filled records, my understanding - debris of versions which
> got status of legal record because of garbage collection failure. They
> are "invisible" for indexed search too.
>
> Gbak perform natural scan making .gbk and include such a records
> into it. On restore constraint in new database is violated and you get
> empty database. Knowing gfix don't detect errors I suspect you have
> case a) and you can find duplicates in original database by
>
> Select Count(*), ID
> >From Problem_Table
> Group By ID
> Having Count(*)>1
> Plan (Problem_Table Natural)
>
> (assuming ID is Primary Key) but recommend to check case b) too:
>
> Select *
> >From Problem_Table
> Where (ID Is Null)Or(ID=0)
> Plan (Problem_Table Natural)
>
> Sometimes such a records can be found only after performing gfix -v
> -f, gfix -m. If you'll find problem records, delete or update them you
> can only if you can formulate Where on attributes not using indexed
> columns to uniquely identify record or using rdb$db_key. When you'll
> need help on this, ask more or search archieve of this list, not long
> ago I posted stored procedure text which updates (or deletes, can't
> recall correctly) records with the same PK value using rdb$db_key.
>
> One more thing you can do is - restore gbk using -i option for gbak.
> You'll get database with data but indices will not be activised and so
> exception will not occure. In this new database you can make the same
> search and repair and after this activate indices. Note you can't
> activate system indices by Alter Index RDB$PRIMARY388 Active, but can
> by
> Update RDB$INDICES SET RDB$INDEX_INACTIVE=0
> Where RDB$INDEX_NAME='RDB$PRIMARY388'
>
> Best regards,
> Alexander.
>