All Apps and Add-ons

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

daniel_schmitz8
Explorer

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.

0 Karma
1 Solution

daniel_schmitz8
Explorer

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

View solution in original post

0 Karma

daniel_schmitz8
Explorer

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.

0 Karma

talla_ranjith
Engager

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

daniel_schmitz8
Explorer

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

0 Karma

daniel_schmitz8
Explorer

Sorry the timeout is set to 30000 ms not 3000.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...