Identify missing records between identical tables

[Ertan Küçükoglu]

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]

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]

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]

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]

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]

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.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/31292e3e-7507-b5b2-ba23-980ece6b87ed%40lawinegevaar.nl.

[Karol Bieniaszewski]

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

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAH2i4ye_Nngxju8Bb60dGPC4T9Ac76FDHcs7s%2BEDURrUPPH8xg%40mail.gmail.com.

 

[Ertan Küçükoglu]

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ı:

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4DZkYk5LWHz1svS%40smtp.poczta.onet.pl.

[Dimitry Sibiryakov]

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]

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.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/8abd4b93-8619-436c-b9e4-ee77b516c72f%40ibphoenix.com.

[Dimitry Sibiryakov]

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]

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.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/6756a2d9-00c5-f136-b9ee-c77b52244785%40ibphoenix.com.