Question Re: Does CONNECTION_AUTHENTICATION block in Technology Q&A

Does CONNECTION_AUTHENTICATION block

Former Member — Tue, 08 Jan 2019 05:33:31 GMT

I am using php (7.1 with php-7.1.0_sqlanywhere_nts.dll) to connect to a SQL Anywhere database running 17.08.4148 that requires authentication. It mostly works correct, but at times i get Message: sasql_connect(): SQLAnywhere: -832 Connection error: Timeout occurred while waiting for connection response

The connectionstring is DSN=Something and the ODBC is set up with SQL Anywhere 17 driver

When I look at the last statement for all open connections when this happens I see lots of connections from this computer as set temporary option "CONNECTION_AUTHENTICATION" = 'Company=XXX;Application=YYY;Signature=ZZZ'

This continues until the "blocked" connections times out. Then everything is working again. Restarting IIS so the connection dies also fixes the problem.

Question is are the statements blocking or are the last statement not reported correct?

Re: Does CONNECTION_AUTHENTICATION block

chris_keating — Thu, 10 Jan 2019 09:52:59 GMT

The output of sa_conn_info and sa_locks should help you to determine if locks or blocking are problematic. This error occurs very early in the connection attempt and before that statement is executed - there is certainly something that is preventing the connection from completing in a way that the client does not timeout the attempt but it is unlikely blocking.

Re: Does CONNECTION_AUTHENTICATION block

VolkerBarth — Thu, 10 Jan 2019 10:14:58 GMT

Do you use a user-defined login procedure?

Re: Does CONNECTION_AUTHENTICATION block

Former Member — Fri, 11 Jan 2019 09:17:55 GMT

I list the connections from this server doing this when it stops: select nodeaddr, b.lastreqtime, LastStatement from sa_conn_info() b join sa_conn_activity() c on b.number = c.number where nodeAddr = 'X.X.X.X' AND reqtype<> 'CONNECT_POOL_CACHE'

Last statement is then the set temporary option.

What should i look at in sa_locks?

When is LastStatement filled? Could it be that its the next statement that is blocking but it is not put in LastStatement yet?

Re: Does CONNECTION_AUTHENTICATION block

Former Member — Fri, 11 Jan 2019 09:18:48 GMT

No, its just normal dba connecting and no user defined code should run before i run the query

Re: Does CONNECTION_AUTHENTICATION block

chris_keating — Fri, 11 Jan 2019 09:49:44 GMT

A non-zero BlockedOn column of the sa_conn_info will be the connection id that is blocking. If you are not not see long lasting non-zero BlockOn for a specific connection, it suggests that there is no blocking occurring. Otherwise, you can then investigate sa_locks to determine the nature of the locks that are causing the blocking.

Re: Does CONNECTION_AUTHENTICATION block

chris_keating — Fri, 11 Jan 2019 10:26:13 GMT

Perhaps getting communication debug information might help.

Add LOG=<filespec> to the connection string on the client and start the engine with -z -o <filespec> -or- execute sa_server_option('DebuggingInformation','yes') on a running server.

Re: Does CONNECTION_AUTHENTICATION block

VolkerBarth — Fri, 11 Jan 2019 10:51:17 GMT

Just to clarify: I was relating to the login_procedure option, so in case that option is set, any starting connection would be affected by that procedure...

Re: Does CONNECTION_AUTHENTICATION block

Breck_Carter — Fri, 11 Jan 2019 11:59:32 GMT

> Could it be that its the next statement that is blocking but it is not put in LastStatement yet?

No, I have never seen that. Sometimes LastStatement is empty, but not wrong.