New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Stale metadata for generator could be used [CORE6464] #2375
Comments
Modified by: @pavel-zotovAttachment: extpool-and-sequence.bat.7z [ 13530 ] |
Commented by: @dyemanov AFAIU, this has almost nothing to do with EDS and should be reproducible with two regular attachments: (1) create sequence, set it to 1 When the statement is prepared, generator name is converted to its internal ID (RDB$GENERATOR_ID) and later all GEN_IDs inside this statement read/write the generator using its ID (for performance reasons). RECREATE SEQUENCE drops the old generator and creates the new one, old ID becomes invalid and all subsequent GEN_IDs using the old ID will return zero (or maybe garbage -- not sure). EDS just preserves the statement in the prepared state if the interval is less than ExtConnPoolLifeTime, thus showing you the issue. I think the source of the issue is the lack of existence locks for generators, thus making it possible to drop the generator which is used by prepared statements -- what is usually prohibited for other objects. |
Commented by: @pavel-zotov > this has almost nothing to do with EDS and should be reproducible with two regular attachments Yes, it is so (checked on recent 3.x and 4.x):set bail on; SET KEEP_TRAN_PARAMS ON; set transaction read committed no wait; create sequence g; -- "(1) create sequence, set it to 1" select gen_id(g,0) as initial_seq_value from rdb$database; set term #; select gen_id(g,0) as final_seq_value from rdb$database;
|
Commented by: @dyemanov Your last test case is wrong because RECREATE SEQUENCE is not committed. |
Commented by: @pavel-zotov > Your last test case is wrong because RECREATE SEQUENCE is not committed. But if i use ES / WITH AUTONOMOUS TRANSACTION - result is the same as above:set bail on; SET KEEP_TRAN_PARAMS ON; set transaction read committed no wait; create sequence g; -- "(1) create sequence, set it to 1" select gen_id(g,0) as initial_seq_value from rdb$database; set transaction READ COMMITTED NO WAIT; set term #; select gen_id(g,0) as final_seq_value from rdb$database;
|
Modified by: @dyemanovVersion: 3.0.7 [ 10940 ] |
Commented by: @hvlad Please, change the title and description to show actual issue (stale metadata for generator could be used, afaiu). |
Modified by: @pavel-zotovdescription: Steps to reproduce: 1) open firebird.conf and set ExtConnPoolLifeTime to value about 5...10 (I used 5); restart FB 4.x. 2) unpack batch from attached .7z; open it in editor and adjust following variables:fbc // path to isql.exe from FB 4.x
|
Submitted by: @pavel-zotov
Attachments:
extpool-and-sequence.bat.7z
Steps to reproduce:
1) open firebird.conf and set ExtConnPoolLifeTime to value about 5...10 (I used 5); restart FB 4.x.
2) unpack batch from attached .7z; open it in editor and adjust following variables:
fbc // path to isql.exe from FB 4.x
dbnm // name of temporary database
usr // SYSDBA
psw // masterkey
This batch will create temporary database and .sql which does:
1) connect to this temp database;
2) run 'RECREATE SEQUENCE g_eds_test;'
3) creates procedure 'sp_increment_sequence' (its name speaks for itself)
4) RECONNECT to database;
5) run procedure 'sp_increment_sequence' with input arg. = 123.
6) check that final value of sequence 'g_eds_test' is 123
Then batch will parse firebird.conf (from !fbc!\ folder) and take value ExtConnPoolLifeTime from there (it must be uncommented, of course).
Finally, it calls just created .sql *three* times, with different interval between second and third: firs interval = (ExtConnPoolLifeTime + 1), second is (ExtConnPoolLifeTime - 1), i.e.:
t0 --> run #1
t1 = return from sql (i.e. finish of run #1)
.... wait for (ExtConnPoolLifeTime + 1) seconds ....
t2 = t1 + (ExtConnPoolLifeTime + 1) seconds --> run #2
t3 = return from sql (i.e. finish of run #2)
.... wait for (ExtConnPoolLifeTime - 1) seconds ....
t4 = t3 + (ExtConnPoolLifeTime - 1) seconds --> run #3
Built-in Windows utility 'timeout' is used for pausesbetween launches of isql.
Here is the content of SQL:
set bail on;
set heading off;
set list on;
connect 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey';
create or alter procedure sp_increment_sequence(a_inc int = null) as begin end;
recreate view v_check as
select cast('now' as timestamp) dts, coalesce( q'{YES, '}' || trim(g.rdb$generator_name) || q'{' exists.}', q'{NO, it doesn't}') as "Does sequence 'g_eds_test' exists ? =>"
from rdb$database r
left join rdb$generators g on upper(g.rdb$generator_name) = upper('g_eds_test');
commit;
select 'point-1' as msg, v.* from v_check v;
recreate sequence g_eds_test;
commit;
select 'point-2' as msg, v.* from v_check v;
select gen_id(g_eds_test, 0) as init_seq_value from rdb$database;
commit;
set term #;
create or alter procedure sp_increment_sequence(a_inc int = null) returns(gen_value_before_inc int, gen_value_after__inc int) as
declare c int;
begin
gen_value_before_inc = gen_id(g_eds_test,0);
c = gen_id( g_eds_test, coalesce(a_inc,1) );
gen_value_after__inc = gen_id(g_eds_test,0);
suspend;
end
#
commit
#
-- ### RECONNECT ###
connect 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey'
#
select gen_id(g_eds_test, 0) as seq_value_after_reconnect from rdb$database
#
commit
#
execute block returns(gen_value_before_inc int, gen_value_after__inc int) as
begin
execute statement ( 'select gen_value_before_inc, gen_value_after__inc from sp_increment_sequence( ? )' ) ( 123 )
on external 'localhost:' || rdb$get_context('SYSTEM','DB_NAME') as user 'SYSDBA' password 'masterkey'
-- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','') --------------------------------------------- ::::: NB :::::
into gen_value_before_inc, gen_value_after__inc
;
suspend;
end
#
set term ;#
select iif( gen_id(g_eds_test, 0) > 0, 'Expected: ' || gen_id(g_eds_test,0), 'UNEXPECTED >>> ' || gen_id(g_eds_test,0) || ' <<< ?' ) as final_seq_value from rdb$database;
commit;
drop procedure sp_increment_sequence;
drop sequence g_eds_test;
commit;
select 'point-3' as msg, v.* from v_check v;
commit;
set list off;
set heading off;
select '=== bye-bye from SQL ===' from rdb$database;
PLEASE NOTE ON COMMENTED LINE:
" -- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','') --------------------------------------------- ::::: NB :::::"
Run this batch.
This is result of batch when it runs with ExtConnPoolLifeTime = 5:
################################
MSG point-1
DTS 2021-01-07 10:30:04.5180
Does sequence 'g_eds_test' exists ? => NO, it doesn't
MSG point-2
DTS 2021-01-07 10:30:04.5290
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.
INIT_SEQ_VALUE 0
SEQ_VALUE_AFTER_RECONNECT 0
GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123
FINAL_SEQ_VALUE Expected: 123
MSG point-3
DTS 2021-01-07 10:30:05.0920
Does sequence 'g_eds_test' exists ? => NO, it doesn't
=== bye-bye from SQL ===
Take delay for 6 s, i.e. ### GREATER ### than ECP lifetime. . .
Waiting for 6 seconds, press a key to continue ...5 4 3 2 1 0
Check result after "long delay":
MSG point-1
DTS 2021-01-07 10:30:11.5640
Does sequence 'g_eds_test' exists ? => NO, it doesn't
MSG point-2
DTS 2021-01-07 10:30:11.5790
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.
INIT_SEQ_VALUE 0
SEQ_VALUE_AFTER_RECONNECT 0
GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123
FINAL_SEQ_VALUE Expected: 123
MSG point-3
DTS 2021-01-07 10:30:12.1600
Does sequence 'g_eds_test' exists ? => NO, it doesn't
=== bye-bye from SQL ===
Take delay for 4 s, i.e. ### SHORTER ### than ECP lifetime. . .
Waiting for 4 seconds, press a key to continue ...3 2 1 0
Check result after "short delay":
MSG point-1
DTS 2021-01-07 10:30:16.2370
Does sequence 'g_eds_test' exists ? => NO, it doesn't
MSG point-2
DTS 2021-01-07 10:30:16.2560
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.
INIT_SEQ_VALUE 0
SEQ_VALUE_AFTER_RECONNECT 0
GEN_VALUE_BEFORE_INC 123
GEN_VALUE_AFTER__INC 246
FINAL_SEQ_VALUE UNEXPECTED >>> 0 <<< ?
MSG point-3
DTS 2021-01-07 10:30:16.3380
Does sequence 'g_eds_test' exists ? => NO, it doesn't
=== bye-bye from SQL ===
################################
As you can see, sequence is recreated every time.
But its *OLD* value (123) still visible to procedure 'sp_increment_sequence' when it is called via ES/EDS and - most important - time from previous call is less then ExtConnPoolLifeTime.
If we uncomment line "-- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','')" and repeat than result is expected:
GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123
FINAL_SEQ_VALUE Expected: 123
(using ROLE clause with random value forces EDS mechanism to create totally new connection, AFAIK).
PS. WI-V4.0.0.2307 Firebird 4.0 Release Candidate 1
PPS.
UPD, 03.02.21. Changed title after Vlad's request.
The text was updated successfully, but these errors were encountered: