(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 |
Have you tried using Connect/Disconnect events on the target db to monitor new connections? (Probably yes, I assume)
Yes, indeed... here are (some of) the connection properties captured by the Connection event;
The connection_authentication, LastStatement and LastPlanText are [cough] funky :)...
Sadly, calling the very cool function sp_forward_to_remote_server() instead of jumping through EXECUTE IMMEDIATE hoops...
...doesn't help.
Should I try request level logging?
That used to be fun and useful, before it got all f***ed up, er, obfuscated :)
And then there's cis_option = 7.
I'll leave that for tomorrow, I've had enough fun for one day.
Getting rid of the weird RAISERROR technique altogether, and using a DECLARE cursor instead, makes the code MUCH cleaner, and it works... but it STILL causes an unexpected idle second remote server connection to the target.
It's actually worse... the problem appears on an earlier call to this procedure, rather than waiting until a later call.
It's probably something in my code causing this change in behavior, not anything in V17.
Specifically, it is probably caused by something in a recent "patch" involving several new and changed procedures and other SQL objects, because the behavior appears immediately after that patch is applied. This is true even when SQL Anywhere 17 is not involved at all; the original V16 database is installed from the setup.exe, started with the V16 engine, and patched with the V16 engine.
...oh, well, I didn't have anything planned for the weekend :)
...request level logging, cis_option = 7... and ODBC logging! whee!
A wild guess: Is this related to the extern_login_credentials setting? (Don't know if I have spelled it correctly...)
Cf. that FAQ...
oooh, oooh ...
...please let it be so! :)
Alas... extern_login_credentials has been set to 'Login_user' for a long time...
...assuming that is what it should be.
Karim's answer contains this statement which seems to be exactly wrong: "...then the extern_login_credentials option will be set to "Effective_user". The idea here is that if you have explicitly asked for legacy behavior when it comes to system stored procedures, then you probably want legacy behavior for remote connections as well."
I am under the impression that "legacy behavior" is "Login_user".
At this point I'm almost willing to try "Effective_user"... almost :)
Yes, I just thought you might try the opposite option to see whether that makes a difference...
Of course... I was just being lazy... sadly, Effective_user had the expected effect; i.e., it made the second connection appear right away instead of later :)
Anyway, it's gotten way more confusing; see How do I stop sa_set_http_option 'SessionID' from causing second remote server connections?
Fwiw, are the remote calls done by the same logged in and the same effective user? SA17 has special values for these and more user names...
O . . . M . . . G . . .
...you have just ruined my day :)
Once upon a time I said "I should really read the docs from cover to cover"... it's nice to have goals even if they're not achievable :)
Here's a description of the snapshot above...
There are two Foxhound sampling sessions (7 and 8) connected to the same target database ddd17.db, one via DSN and the other via DSN-less connection string.
Each sampling session "sees" three connections on the target database: It's own real connection to the target database, the other session's real connection to the target database, and the goofy connection.
That means Foxhound is recording six sets of data; i.e., 3 connections times 2 sessions.
Note... The user ids DBA_menu and DBA_monitor_options are DBA-privilege user ids used ONLY for CREATE SERVICE AUTHORIZATION OFF USER clauses...
Don't ask me "Why?", that AUTHORIZATION OFF USER clause setup was first used over 10 years ago and it has worked ever since :)
PS the last CREATE EXTERNLOGIN statement was removed in 2008. All targets are SQL Anywhere.
Hm, in my understanding the extern_login_credentials option set to its "pre-v16 default" "Login_user" does explain why a second connection is used (because the session user has changed - how do you do that for a running connection - via SETUSER?).
However, I would have thought the option set to "Effective_user" would mean that in both cases the executing user is "dba" and therefore only one remote connection is done...
Whcih basically means, I still have not fully understood the extern_login_credentials settings...
> I still have not fully understood the extern_login_credentials settings
Indeed... it is worse for me, the more times I read the docs, the less I understand.
> via SETUSER?
No, I have never used SETUSER... ever :)
My current "wave a dead chicken" thought is to change the service definitions to AUTHORIZATION OFF USER DBA so that DBA is the only user id in use.
The Foxhound database almost entirely walled off from end users via client-server database connections... there is a read-only ADHOC reporting user id, and a new ADMIN user id that can only UPDATE a few columns in one single row of one single table... so all this talk of "session", "effective" and "login" is moot...
. . . DBA !
No Dead Chicken Joy... I can get all the "XXX USER" thingies to be the same by using AUTHORIZATION OFF USER DBA, but the goofy connection still appears.
FYI the "Session" values 7, 8 are NOT SQL Anywhere HTTP sessions, they are Foxhound "sampling sessions".
There is only one HTTP session, started by rroad_administrator_login, and ALL the HTTP services after that are using the same connection number 22.
And still "goofy" connections both with option "effective_user"?
Agreed:)
> And still "goofy" connections both with option "effective_user"?
SET OPTION PUBLIC.extern_login_credentials = 'Effective_user';
As reported earlier, it makes everything worse.
Now there are FOUR connections RIGHT AWAY, before sa_set_http_option 'SessionID' is even called.
As before, Foxhound has double-vision because it has two sample sessions for the same target; i.e., it sees the four connections twice:
Not only that, but the extra connection are REAL connections, doing REAL work... so they pass the "msec between Login and LastReq".
I believe these are the extra connections Karim talked about, and they are caused by other FORWARD TO operations (of which Foxhound has many).
I believe my goofy symptom is DIFFERENT, and has NOTHING to do with extern_login_credentials.
Now, let's see what happens when the Foxhound Login-then-Monitor-Options test is performed...
The owners are OK, just like before, all DBA...
But now the goofy connection has appeared, for a total of FIVE connections (or 10, as seen by Foxhound):
So, no, 'Effective_user' is not what I want :)
Of course, I don't want the goofy connection either, but I think I'm just gonna have to ignore it.