Does CONNECTION_AUTHENTICATION block

[Former Member]

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?

[chris_keating]

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.

[VolkerBarth]

Do you use a user-defined login procedure?

[Former Member]

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?

[Former Member]

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

[chris_keating]

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.

[chris_keating]

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.

[VolkerBarth]

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...

[Breck_Carter]

> 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.