Hi all,
I am having a problem with the DB connect app and a scheduled query which is executed each 30 minutes. Basically the SQL is working for a while (2-4 hours), but then suddenly the SQL is not executed anylonger, due to the following error:
error code = 400
error message = caused by: Exception(\' java.sql.SQLTransientConnectionException: MED - Connection is not available, request timed out after 3000ms..\',).
My settings are the following:
Splunk version: 6.5.1
DBConnect version: 2.4.0
Java = C:\Program Files\Java\jdk1.8.0_101
JDBC driver = MS-SQL Server Using MS Generic Driver 4.0 (sqljdbc4.jar)
Database = MSSQL 2012
transaction type = py_dbquery:execute_query
I have tried to find anything on MSSQL server side, but there is no event log entry or anything else, it seems like the SQL is triggered and running into a timeout on Splunk side, but the SQL is never reaching the MSSQL server. The problem is after a few hours gone again, then it is working for maybe 1-2 hours and afterwards the error appears again.
Does anybody have an idea what could be the reason?
Thanks.
I have found the problem, there is a configuration file "db_connections.conf" where you can edit the following values.
useConnectionPool = false
maxConnLifetimeMillis = 35000
maxWaitMillis = 30000
maxIdleConn = 10
maxTotalConn = 10
Since I updated the DB Connect App to version 3.1.0 the problem does not appear again.
I also haven't configured this Parameters in my db_connections.conf file anylonger.
Maybe this was a bug for the old DB Connect App in version 2.4.0.
On Splunk documentation it is mentioned that, maxConnLifetimeMillis OR maxWaitMillis OR maxIdleConn OR maxTotalConn settings are valid only when useConnectionPool = true. Can you please let me know if these settings are still working for you.
maxConnLifetimeMillis = <value>
# optional, default is 120000 = 120 seconds
# valid when useConnectionPool = true
# The maximum lifetime in milliseconds of a connection. After this time is exceeded the connection will fail the next activation, passivation or validation test.
# A value of zero or less means the connection has an infinite lifetime.
maxWaitMillis = <value>
# optional, default is 30000 = 30 seconds
# valid when useConnectionPool = true
# The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely.
maxIdleConn = <value>
# optional, default is 8 connections
# valid when useConnectionPool = true
# The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit.
# Since DB connect 2.3.0, this parameter is obsolete and not used any more.
maxTotalConn = <value>
# optional, default is 8 connections
# valid when useConnectionPool = true
# The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit.
I have found the problem, there is a configuration file "db_connections.conf" where you can edit the following values.
useConnectionPool = false
maxConnLifetimeMillis = 35000
maxWaitMillis = 30000
maxIdleConn = 10
maxTotalConn = 10
Sorry the timeout is set to 30000 ms not 3000.