Why does Splunk DBConnect - execute query transaction is sometimes running into an error

[daniel_schmitz8]

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.

[daniel_schmitz8]

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.

[talla_ranjith]

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.

[daniel_schmitz8 · Accepted solution]

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

[daniel_schmitz8]

Sorry the timeout is set to 30000 ms not 3000.