Question Re: Log existing connection failures in Technology Q&A

Log existing connection failures

Former Member — Mon, 21 Jan 2013 07:35:03 GMT

SQL Anywhere 10 does not report database connection failures on the server when there is an underlying TCP/IP error.

From looking through the docs I've found an auditing feature for successful and failed connection attempts. This falls short for what we need.

Is it possible to enable a simple text based access log for the database similar to a HTTP server e.g. connection request - ip address - time, connection failure - ip address - time? It would also be good to have close connection requests.

Thanks in advance,

Shane

Re: Log existing connection failures

Breck_Carter — Mon, 21 Jan 2013 08:21:35 GMT

The CREATE EVENT statement offers some functionality with the Connect, ConnectFailed and Disconnect event types, but it probably falls short of your needs as well. ODBC tracing and the dbsrv10 -z option are probably too extreme.

Re: Log existing connection failures

jeff_albion — Mon, 21 Jan 2013 08:45:20 GMT

SQL Anywhere 10 does not report database connection failures on the server when there is an underlying TCP/IP error.

Perhaps I'm misunderstanding, but I assume you're referring to a TCP/IP error between the client and the server (e.g. 'host not found', etc.?) If so, the server may not see all of these errors to be logged, since they are potentially happening at the network level. For these types of failures, you would be better to enable client-side logging (i.e. add "LogFile=clientlog.txt" to your connection string).

Are you seeing these failures often / unexpectedly?

Is it possible to enable a simple text based access log for the database similar to a HTTP server e.g. connection request - ip address - time, connection failure - ip address - time? It would also be good to have close connection requests.

See the 'dbsrv10 -z -o output.txt' console log output switches.

If you would like to create your own logging feature, you can likely do that via the 'Connect / Disconnect' system events and the CONNECTION_PROPERTY('AppInfo'), CONNECTION_PROPERTY('CommLink'), and CONNECTION_PROPERTY('NodeAddr') connection properties.

Re: Log existing connection failures

VolkerBarth — Mon, 21 Jan 2013 09:17:51 GMT

Note that for failing connection attempts due to network errors, the ConnectFailed event won't usually be able to log these ... simply as the server won't notice such an attempt at all (as Jeff and Breck have stated, too)...

Re: Log existing connection failures

Former Member — Tue, 22 Jan 2013 10:04:10 GMT

Requiring extra logging or an event is overkill, IMHO. It would be nice if the client posted the OS-level/TCP-level error code plus a human-readable description.

Re: Log existing connection failures

Former Member — Tue, 22 Jan 2013 10:41:05 GMT

Thanks for taking the time to respond.

When I mentioned TCP/IP error it was in the context of an active connection to the database that suffers from a network outage.

Our application using connection pooling which involves the re-use of the same connection many times over a prolonged period. If the network goes down between our application host and database host briefly I would like to see an appropriate error in the database logs.

Since this is an intermittent problem in our production environment it is not suitable for use to run the database in diagnostic mode using the -z server option. I understand that this has a major affect on performance.

Unfortunately the connection events only show connection attempts rathen than the health of existing connections.

Re: Log existing connection failures

VolkerBarth — Tue, 22 Jan 2013 11:05:17 GMT

Just to understand: If a living connection goes down, wouldn't it be easier to log that on the client side?

I don't know which API you are using. AFAIK, v11 has introduced a ODBC feature that might be of use in case you are using ODBC - from the "What's new / Programming interfaces" doc page:

SQL_ATTR_CONNECTION_DEAD promptly detects dead connection

Using ODBC's SQLGetConnectAttr call to get the SQL_ATTR_CONNECTION_DEAD attribute now gets the value SQL_CD_TRUE if the connection has been dropped even if no request has been made to the server since the connection was dropped. Determining if the connection has been dropped is done without making a request to the server, and the dropped connection is detected within a few seconds. The connection can be dropped for several reasons, for example, on an idle timeout. Before this change, SQL_ATTR_CONNECTION_DEAD only got the value SQL_CD_TRUE if the connection was disconnected or if ODBC driver made a request to the server (by calling SQLExecDirect for example) after the connection was dropped. See Getting connection attributes.

Re: Log existing connection failures

jeff_albion — Tue, 22 Jan 2013 12:43:43 GMT

If the network goes down between our application host and database host briefly I would like to see an appropriate error in the database logs.

I believe you really mean "application logs" here, not database server logs. Again, the failure to contact the server over TCP/IP from the client needs to be logged on the client side - the database server can't log a connection failure that it can't see.

Beyond the "LOG=" client log connection string option that I mentioned previously, depending on which API you're using (as Volker indicated), you can usually 'catch' some kind of exception / log an error upon a failed connection attempt from the client side in the API layer, and then "log" it somewhere in your application.

e.g. ADO.NET C# code:

SAConnection conn = new SAConnection( "Data Source=SQL Anywhere 12 Demo;UID=DBA;PWD=sql" );

try {
    conn.Open();
    ...
} catch (SAException ex){
    MessageBox.Show( ex.Errors[0].Message );
    // do more application log error logging here
}

e.g JDBC code:

try {
    Connection conn = DriverManager.getConnection( "jdbc:sqlanywhere:uid=DBA;pwd=sql" );
    ...
} catch (SQLException sqe){
   System.err.println("Error: " + sqe.toString() + ", sqlstate = " + sqe.getSQLState());
    // do more application log error logging here
}