Error "Too many recursion levels" appears infinitely in firebird.log when on-disconnect trigger has program error (e.g. creates new connection using EDS etc).
Terminating of application does not stop this process, server needs to be restarted.
This behaviour seems to be regression in comparing to FB 3.x.
This is example:
set bail on;
shell if exist c:\temp\tmp4test.fdb del c:\temp\tmp4test.fdb ;
create database 'localhost:c:\temp\tmp4test.fdb' user 'SYSDBA' password 'masterkey';
show version;
recreate table log( att bigint default current_connection, event_name varchar(6) );
set term ^;
create or alter trigger trg_attach on connect as
begin
in autonomous transaction do
insert into log(event_name) values ('attach');
end
^
create or alter trigger trg_detach on disconnect as
begin
execute statement ('insert into log(event_name) values(?)') ('detach')
with autonomous transaction
on external 'localhost:' || rdb$get_context('SYSTEM', 'DB_NAME')
as user 'SYSDBA' password 'masterkey' role 'R' || replace(uuid_to_char(gen_uuid()),'-','')
;
end
^
set term ;^
commit;
connect 'c:\temp\tmp4test.fdb' user 'SYSDBA' password 'masterkey';
set echo on;
show trigger;
quit;
Execution of this script leads to following:
1) 2.5.9.27156 - issues 'show trigger' output and returns to OS without any error; firebird.log also has no any error (and it is expected for this Firebird version);
2) 3.0.12.33740 - the same as 2.5.x but firebird.log will have TWO messages:
PZ Wed Apr 10 13:14:47 2024
Database: C:\TEMP\TMP4TEST.FDB
Error at disconnect:
Execute statement error at attach :
335544830 : Too many recursion levels of EXECUTE STATEMENT
Data source : Firebird::localhost:C:\TEMP\TMP4TEST.FDB
At trigger 'TRG_DETACH' line: 3, col: 5
PZ Wed Apr 10 13:14:48 2024
Database: C:\TEMP\TMP4TEST.FDB
Error at disconnect:
Execute statement error at attach :
335544344 : I/O error during "CreateFile (open)" operation for file "C:\TEMP\TMP4TEST.FDB"
335544734 : Error while trying to open file
36183911 : process cannot access the file because it is being used by another process
Data source : Firebird::localhost:C:\TEMP\TMP4TEST.FDB
At trigger 'TRG_DETACH' line: 3, col: 5
No new messages will appear after that in log.
3) 4.0.5.3080, 5.0.1.1371, 6.0.0.273 -- all of them do NOT issue 'show trigger' and do not return control to OS (i.e. ISQL looks like 'hanging').
Interruption of ISQL works OK (i.e. will cancel ISQL process and return control to OS) but firebird.log will be infinitely filled with messages:
PZ Wed Apr 10 13:23:11 2024
Database: C:\TEMP\TMP4TEST.FDB
Error at disconnect:
Execute statement error at attach :
335544830 : Too many recursion levels of EXECUTE STATEMENT
Data source : Firebird::localhost:C:\TEMP\TMP4TEST.FDB
At trigger 'TRG_DETACH' line: 3, col: 5
Only restart of FB service will terminate this.
PS.
If we run several times (during ISQL 'hanging'):
fb_lock_print -c -d C:\temp\TMP4TEST.FDB | findstr /i /c:"owners ("
-- then about 50 owners can be seen for FB 4.x and 57...63 owners for FB 5.x and 6.x (checked on SS in all cases).
Error "Too many recursion levels" appears infinitely in firebird.log when on-disconnect trigger has program error (e.g. creates new connection using EDS etc). Terminating of application does not stop this process, server needs to be restarted. This behaviour seems to be regression in comparing to FB 3.x.
This is example:
Execution of this script leads to following: 1) 2.5.9.27156 - issues 'show trigger' output and returns to OS without any error; firebird.log also has no any error (and it is expected for this Firebird version); 2) 3.0.12.33740 - the same as 2.5.x but firebird.log will have TWO messages:
No new messages will appear after that in log.
3) 4.0.5.3080, 5.0.1.1371, 6.0.0.273 -- all of them do NOT issue 'show trigger' and do not return control to OS (i.e. ISQL looks like 'hanging'). Interruption of ISQL works OK (i.e. will cancel ISQL process and return control to OS) but firebird.log will be infinitely filled with messages:
Only restart of FB service will terminate this.
PS. If we run several times (during ISQL 'hanging'):
fb_lock_print -c -d C:\temp\TMP4TEST.FDB | findstr /i /c:"owners ("
-- then about 50 owners can be seen for FB 4.x and 57...63 owners for FB 5.x and 6.x (checked on SS in all cases).