Re: [Firebird-devel] Replication - generators' value are not replicated
Hi All, just for the archive, finally Dmitry and my colleague Gábor find out what was the problem and it is solved now: https://github.com/FirebirdSQL/firebird/issues/6848 Thank you both! András -Original Message- From: Omacht András [mailto:omacht.and...@libra.hu] Sent: Tuesday, May 18, 2021 8:32 PM To: For discussion among Firebird Developers Subject: Re: [Firebird-devel] Replication - generators' value are not replicated Hi Dimitry, unfortunately, this is not that case. It seems this happen when we read data from an another database and insert/update into the master one. This is an execute block running for execute statement on external and the target table has the trigger which increases the generator Both generators are used this way only at after insert/update triggers on different tables. Maybe tomorrow whe could figure out what is wrong and can make a simple test case. UUID is not playing for us, to much table and business logic code to refactor it. András -Original Message- From: Dimitry Sibiryakov [mailto:s...@ibphoenix.com] Sent: Tuesday, May 18, 2021 4:00 PM To: For discussion among Firebird Developers Subject: Re: [Firebird-devel] Replication - generators' value are not replicated 18.05.2021 14:45, Omacht András wrote: > We have very complex and long running tests. > We ran the simpliest, which creates hundreds of domains, tables, stored > procedures, views, triggers and inserting thousands data into tables. > Almost everything is ok with replication, we only have two generators that > end in different values in the replicas (they are different from the master, > they remain in the original state in the replicas). I can foresee only one case when it is possible: generators are increased in one connection and received values used in another. PS: I suggest to use UUIDs for primary keys instead of generators. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
2021.06.08. 10:11 keltezéssel, Dmitry Yemanov írta: 08.06.2021 11:03, Gabor Boros wrote: I can send (to who and which address?) a test case privately which demonstrate the problem. The generator's new value used at insert, the new record replicated but the generator's value not. Please send it to me: firebi...@yandex.ru Dmitry Done. :-) Gabor Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
08.06.2021 11:03, Gabor Boros wrote: I can send (to who and which address?) a test case privately which demonstrate the problem. The generator's new value used at insert, the new record replicated but the generator's value not. Please send it to me: firebi...@yandex.ru Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
2021.05.18. 13:21 keltezéssel, Dmitry Yemanov írta: This is "as designed". Generators are replicated if you made some modifications and commit them, in other words if you had a chance to store the newly generated values in some tables. Hi Dmitry and All, I can send (to who and which address?) a test case privately which demonstrate the problem. The generator's new value used at insert, the new record replicated but the generator's value not. Gabor Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
Hi Dimitry, unfortunately, this is not that case. It seems this happen when we read data from an another database and insert/update into the master one. This is an execute block running for execute statement on external and the target table has the trigger which increases the generator Both generators are used this way only at after insert/update triggers on different tables. Maybe tomorrow whe could figure out what is wrong and can make a simple test case. UUID is not playing for us, to much table and business logic code to refactor it. András -Original Message- From: Dimitry Sibiryakov [mailto:s...@ibphoenix.com] Sent: Tuesday, May 18, 2021 4:00 PM To: For discussion among Firebird Developers Subject: Re: [Firebird-devel] Replication - generators' value are not replicated 18.05.2021 14:45, Omacht András wrote: > We have very complex and long running tests. > We ran the simpliest, which creates hundreds of domains, tables, stored > procedures, views, triggers and inserting thousands data into tables. > Almost everything is ok with replication, we only have two generators that > end in different values in the replicas (they are different from the master, > they remain in the original state in the replicas). I can foresee only one case when it is possible: generators are increased in one connection and received values used in another. PS: I suggest to use UUIDs for primary keys instead of generators. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
18.05.2021 14:45, Omacht András wrote: We have very complex and long running tests. We ran the simpliest, which creates hundreds of domains, tables, stored procedures, views, triggers and inserting thousands data into tables. Almost everything is ok with replication, we only have two generators that end in different values in the replicas (they are different from the master, they remain in the original state in the replicas). I can foresee only one case when it is possible: generators are increased in one connection and received values used in another. PS: I suggest to use UUIDs for primary keys instead of generators. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
Dmitry, sorry, you are right, I oversimplified the case.
It works well If I create primary key.
We have very complex and long running tests.
We ran the simpliest, which creates hundreds of domains, tables, stored
procedures, views, triggers and inserting thousands data into tables.
Almost everything is ok with replication, we only have two generators that end
in different values in the replicas (they are different from the master, they
remain in the original state in the replicas).
All transactions are commited in the process, it is 100% sure and tables have
primary keys and the replication log shows no error.
My colleagues and I are trying to find the cause ...
Sorry for the blind noise.
András
-Original Message-
From: Dmitry Yemanov [mailto:firebi...@yandex.ru]
Sent: Tuesday, May 18, 2021 1:46 PM
To: For discussion among Firebird Developers
Subject: Re: [Firebird-devel] Replication - generators' value are not replicated
18.05.2021 14:36, Omacht András wrote:
> Ok, let's see the following example:
>
> Master:
> CREATE SEQUENCE TMP_GEN_2;
> commit;
>
> CREATE TABLE TMP_TABLE (
> VC VARCHAR(10)
> );
> commit;
>
> CREATE OR ALTER TRIGGER TMP_TABLE_BIU0 FOR TMP_TABLE ACTIVE BEFORE
> INSERT OR UPDATE POSITION 0 as declare variable dummy integer; begin
>dummy = gen_id(tmp_gen_2, 1);
> end
> commit;
>
> insert into TMP_TABLE (VC) values ('A'); commit;
> -- At this point in master and replica's generator has the same value:
> 1
>
> update tmp_table set vc = 'B';
> commit;
> -- At this point master's generator value is 2, but the replica's generator
> value is 1.
What you should get is a "Table TMP_TABLE has no unique key" error while
committing the UPDATE statement.
Dmitry
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
18.05.2021 13:45, Dmitry Yemanov wrote: What you should get is a "Table TMP_TABLE has no unique key" error while committing the UPDATE statement. If reporting of replication errors had been enabled by default... -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
18.05.2021 13:47, Omacht András wrote: It looks like when I do update and commit then the generator's value is not syncronized. As Dmitry wrote above it is not synchronized because of replication error. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
Just one more info.
After this if I do
insert into TMP_TABLE (VC) values ('C');
commit;
the generator value is ok again. Both in master and replica has 3.
It looks like when I do update and commit then the generator's value is not
syncronized.
András
-Original Message-
From: Omacht András [mailto:omacht.and...@libra.hu]
Sent: Tuesday, May 18, 2021 1:36 PM
To: For discussion among Firebird Developers
Subject: Re: [Firebird-devel] Replication - generators' value are not replicated
Ok, let's see the following example:
Master:
CREATE SEQUENCE TMP_GEN_2;
commit;
CREATE TABLE TMP_TABLE (
VC VARCHAR(10)
);
commit;
CREATE OR ALTER TRIGGER TMP_TABLE_BIU0 FOR TMP_TABLE ACTIVE BEFORE INSERT OR
UPDATE POSITION 0 as declare variable dummy integer; begin
dummy = gen_id(tmp_gen_2, 1);
end
commit;
insert into TMP_TABLE (VC) values ('A'); commit;
-- At this point in master and replica's generator has the same value: 1
update tmp_table set vc = 'B';
commit;
-- At this point master's generator value is 2, but the replica's generator
value is 1.
The last update is a commited DML...
CÉGÜNK A LIBRA CSOPORT TAGJA
OMACHT ANDRÁS
fejlesztési igazgató
LIBRA Szoftver Zrt.
1113 Budapest, Karolina út 65.
+36 (1) 255-3939
i...@libra.hu | www.libra.hu
-Original Message-
From: Dimitry Sibiryakov [mailto:s...@ibphoenix.com]
Sent: Tuesday, May 18, 2021 1:23 PM
To: For discussion among Firebird Developers
Subject: Re: [Firebird-devel] Replication - generators' value are not replicated
18.05.2021 13:20, Omacht András wrote:
> I commited, as shown in my example.
Transaction also must contain some DML. Without it replication of generator
is pointless.
--
WBR, SD.
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
18.05.2021 14:36, Omacht András wrote:
Ok, let's see the following example:
Master:
CREATE SEQUENCE TMP_GEN_2;
commit;
CREATE TABLE TMP_TABLE (
VC VARCHAR(10)
);
commit;
CREATE OR ALTER TRIGGER TMP_TABLE_BIU0 FOR TMP_TABLE
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
declare variable dummy integer;
begin
dummy = gen_id(tmp_gen_2, 1);
end
commit;
insert into TMP_TABLE (VC) values ('A');
commit;
-- At this point in master and replica's generator has the same value: 1
update tmp_table set vc = 'B';
commit;
-- At this point master's generator value is 2, but the replica's generator
value is 1.
What you should get is a "Table TMP_TABLE has no unique key" error while
committing the UPDATE statement.
Dmitry
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
Ok, let's see the following example:
Master:
CREATE SEQUENCE TMP_GEN_2;
commit;
CREATE TABLE TMP_TABLE (
VC VARCHAR(10)
);
commit;
CREATE OR ALTER TRIGGER TMP_TABLE_BIU0 FOR TMP_TABLE
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
declare variable dummy integer;
begin
dummy = gen_id(tmp_gen_2, 1);
end
commit;
insert into TMP_TABLE (VC) values ('A');
commit;
-- At this point in master and replica's generator has the same value: 1
update tmp_table set vc = 'B';
commit;
-- At this point master's generator value is 2, but the replica's generator
value is 1.
The last update is a commited DML...
CÉGÜNK A LIBRA CSOPORT TAGJA
OMACHT ANDRÁS
fejlesztési igazgató
LIBRA Szoftver Zrt.
1113 Budapest, Karolina út 65.
+36 (1) 255-3939
i...@libra.hu | www.libra.hu
-Original Message-
From: Dimitry Sibiryakov [mailto:s...@ibphoenix.com]
Sent: Tuesday, May 18, 2021 1:23 PM
To: For discussion among Firebird Developers
Subject: Re: [Firebird-devel] Replication - generators' value are not replicated
18.05.2021 13:20, Omacht András wrote:
> I commited, as shown in my example.
Transaction also must contain some DML. Without it replication of generator
is pointless.
--
WBR, SD.
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
18.05.2021 13:20, Omacht András wrote: I commited, as shown in my example. Transaction also must contain some DML. Without it replication of generator is pointless. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
18.05.2021 13:52, Omacht András wrote: FB4 - 4.0.0.2489 We tried the following in both (sync, async) modes: Master: CREATE SEQUENCE TMP_GEN_1; commit; (The sequence was created fine in replicas.) Master: select gen_id(tmp_gen_1, 1) from rdb$database; -- Result: 1 - OK commit; select gen_id(tmp_gen_1, 0) from rdb$database; -- Result: 1 – OK Replica: select gen_id(tmp_gen_1, 0) from rdb$database; -- Result: 0 – This should be 1. Can someone confirm if this is a bug? This is "as designed". Generators are replicated if you made some modifications and commit them, in other words if you had a chance to store the newly generated values in some tables. Otherwise (changes were rolled back or transaction is de facto read-only, as in your example), generators are not replicated. IMO, it doesn't make much sense in real life, because the new value is not utilized anyway. Moreover, replicating them unconditionally would negatively affect the performance (if they would be replicated immediately, increment by increment, instead of replicating only the last value as it's done now). Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
Hi Dimitry, I commited, as shown in my example. Master: select gen_id(tmp_gen_1, 1) from rdb$database; -- Result: 1 - OK commit; (In my opinion, replication should work for rollback anyway.) CÉGÜNK A LIBRA CSOPORT TAGJA OMACHT ANDRÁS fejlesztési igazgató LIBRA Szoftver Zrt. 1113 Budapest, Karolina út 65. +36 (1) 255-3939 i...@libra.hu | www.libra.hu -Original Message- From: Dimitry Sibiryakov [mailto:s...@ibphoenix.com] Sent: Tuesday, May 18, 2021 1:16 PM To: For discussion among Firebird Developers Subject: Re: [Firebird-devel] Replication - generators' value are not replicated 18.05.2021 12:52, Omacht András wrote: > Can someone confirm if this is a bug? No, this is by design. You must commit transaction that changed sequence on master side. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Replication - generators' value are not replicated
18.05.2021 12:52, Omacht András wrote: Can someone confirm if this is a bug? No, this is by design. You must commit transaction that changed sequence on master side. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
