(see also How do I stop sa_set_http_option 'SessionID' from causing second remote server connections?)
A SQL Anywhere procedure created in 2006, running in production since 2011 and last modified in 2013 has suddenly changed it's behavior on @@version 17.0.9.4882...
It creates a second persistent idle connection to a remote (proxy) SQL Anywhere server when three successive FORWARD TO statements are executed.
SELECT CURRENT TIMESTAMP, * FROM sa_conn_info() ORDER BY Number;
current timestamp,Number,Name,
Userid,DBNumber,LastReqTime,ReqType,CommLink,NodeAddr,ClientPort,ServerPort,
BlockedOn,LockRowID,LockIndexID,LockTable,UncommitOps,ParentConnection,LockObject,LockObjectType
2019-10-31 10:08:16.272,1,'Foxhound-Monitor-10160', ***** the main Foxhound sampling connection on the target database
'dba',0,'2019-10-31 10:08:16.272','PREFETCH','local','',59881,2638,
0,0,,'',0,,'',''
2019-10-31 10:08:16.272,2,'ddd17-1',
'dba',0,'2019-10-31 10:08:16.272','FETCH','local','',0,0,
0,0,,'',0,,'',''
2019-10-31 10:08:16.272,3,'ddd17-2',
'dba',0,'2019-10-31 10:07:22.210','PREFETCH','local','',0,0,
0,0,,'',0,,'',''
2019-10-31 10:08:16.272,4,'Foxhound-Monitor-10160', ***** the bogus idle connection
'dba',0,'2019-10-31 10:07:42.573','COMMIT','local','',59883,2638,
0,0,,'',0,,'',''
2019-10-31 10:08:16.272,1000000001,'INT: StmtPerfMngrConn',
'',0,'','unknown (0)','NA','NA',0,0,
0,0,,'',0,,'',''
Why is this bad? Because Foxhound thinks you have accidentally started two Monitor sessions on the same target...
Duplicate Foxhound sampling sessions. There is more than one Foxhound connection to this target database.
Note: The version 17.0.9.4882 has not changed recently.
When these FORWARD TO statements are executed, other code has already created a first persistent connection to the same remote SQL Anywhere server; I have always assumed that connection was used by the FORWARD TO statements, but I have no idea if short-lived connection were ever opened and closed.
However, if one or more connections were ever created by these FORWARD TO statements, they were never observed; i.e., they were not persistent.
...and, unexpected increases in connection numbers were ALSO never observed.
Here's the Last Plan Text on the idle connection...
( Plan
( SingleRowGroupBy
( NestedLoopsJoin
( IndexScan ( ISYSUSER su ) user_name )
( IndexOnlyScan ISYSOPTION ISYSOPTION )
)
)
)
Here's the calling code...
The "proxy owner" value is used for both a SQL Anywhere user id and an associated remote server name...
DECLARE @proxy_owner VARCHAR ( 128 );
...
SELECT ...
rroad_sampling_options.proxy_owner
INTO ...
@proxy_owner
FROM rroad_sampling_options
WHERE rroad_sampling_options.sampling_id = @sampling_id;
...
SET @RememberLastPlan = TRIM ( LEFT ( COALESCE ( f_forward_string_function_call ( @proxy_owner, 'PROPERTY ( ''RememberLastPlan'' )' ), '' ), 3 ) );
SET @RememberLastStatement = TRIM ( LEFT ( COALESCE ( f_forward_string_function_call ( @proxy_owner, 'PROPERTY ( ''RememberLastStatement'' )' ), '' ), 3 ) );
SET @RequestTiming = TRIM ( LEFT ( COALESCE ( f_forward_string_function_call ( @proxy_owner, 'PROPERTY ( ''RequestTiming'' )' ), '' ), 3 ) );
Here's where the FORWARD TO statements are...
CREATE FUNCTION f_forward_string_function_call (
IN @p_remote_server_name VARCHAR ( 128 ),
IN @p_string_function_call LONG VARCHAR )
RETURNS LONG VARCHAR
NOT DETERMINISTIC
BEGIN
-- © Copyright 2013 RisingRoad. All rights reserved. All unpublished rights reserved.
-- breck.carter@gmail.com
-- www.risingroad.com
DECLARE @remote_server_name VARCHAR ( 128 );
DECLARE @sql LONG VARCHAR;
DECLARE @errormsg VARCHAR ( 32767 );
DECLARE @string_return_value LONG VARCHAR;
DECLARE @string_return_value_pos BIGINT;
DECLARE @semicolon_pos BIGINT;
--------------------------------------------------------------------
SET @remote_server_name = TRIM ( COALESCE ( @p_remote_server_name, '' ) );
IF @remote_server_name = '' THEN
RETURN '';
END IF;
--------------------------------------------------------------------
-- Get the string return value via FORWARD TO and RAISERROR.
BEGIN -- error handling block
SET @sql = 'CAST ( ' + @p_string_function_call + ' AS VARCHAR )';
SET @sql = 'RAISERROR 20000 ''Foxhound:'' + ' + @sql; -- use language-invariant eyecatcher 'Foxhound:'
SET @sql = REPLACE ( @sql, '''', '''''' );
SET @sql = 'FORWARD TO ' + @remote_server_name + ' ''' + @sql + '''';
EXECUTE IMMEDIATE @sql;
EXCEPTION
WHEN OTHERS THEN
SET @errormsg = ERRORMSG();
END; -- error handling block
-------------------------------------------------------------------
-- Extract the string value from the error message.
SET @string_return_value = ''; -- until successfully changed
IF @errormsg = 'Server '''': ???' THEN
ELSE
SET @string_return_value_pos = LOCATE ( @errormsg, 'Foxhound:' );
IF @string_return_value_pos > 0 THEN
-- +123456789
-- Foxhound:xxx...
SET @string_return_value = TRIM ( SUBSTR ( @errormsg, @string_return_value_pos + 9 ) );
END IF;
END IF;
RETURN @string_return_value;
END; -- f_forward_string_function_call
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.