firebird-support - Odp: [firebird-support] Restore errors

Subject	Odp: [firebird-support] Restore errors - missing FK
Author	liviuslivius@poczta.onet.pl
Post date	2014-07-01T04:55:22Z

Hi,

There was two differences.
1. Your query return whole dataset my only missed ent.
2. I add +0 to avoid index usage your query use index in join and that index can be corrupted

But empty result say somethink different. Try follow Sean questions.

Regards,
Karol Bieniaszewski

Wysłane z mojego HTC

----- Reply message -----
Od: "'Bob Murdoch' mailgroups@... [firebird-support]" <firebird-support@yahoogroups.com>
Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] Restore errors - missing FK
Data: wt., lip 1, 2014 02:53

Thank you Karol for your post.

However, your query did not yield any results either. I’m not sure what the difference would have been between using the left join and the subselect, but I’ll take any advice right now.

Thank you,

Bob M..

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Monday, June 30, 2014 1:23 AM
To: firebird-support@yahoogroups.com
Subject: Odp: [firebird-support] Restore errors - missing FK

Hi,

Your query is not good for index error
Try
Select * from zip_code_account z where not exists(select * from account a where a.account_id + 0 = z.account_id)

This will be slow because it not use index but give you the answer

Regards,
Karol Bieniaszewski

I received an error during a restore process today, where multiple FKs could not be restored. The restore log messages look like this:

gbak:cannot commit index FK_ZIP_CODE_ACCT_TO_ACCOUNT

gbak: ERROR:violation of FOREIGN KEY constraint "FK_ZIP_CODE_ACCT_TO_ACCOUNT" on table "ZIP_CODE_ACCOUNT"

gbak: ERROR: Foreign key reference target does not exist

gbak:cannot commit index FK_TRANSFER_TO_ACCOUNT

gbak: ERROR:violation of FOREIGN KEY constraint "FK_TRANSFER_TO_ACCOUNT" on table "TRANSFER"

gbak: ERROR: Foreign key reference target does not exist

All told there were about 23 foreign keys which couldn’t be restored – almost the exact number of foreign keys pointing to the ACCOUNT table.

It looks to me like somehow an ACCOUNT row was deleted, breaking the FK. I ran a number of queries that look like this:

select

t.account_id, a.account_id

from

zip_code_account t

left join account a on (a.account_id = t.account_id)

So that I could look for any record in ZIP_CODE_ACCOUNT that did not have a matching record in ACCOUNT. There were no records that matched this condition. I tried it on a number of other tables as well, and could not find one instance where a value in table T.ACCOUNT_ID did not have a matching value in A.ACCOUNT_ID.

What would have caused this restore to fail?

Thank you,

Bob M..