Identify missing records between identical tables

16 views
Skip to first unread message

Ertan Küçükoglu

unread,
Feb 9, 2021, 6:17:35 AMFeb 9
to firebird...@googlegroups.com
Hello,

There happened a power cut in one client database server hardware. No VM, bare metal server.

Now there are records missing in the production database in one table. I need to recover missing records from a gbak full backup file. It is not possible to restore backup and use it as production for certain reasons.

Is there an easy way/free tool to identify missing records between two database tables?

I need to identify and insert these missing records only.

Thanks & Regards,
Ertan Küçükoğlu

Dimitry Sibiryakov

unread,
Feb 9, 2021, 6:32:58 AMFeb 9
to firebird...@googlegroups.com
09.02.2021 12:17, Ertan Küçükoglu wrote:
> Is there an easy way/free tool to identify missing records between two database tables?
>
> I need to identify and insert these missing records only.

"for execute statement 'select .... ' on external data source do if not exists(....)
then insert..."

--
WBR, SD.

Mark Rotteveel

unread,
Feb 9, 2021, 7:36:29 AMFeb 9
to firebird...@googlegroups.com
On 09-02-2021 12:17, Ertan Küçükoglu wrote:
> There happened a power cut in one client database server hardware. No
> VM, bare metal server.
>
> Now there are records missing in the production database in one table. I
> need to recover missing records from a gbak full backup file. It is not
> possible to restore backup and use it as production for certain reasons.

This is a rather curious problem. A power failure should not normally
cause the loss of records that already existed in the database. Records
from an in-progress transaction might get lost, but existing records
should be preserved.

Have you tried using gfix on a copy of the affected database? How do you
know records are missing?

Mark
--
Mark Rotteveel

Ertan Küçükoglu

unread,
Feb 9, 2021, 7:38:48 AMFeb 9
to firebird...@googlegroups.com
Hello,

Thanks for the hint. I didn't know it was possible to query another firebird database.

What I failed is to "select" missing records and get them listed on screen in order to have some information in my hand to show as "these were missing". Seems like the execute statement is working like a cursor or similar or I failed to find a way to make it select records.

What I tried is something as below:

set term ^ ;
execute block
as
begin
  execute statement 'select * from X' on external data source 'localhost:E:\DBs\backup\DB_B.FDB' as user 'SYSDBA' password 'masterkey';
end^
set term ; ^

That gives "Output parameters mismatch" error.

Thanks & Regards,
Ertan Küçükoğlu


Dimitry Sibiryakov <s...@ibphoenix.com>, 9 Şub 2021 Sal, 14:32 tarihinde şunu yazdı:
--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/c34be4de-2385-0632-8fc7-16b296b7393e%40ibphoenix.com.

Mark Rotteveel

unread,
Feb 9, 2021, 7:48:18 AMFeb 9
to firebird...@googlegroups.com
On 09-02-2021 13:38, Ertan Küçükoglu wrote:
> Hello,
>
> Thanks for the hint. I didn't know it was possible to query another
> firebird database.
>
> What I failed is to "select" missing records and get them listed on
> screen in order to have some information in my hand to show as "these
> were missing". Seems like the execute statement is working like a cursor
> or similar or I failed to find a way to make it select records.
>
> What I tried is something as below:
>
> set term ^ ;
> execute block
> as
> begin
>   execute statement 'select * from X' on external data source
> 'localhost:E:\DBs\backup\DB_B.FDB' as user 'SYSDBA' password 'masterkey';
> end^
> set term ; ^
>
> That gives "Output parameters mismatch" error.

That is because you need to have return parameters (or local variables)
to accept the values of a row. Furthermore, you need to use FOR EXECUTE
STATEMENT.

So, say `X` has three columns, you need to do something like

execute block returns (id integer, col2 varchar(100), col3 varchar(200))
as
begin
for execute statement 'select id, col2, col3 from X'
on external data source [...] /* removed for brevity */
into id, col2, col3 do
begin
suspend;
end
end

Or, more in line with what Dimitry suggested:

execute block
as
declare id integer;
declare col2 varchar(100);
declare col3 varchar(100);
begin
for execute statement 'select id, col2, col3 from X'
on external data source [...] /* removed for brevity */
into id, col2, col3 do
begin
if (not exists(select * from X where id = :id)) then
begin
insert into x (id, col2, col3) values (:id, :col2, :col3);
end
end
end

Mark
--
Mark Rotteveel

