Placing "Srp" at the start of authentification plugins list (on server) can
lead to multiple (up to 42) fails to connect with error "SQLSTATE = 08006".
Mostly on Classic, also can be on SuperClassic. Never on SuperServer. Requires
FB run as APP.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-5050
URL: http://tracker.firebirdsql.org/browse/CORE-5050
Project: Firebird Core
Issue Type: Bug
Components: API / Client Library, Engine, ISQL, Security
Affects Versions: 3.0 RC1
Reporter: Pavel Zotov
Attachments: logs-with-failed-first-attempt-to-connect.7z
1. Create SQL script (I've named it 'c4868.sql' because this is next step of
investigation core-4868 issues):
===
set term ^;
create or alter procedure sys_get_fb_arch (
a_connect_with_usr varchar(31) default 'SYSDBA'
,a_connect_with_pwd varchar(31) default 'masterke'
) returns(
fb_arch varchar(50)
) as
declare cur_server_pid int;
declare ext_server_pid int;
declare att_protocol varchar(255);
declare v_test_sttm varchar(255);
declare v_fetches_beg bigint;
declare v_fetches_end bigint;
begin
-- Aux SP for detect FB architecture.
select a.mon$server_pid, a.mon$remote_protocol
from mon$attachments a
where a.mon$attachment_id = current_connection
into cur_server_pid, att_protocol;
if ( att_protocol is null ) then
fb_arch = 'Embedded';
else if ( upper(current_user) = upper('SYSDBA')
and rdb$get_context('SYSTEM','ENGINE_VERSION') NOT starting with
'2.5'
and exists(select * from mon$attachments a
where a.mon$remote_protocol is null
and upper(a.mon$user) in ( upper('Cache
Writer'), upper('Garbage Collector'))
)
) then
fb_arch = 'SuperServer';
else
begin
v_test_sttm =
'select a.mon$server_pid + 0*(select 1 from rdb$database)'
||' from mon$attachments a '
||' where a.mon$attachment_id = current_connection';
select i.mon$page_fetches
from mon$io_stats i
where i.mon$stat_group = 0 -- db_level
into v_fetches_beg;
execute statement v_test_sttm
on external
'localhost:' || rdb$get_context('SYSTEM', 'DB_NAME')
as
user a_connect_with_usr
password a_connect_with_pwd
role left('R' || replace(uuid_to_char(gen_uuid()),'-',''),31)
into ext_server_pid;
in autonomous transaction do
select i.mon$page_fetches
from mon$io_stats i
where i.mon$stat_group = 0 -- db_level
into v_fetches_end;
fb_arch = iif( cur_server_pid is distinct from ext_server_pid,
'Classic',
iif( v_fetches_beg is not distinct from
v_fetches_end,
'SuperClassic',
'SuperServer'
)
);
end
fb_arch = fb_arch || ' ' || rdb$get_context('SYSTEM','ENGINE_VERSION');
suspend;
end
^ -- sys_get_fb_arch
set term ;^
commit;
create or alter view v_info as
select
mon$user as who_ami,
mon$auth_method as my_auth,
rdb$get_context('SYSTEM','CLIENT_PROCESS') as my_exe,
mon$client_version as my_fbdll,
current_connection as my_attach,
p.fb_arch
from mon$attachments
left join sys_get_fb_arch('SYSDBA', 'masterke') p on 1=1
where mon$attachment_id = current_connection
;
set list on;
set term ^;
execute block returns(
msg varchar(50),
fb_arch type of column v_info.fb_arch,
who_ami type of column v_info.who_ami,
my_auth type of column v_info.my_auth,
my_exe type of column v_info.my_exe,
my_fbdll type of column v_info.my_fbdll,
my_attach type of column v_info.my_attach
) as
begin
for
select 'From main connection', fb_arch, who_ami, my_auth, my_exe,
my_fbdll, my_attach
from v_info
into msg, fb_arch, who_ami, my_auth, my_exe, my_fbdll, my_attach
do
suspend;
for
execute statement
'select ''From connection via ES/EDS'', fb_arch, who_ami, my_auth,
my_exe, my_fbdll, my_attach '
||' from v_info'
on external 'localhost:' || rdb$get_context('SYSTEM','DB_NAME')
as user 'SYSDBA' password 'masterke'
into msg, fb_arch, who_ami, my_auth, my_exe, my_fbdll, my_attach
do
suspend;
end
^
set term ;^
rollback;
===
2. Create batch file with name that is matched to SQL (except extension, of
course; so, its name here is: 'c4868.bat'):
===
@echo off
setlocal enabledelayedexpansion enableextensions
@rem path to ISQL on client machine:
set fbc=C:\MIX\firebird\fb30
@rem Remote host, port and DB alias:
set host=192.168.0.220
set port=3333
set dbnm=e30
set pbak=%path%
set path=%fbc%;%pbak%
@rem Define current timestamp in order to add its value to LOGS:
set vbs=%~n0.tmp.vbs
del %vbs% 2>nul
(
echo strDate = DatePart("yyyy",Date^) _
echo ^& Right("0" ^& DatePart("m",Date^), 2^) _
echo ^& Right("0" ^& DatePart("d",Date^), 2^)
echo strTime = Right("0" ^& Hour(Now^), 2^) _
echo ^& Right("0" ^& Minute(Now^), 2^)
@rem echo ^& Right("0" ^& Second(Now^), 2^)
echo Wscript.Echo( strDate ^& "_" ^& strTime^)
) >>%vbs%
for /f %%i in ('cscript /nologo %vbs%') do set dts=%%i
del %vbs% 2>nul
set log=%~n0.%dts%.log
set err=%~n0.%dts%.err
set tmp=%~n0.tmp
del %log% 2>nul
del %err% 2>nul
echo Starting probes at !time!
set k=0
:m1
set /a k=!k!+1
set msg=!time!. Probe # !k!
echo !msg!
echo !msg!>>%log%
%fbc%\isql %host%/%port%:%dbnm% -z -i %~n0.sql 1>>%log% 2>>%tmp%
findstr /i SYSDBA %log% >nul
if not errorlevel 1 goto end
for /f "tokens=*" %%a in ('findstr /i /c:"statement failed" %tmp%') do (
echo !time! %%a >>%err%
)
del %tmp% 2>nul
echo !time!. FAILED.
ping -n 1 127.0.0.1 >nul
goto :m1
:end
if not exist %err% (
echo !time!. Got connection at FIRST attempt, no logs required.
type %log%
del %log%
) else (
echo !time! Got connection only at !k!th attempt, check %err%
)
del %tmp% 2>nul
set path=%pbak%
===
3. Replace in batch settings of: FBC, HOST, PORT and DBNM to yours.
4. Change firebird.conf on server to:
===
AuthClient = Srp,Legacy_Auth,Win_Sspi
AuthServer = Srp,Legacy_Auth
BugCheckAbort=1
ExternalFileAccess = Restrict /var/db/fb30
FileSystemCacheThreshold = 65536K
LockHashSlots = 22111
LockMemSize = 64M
MaxUserTraceLogSize = 99999
RemoteServicePort = 3333
Servermode = Classic
TempCacheLimit = 2147483647
TempDirectories = /tmp/firebird
UserManager = Srp
WireCrypt = Disabled
===
NOTE: 'Srp' must be at 1st position in Auth* lists.
Client-side firebird.conf can be with either 'Legacy' or 'Srp' in 1st position
of plugins - this seems has no matter.
5. Open two console windows: 1) on server host (I use FB on Linux, so get this
via Putty); 2) on client machine (Windows XP).
6. Switch to client console and type: C4868.BAT - but do NOT press Enter now.
7. Stop FB if it is running as service. Type in command prompt:
/opt/fb30/bin/firebird -- but also do NOT press Enter for now.
8. Check that when you will press Alt-Tab then Windows will switch between
server & client console windows and do not 'jumps' to some other window (if
exists).
9. Switch to server console and press Enter.
10. Swicth === !! AS FAST AS YOU CAN !! == to client console (by pressing
Alt-Tab) and press Enter there.
When FB is launched as Classic (as it show above) one may to get almost in
every 3/4 runs following (in the client console):
===
Starting probes at 13:13:54,80
13:13:54,80. Probe # 1
13:13:55,97. FAILED.
13:13:56,00. Probe # 2
13:13:57,18. FAILED.
13:13:57,21. Probe # 3
13:13:58,38. FAILED.
13:13:58,39. Probe # 4
13:13:59,58. FAILED.
13:13:59,60. Probe # 5
13:14:00,80. FAILED.
13:14:00,82. Probe # 6
13:14:02,00. FAILED.
13:14:02,02. Probe # 7
13:14:03,21. FAILED.
13:14:03,22. Probe # 8
13:14:04,60 Got connection only at 8th attempt, check c4868.20151219_1313.err
===
Most often there are 25 attempts before connection can be established (for
Classic).
When FB is launched as SuperClassic this strange behaviour is harder to
reproduce, and it seems that one may to get it only at FIRST running since
parameter 'Servermode' was changed to SC. Any subsequent launched of batch will
report that connection is established immediatelly, at 1st attempt.
When FB is launched as SuperServer this effect could not be reproduced by me.
When 'Legacy' is placed at 1st position on server firebird.conf this effect
does not appear.
When FB is running as service this effect also does not appear.
IMPORTANT NOTES.
1. *** WAIT *** at least 20-30 seconds after FB application process is stopped
(by pressing Ctrl-C). Do not launch it again just after you change config if
this time was not elapsed yet!
2. If you change Servermode to SuperClassic you will be able to get failed
attempts only ONCE, even if later you will stop FB application and wait more
than several minutes. You have to change Servermode to Classic and do at least
one launch before change it back to SuperClassic. MISTERIOUS but this is what
I've got on my environment.
3. When you will play only with Servermode = Classic you will get failed
attempts almost every time (I got approx. in 75% of cases), so you can leave
config unchanged. But remember: you have to WAIT at least 20-30 seconds before
subsequent run of batch.
Bulk of logs with failed 1st and subsequent attaches see in attached file.
PS. It's very pity that one may not to obtain actual server config parameters
in ISQL (by some new fields in rdb$database ?) and log them :(
--
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
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
