Skip to content
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

Crash on 3.0 and 4.0 when DB contains database-level trigger [CORE5533] #5801

Closed
firebird-issue-importer opened this issue May 8, 2017 · 10 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented May 8, 2017

Submitted by: @pavel-zotov

Attachments:
crash-on-drop-db-level-trigger.7z
crash-on-drop-db-level-trigger-b.7z

Following script leads FB 3.0 and 4.0 to crash (and finishes w/o problem on FB 2.5.8):

===
shell del C:\MIX\firebird\QA\fbt-repo\tmp\trgtest.fdb 2>nul;
create database 'localhost:C:\MIX\firebird\QA\fbt-repo\tmp\trgtest.fdb' user 'SYSDBA' password 'masterkey';
show version;

set bail on;
set list on;
set echo on;
set term ^;
execute block as
begin
begin
rdb$set_context('USER_SESSION','POINT_1','1');
execute statement 'drop trigger trg_tx_start';
when any do begin end
end
rdb$set_context('USER_SESSION','POINT_1','2');

end
^
set term ;^
commit;

recreate table tlog (ID integer, MSG varchar(100));

set term ^;
create trigger trg_tx_start on TRANSACTION START position 0
as
begin
execute statement ('insert into tlog(msg) values(?)') ('Tx start');
end
^
set term ;^
commit;

--connect 'localhost:C:\MIX\firebird\QA\fbt-repo\tmp\trgtest.fdb' user 'SYSDBA' password 'masterkey'; ------------------------------ [ 1 ]

set term ^;
execute block as
declare c int;
begin
begin
rdb$set_context('USER_SESSION','POINT_1','3');
execute statement 'select count(*) from rdb$types,rdb$types,(select 1 i from rdb$types rows 100)' into c; ------- artificial delay for logging this point in the trace log

    execute statement 'drop trigger trg\_tx\_start';
when any do begin end
end
rdb$set\_context\('USER\_SESSION','POINT\_1','4'\);

end
^
set term ;^
commit;
quit;

Reconnect (see above statement marked as "[1]" ) does not help.
Trace and artificial delay ( execute statement 'select count(*) from rdb$types,rdb$types,(select 1 i from rdb$types rows 100)' into c; ) shows:

2017-05-08T11:56:36.6400 (1352:00FA2800) SET_CONTEXT
C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\TRGTEST.FDB (ATT_3, SYSDBA:NONE, NONE, TCPv4:127.0.0.1/1222)
C:\MIX\firebird\fb40\isql.exe:1320
(TRA_12, CONCURRENCY | WAIT | READ_WRITE)
[USER_SESSION] POINT_1 = "3"

2017-05-08T11:56:36.6400 (1352:00FA2800) PREPARE_STATEMENT
C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\TRGTEST.FDB (ATT_3, SYSDBA:NONE, NONE, TCPv4:127.0.0.1/1222)
C:\MIX\firebird\fb40\isql.exe:1320
(TRA_12, CONCURRENCY | WAIT | READ_WRITE)

Statement 1560:
-------------------------------------------------------------------------------
select count(*) from rdb$types,rdb$types,(select 1 i from rdb$types rows 100)
0 ms

2017-05-08T11:56:36.6400 (1352:00FA2800) EXECUTE_STATEMENT_START
C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\TRGTEST.FDB (ATT_3, SYSDBA:NONE, NONE, TCPv4:127.0.0.1/1222)
C:\MIX\firebird\fb40\isql.exe:1320
(TRA_12, CONCURRENCY | WAIT | READ_WRITE)

Statement 1560:
-------------------------------------------------------------------------------
select count(*) from rdb$types,rdb$types,(select 1 i from rdb$types rows 100)

Error reading data from the connection.

(without delay trace can not even show start of 2nd execute block - it is interrupted by crash).

firebird.log:

CSPROG Mon May 08 11:56:45 2017
REMOTE INTERFACE/gds__detach: Unsuccesful detach from database.
Uncommitted work may have been lost.
Error writing data to the connection.

Stack trace and .sql for running on POSIX - see in attached .7z (done for LI-V3.0.3.32718, but the same result can be seen on recent 4.0 build).

Commits: 0dbef7f 208f24f

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 8, 2017

Modified by: @pavel-zotov

Attachment: crash-on-drop-db-level-trigger.7z [ 13111 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 8, 2017

Commented by: @pavel-zotov

Problem seems to be related to db-level trigger per se rather than attempt to drop it.

This:

shell del C:\MIX\firebird\QA\fbt-repo\tmp\trgtest.fdb 2>nul;
create database 'localhost/3333:C:\MIX\firebird\QA\fbt-repo\tmp\trgtest.fdb' user 'SYSDBA' password 'masterkey';
show version;

set bail on;
set list on;
set term ^;
execute block as
begin
begin
rdb$set_context('USER_SESSION','POINT_1','1');
execute statement 'drop trigger trg_tx_start';
when any do begin end
end
rdb$set_context('USER_SESSION','POINT_1','2');

end
^
set term ;^
commit;

recreate table tlog (id integer, MSG varchar(100));

set term ^;
create trigger trg_tx_start inactive on TRANSACTION START as
begin
execute statement ('insert into tlog(id, msg) values(?, ?)') (current_transaction,'Tx start');
end
^
set term ;^
commit;

alter trigger trg_tx_start active;
commit;

set autoddl off;

set echo on;

select * from tlog; ---------------- 1
rollback;

select * from tlog; ---------------- 2
commit;

select * from tlog; ---------------- 3

-- does not contain DROP TRIGGER statement but also failed.
Tried on:

ISQL Version: WI-V3.0.3.32718 Firebird 3.0
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-V3.0.3.32718 Firebird 3.0"
Firebird/Windows/Intel/i386 (remote server), version "WI-V3.0.3.32718 Firebird 3.0/tcp (csprog)/P15"
Firebird/Windows/Intel/i386 (remote interface), version "WI-V3.0.3.32718 Firebird 3.0/tcp (csprog)/P15"
on disk structure version 12.0

Console output:

select * from tlog; ---------------- 1

ID 12
MSG Tx start

ID 13
MSG Tx start

rollback;
Statement failed, SQLSTATE = 08006
Error reading data from the connection.

firebird.conf:

RemoteServicePort = 3333
Servermode = Super
IpcName = fb30ss

LockHashSlots = 22111
TempCacheLimit = 2147483647

#⁠ actual only in Win, FW = OFF
MaxUnflushedWrites = -1
MaxUnflushedWriteTime = -1

#⁠ExternalFileAccess = Restrict C:\MIX\firebird
TempDirectories = c:\temp
MaxUserTraceLogSize = 99999

FileSystemCacheThreshold = 65536K

AuthServer = Legacy_Auth,Srp
AuthClient = Legacy_Auth,Srp,Win_Sspi
WireCrypt = Disabled
UserManager = Legacy_UserManager
DeadlockTimeout = 1
UdfAccess = Restrict UDF;C:\WINNT\system32

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 8, 2017

Modified by: @pavel-zotov

summary: Crash on 3.0 and 4.0 when attempting to drop database-level trigger => Crash on 3.0 and 4.0 when DB contains database-level trigger

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 8, 2017

Commented by: @pavel-zotov

Problem does exist in _all_ 4.0.x builds since 4.0.0.0032371-0_Win32.7z (03-mar-2016) - console output finishes in all cases with the same text:

ID 13
MSG Tx start

rollback;
Statement failed, SQLSTATE = 08006
Error reading data from the connection.

As of 3.0.x - it looks "much better".
Particularly, 3.0.0.31472-0_Win32.zip (10-dec-2014) it hangs with producing following output:

ID 13
MSG Tx start

rollback;

select * from tlog; ---------------- 2

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 8, 2017

Commented by: @pavel-zotov

Attached file "crash-on-drop-db-level-trigger-b.7z " contains .sql script that reproduces crash and its stack trace for simple case:

===
alter trigger trg_tx_start active;
commit;

set autoddl off;

set echo on;

select * from tlog; ---------------- 1
rollback;
quit;

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 8, 2017

Modified by: @pavel-zotov

Attachment: crash-on-drop-db-level-trigger-b.7z [ 13112 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 8, 2017

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2017

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

Fix Version: 3.0.3 [ 10810 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 11, 2017

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 11, 2017

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment