Subject Re: [firebird-support] Re: DB restore and datapumping fails
Author Helen Borrie
At 03:11 AM 10/11/2007, you wrote:

>OK, I now pumped data from that table to an table without any constraints:
>and then tried
>select count(uid), uid from tab_graafik_test group by uid order by 1 desc
>and it appears that there is indeed 2 uid which aren't unique!
>==== ======= ========= ======= ======= ========
>139972 1 24 08.08.2007 26.06.2007
>139972 1 24 08.08.2007 26.06.2007
>139973 1 24 09.08.2007 26.06.2007
>139973 1 24 09.08.2007 26.06.2007
>Note that only difference is in "Changed" field.
>However, when running this query against original table (TAB_Graafik),
>there is no dublicates!

The output shown above is not the output of this query.

Do you notice also that uid 139972 and 139973 exhibit a violation of the unique constraint CU_Graafik_KuupLepVah UNIQUE(Kuupaev,Tooleping,Vahetus) that was defined for the source table?


it is all rather academic. Unfortunately, you have already corrupted the original database, considering that you used

gbak -R -user ...

gbak: ERROR: attempt to store duplicate value (visible to active
transactions) in unique index "RDB$PRIMARY40"
gbak: Exiting before completion due to errors

So gbak has gone this far:

1. It deleted the original database (that is what the -R flag does)
2. It created the new database for the restore
3. It created many of the objects, including the PK and FK constraints and their indexes, which it flagged temporarily as inactive.
4. It pushed data into the tables.
5. Then it activated the constraint indexes and started to build them.
6. It reached RDB$PRIMARY40 and encountered a PK violation when it tried to build the index on that PK, so it committed the transaction and stopped.

No further constraints or indexes would be activated. The database is left in the state where users can break any of those unprotected keys.

Somewhere between 3 and 5, as well as after the restore was committed, you had users logged in and operating on tables that had no constraints. And, judging by the timestamp values in CHANGED (above) it is not the first time you have fallen into this trap - that particular example occurred months ago.

>So it seems that data from an old transaction
>is somehow has survived, but it is visible in only in some special

Superseded data from an old transaction *does* survive on disk until those old recversions are garbage-collected. But nothing can make multiple recversions available to any transaction.
--- the transaction that creates a new recversion sees only that recversion
--- other transactions see only the last committed recversion

But that's not the issue. The issue is two-fold:

1. Using the -R switch for restoring a database
2. Allowing users to log in to a partially-restored database

>Database header page information:
> Flags 0
> Checksum 12345
> Generation 13048450
> Page size 4096
> ODS version 10.1
> Oldest transaction 13048431
> Oldest active 13048432
> Oldest snapshot 13048432
> Next transaction 13048433
> Bumped transaction 1
> Sequence number 0
> Next attachment ID 0
> Implementation ID 16
> Shadow count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Creation date Jul 5, 2006 12:07:26

This is not the header page from a recently restored database. So at least you have (apparently) a previous copy that you can fall back to. Hopefully the users won't be too upset that their recent work has been lost...I'd want to inspect ALL data carefully, even in the "last known good" copy, in case there are other constraints that have been left inactive for many months.

NEVER use gbak -R on a database that cannot be shut down.
NEVER consider a database as "shut down" if you have users that log in as SYSDBA or Owner.