Ertan Küçükoglu

unread,
Feb 9, 2021, 7:48:41 AMFeb 9
to firebird...@googlegroups.com
Hello,

I thought it was a power failure by looking at Windows event log. However, I am told there was no power loss. It is obvious the server is rebooted using hard reset or something like that.

I am not sure how to use gfix on such a database. I tried following 

gfix .\DB_B.FDB -user SYSDBA -password masterkey -validate -no_update -full
Summary of validation errors

        Number of record level errors   : 5

We are informed that there are missing records by the customer. Compared to backup, there are about half of the records missing in that table (38213 in backup and 16491 in production database). This is not the first time we faced that situation. I am told that what I should look for is a human error at that time. So far I could not identify what really happened in all of these cases.

Thanks & Regards,
Ertan Küçükoğlu


Mark Rotteveel <ma...@lawinegevaar.nl>, 9 Şub 2021 Sal, 15:36 tarihinde şunu yazdı:
--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

Karol Bieniaszewski

unread,
Feb 9, 2021, 8:41:06 AMFeb 9
to firebird...@googlegroups.com

In such cases i first identify an hour when this happened. Then i can compare it with working hours or e.g. cleaning service hours 😉

 

To fix database, first look into Firebird.log and look what errors was written after your gfix command execution.

As gfix report all errors into Firebird.log.

 

Regards,

Karol Bieniaszewski

Ertan Küçükoglu

unread,
Feb 10, 2021, 8:12:25 AMFeb 10
to firebird...@googlegroups.com
I just had a chance to check the firebird log file. Below are the relevant lines for the problematic table.

DESKTOP-ERTAN (Server) Wed Feb 10 16:06:45 2021
Database: E:\DBS\DB_B.FDB
Relation has 108 orphan backversions (12875 in use) in table X (131)

I really have no idea what these log lines mean.

BTW, I could recover missing records using Dmirtry and Mark replies. Thanks for that.

However, I am eager to identify the cause of that problem as I increasingly face it over and over again.

Thanks & Regards,
Ertan Küçükoğlu


Karol Bieniaszewski <livius...@poczta.onet.pl>, 9 Şub 2021 Sal, 16:41 tarihinde şunu yazdı:

Dimitry Sibiryakov

unread,
Feb 10, 2021, 8:47:24 AMFeb 10
to firebird...@googlegroups.com
10.02.2021 14:12, Ertan Küçükoglu wrote:
> However, I am eager to identify the cause of that problem as I increasingly face it over
> and over again.

Check that the database has forced writes ON.

--
WBR, SD.

Ertan Küçükoglu

unread,
Feb 10, 2021, 12:05:39 PMFeb 10
to firebird...@googlegroups.com
I have found how to change forced writes mode.

gfix -mo[de] {read_write | read_only} db_name

I could not find how to see the current state of forced writes despite my searching.

Some help is appreciated.

Thanks & Regards,
Ertan Küçükoğlu


Dimitry Sibiryakov <s...@ibphoenix.com>, 10 Şub 2021 Çar, 16:47 tarihinde şunu yazdı:
--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

Dimitry Sibiryakov

unread,
Feb 10, 2021, 12:08:24 PMFeb 10
to firebird...@googlegroups.com
10.02.2021 18:05, Ertan Küçükoglu wrote:
> I have found how to change forced writes mode.
>
> gfix -mo[de] {read_write | read_only} db_name

This command set read-only mode, not forced writes. It is set with -sync/-async.

> I could not find how to see the current state of forced writes despite my searching.

gstat -h.

--
WBR, SD.

Ertan Küçükoglu

unread,
Feb 10, 2021, 1:52:52 PMFeb 10
to firebird...@googlegroups.com
PS E:\DBs> gstat -h '.\DB_B.FDB'

Database ".\DB_B.FDB"
Gstat execution time Wed Feb 10 21:50:27 2021

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              15518902
        Page size               4096
        ODS version             11.2
        Oldest transaction      15514895
        Oldest active           15518031
        Oldest snapshot         15518031
        Next transaction        15518031
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      167639
        Implementation ID       16
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Jun 5, 2018 15:59:11
        Attributes              force write

    Variable header data:
        Sweep interval:         20000
        *END*
PS E:\DBs>

It seems force write is turned on.

Dimitry Sibiryakov <s...@ibphoenix.com>, 10 Şub 2021 Çar, 20:08 tarihinde şunu yazdı:
--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages