Network problems in ES/EDS (if occur) does now allow: 1) delete attachment
which initiated this ES; 2) shuttown DB (!)
----------------------------------------------------------------------------------------------------------------------
Key: CORE-5609
URL: http://tracker.firebirdsql.org/browse/CORE-5609
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.2, 2.5.7
Reporter: Pavel Zotov
1. Create following script (note that bogon IP = 192.0.2.2 is used as node that
for sure will not be ever reached, so its using will always cause network
trouble):
===
set list on;
select current_timestamp as dts_beg from rdb$database;
set term ^;
execute block returns (c int) as
begin
execute statement 'select 1 from rdb$database'
on external '192.0.2.2:' || rdb$get_context('SYSTEM', 'DB_NAME')
as user 'SYSDBA' password 'masterkey'
into c;
suspend;
end
^
set term ;^
select current_timestamp as dts_end from rdb$database;
===
2. Run this script:
C:\FB\25sC\bin\isql /:e25 -i ext-conn-test.sql
You will see that both in 2.5 and 3.0 it will hangs approx. for ~45 seconds.
Eventually console will contain output like this:
===
DTS_BEG 2017-09-11 00:51:58.1160
Statement failed, SQLSTATE = 42000
Execute statement error at attach :
335544721 : Unable to complete network request to host "192.0.2.2".
335544722 : Failed to establish a connection.
Data source : Firebird::192.0.2.2:C:\FBTESTING\QA\FBT-REPO\TMP\E25.FDB
-At block line: 3, col: 5
After line 3 in file ext-conn-test.sql
DTS_END 2017-09-11 00:52:42.1500
===
3. Create another script that will be used to kill all attachments but show
data of them (except his own and, for 3.0+, ecxept system connections created
by cache writer and GC):
===
set list on;
commit;
select current_timestamp, 'Point #1. List of attachments that now are alive and
should be killed:' msg from rdb$database;
set term ^;
execute block returns (
mon_attachment_id type of column mon$attachments.mon$attachment_id
,mon_server_pid type of column mon$attachments.mon$server_pid
,mon_state type of column mon$attachments.mon$state
,mon_attachment_name type of column mon$attachments.mon$attachment_name
,mon_user type of column mon$attachments.mon$user
,mon_role type of column mon$attachments.mon$role
,mon_remote_protocol type of column mon$attachments.mon$remote_protocol
,mon_remote_address type of column mon$attachments.mon$remote_address
,mon_remote_pid type of column mon$attachments.mon$remote_pid
,mon_character_set_id type of column mon$attachments.mon$character_set_id
,mon_timestamp type of column mon$attachments.mon$timestamp
,mon_garbage_collection type of column
mon$attachments.mon$garbage_collection
,mon_remote_process type of column mon$attachments.mon$remote_process
,mon_stat_id type of column mon$attachments.mon$stat_id
)
as
declare v_id bigint;
begin
for
select
mon$attachment_id
,mon$server_pid
,mon$state
,mon$attachment_name
,mon$user
,mon$role
,mon$remote_protocol
,mon$remote_address
,mon$remote_pid
,mon$character_set_id
,mon$timestamp
,mon$garbage_collection
,mon$remote_process
,mon$stat_id
from mon$attachments
where mon$attachment_id != current_connection and mon$remote_protocol is not
null
into
mon_attachment_id
,mon_server_pid
,mon_state
,mon_attachment_name
,mon_user
,mon_role
,mon_remote_protocol
,mon_remote_address
,mon_remote_pid
,mon_character_set_id
,mon_timestamp
,mon_garbage_collection
,mon_remote_process
,mon_stat_id
as cursor c
do begin
delete from mon$attachments where current of c;
suspend;
end
end
^
set term ;^
commit; -- !!! .C.O.M.M.I.T. !!!
select current_timestamp, 'Point #2. Check that NO attachments appear below
this message becaus they are already killed:' msg from rdb$database;
set count on;
select
mon$attachment_id
,mon$server_pid
,mon$state
,mon$attachment_name
,mon$user
,mon$role
,mon$remote_protocol
,mon$remote_address
,mon$remote_pid
,mon$character_set_id
,mon$timestamp
,mon$garbage_collection
,mon$remote_process
,mon$stat_id
from mon$attachments
where mon$attachment_id != current_connection and mon$remote_protocol is not
null
;
set count off;
select current_timestamp, 'Poing #3. ---------------------------------- end of
script -------------------------------' msg from rdb$database;
===
4. Now run again script from "1." and try to kill attaches - during it will
hangs for ~45 seconds run another window and isql plus script from "3". You
will see that attachment that is WAITING for reply from bogon IP can not be
killed.
Moreover, you even will not be able to SHUTDOWN (!) database until this problem
will gone: attachment fields will be displayed after text "Point #2. Check that
NO attachments appear below this message becaus they are already killed" but
this should not be so.
Checked on Win 8.1 Pro 64 bit, FB builds :
WI-V3.0.3.32798 (25-aug-2017) - SC; SS
WI-V2.5.8.27070 (23-aug-2017) - SC only.
PS.
Yet another note, about 2.5.x.
Run script from "1." and terminate its work by pressing Ctrl-Break. Than
(AFTER terminating and return to OS shell prompt) run script from "3".
You will see that attachment still exists. This will be so about 60 seconds. No
such effect on 3.0 (both SS and SC).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
