Subject Re: DB restore and datapumping fails
Author ainpoissee
> >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
> >11:12:00
> >139972 1 24 08.08.2007 26.06.2007
> >11:16:45
> >139973 1 24 09.08.2007 26.06.2007
> >11:12:04
> >139973 1 24 09.08.2007 26.06.2007
> >11:16:49
> >
> >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.

Yes, the query returned
2 139972
2 139973
1 ....
and I then executed
select * from tab_graafik_test where uid=139972 or uid=139973
to get the table I posted... I skipped this part in my original post.

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

I tried all sorts of other things too, ie does the "Vahetus" or
"Tooleping" column contain NULL or a value which doesn't exist and so
on... all those queries give me false (in the sense that all data
seemed to be OK, none of the constraints violated).

> it is all rather academic. Unfortunately, you have already
corrupted the original database, considering that you used
> gbak -R -user ...

Actually, I didn't corrupt the original database. I do have copy of it
in a safe place :)
I did use -R as it is actually a customers database, they had a
problem restoring it and so they sent me a copy of it. I tried to
restore it as my test DB, replacing current one...
They sent me both fdb and fbk, so thats why I were able to look into
sys tables, extract header page etc. Restoring from fbk fails, but
connecting and quering fdb seems to work OK. However, since restoring
form backup fails, there is obviously something wrong with it so I'm
trying to pump data over into "fresh DB".

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

No, as explained above, I do use that DB in my test environment where
I'm the only user.
Now the fact that the bad data is several months old suggest to me
that there is some obscure bug in FB which caused this - as I know
that they haven't done any upgrades at least 6 months! BTW the
customer uses 1.5 classic on win.

> 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

As explained none of those are actually issues in this case - 1
because I knowingly did this to restore it over my test DB and 2 as
there wasn't anyone logged in.

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

Yes, thats a header from the fdb they sent me.

BTW, as a last thing on friday I executed
delete from tab_graafik where uid=139972 or uid=139973
and then tried to pump data to fresh DB. And this time it succseeded!
I haven't had time to verify that everithing is really OK with data
(will do on monday), but I had a quick look and it seems that there is
records with those offending uids (139972 and 139973) in new dB! So it
seems that the delete command deleted those offending back versions of
those records...