topic Re: DB Connect Input - Query works but Input does not in Getting Data In

DB Connect Input - Query works but Input does not

rturk — Wed, 05 Mar 2014 06:17:37 GMT

Oh hai Splunkers!

So I'm trying to extract a DB table for indexing into Splunk. I have successfully set up an ODBC connection to the external DB, and issuing SQL commands against it work without an issue (see below).

Where it all appears to fall down is when I am setting up the DB Connect database input.

Looking at the dbx_debug log, I see the following errors:

2014-03-05 17:14:00.036 dbx6402:WARN:Database - Database type=com.splunk.dbx.sql.type.impl.ODBC@29306c does not support connection validation

Followed by:

2014-03-05 16:46:00.026 dbx1035:ERROR:TailDatabaseMonitor - Error while executing database monitor: java.sql.SQLException: Invalid Fetch Size
java.sql.SQLException: Invalid Fetch Size
    at sun.jdbc.odbc.JdbcOdbcStatement.setFetchSize(Unknown Source)
    at com.splunk.dbx.sql.type.impl.AbstractDatabaseType.setStreamingResults(AbstractDatabaseType.java:355)
    at com.splunk.dbx.sql.Database.configureStatement(Database.java:222)
    at com.splunk.dbx.sql.Database.query(Database.java:256)
    at com.splunk.dbx.monitor.impl.TailDatabaseMonitor.performMonitoring(TailDatabaseMonitor.java:115)
    at com.splunk.dbx.monitor.DatabaseMonitorExecutor.executeMonitor(DatabaseMonitorExecutor.java:126)
    at com.splunk.dbx.monitor.DatabaseMonitorExecutor.call(DatabaseMonitorExecutor.java:102)
    at com.splunk.dbx.monitor.DatabaseMonitorExecutor.call(DatabaseMonitorExecutor.java:37)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

The SQL command that was used for the input returned only 36 events, so excessive results wouldn't appear to be the issue.

Is connection validation a necessity for inputs? Is there something obvious I'm missing here?

Environment

Windows Server 2012
Splunk 5.0.5
JRE7 (1.7.0_51 32-bit)

Re: DB Connect Input - Query works but Input does not

ViniciusANT — Fri, 10 Oct 2014 19:48:22 GMT

Hi,

I had this problem trying to search a MDB file .

java.sql.SQLException: Invalid Fetch Size
     at sun.jdbc.odbc.JdbcOdbcStatement.setFetchSize(Unknown Source)

Try to set in your dbquery search to use something like limit=1000 . That solved my problems when using MDB files.

Re: DB Connect Input - Query works but Input does not

MaverickT — Wed, 24 Dec 2014 14:20:42 GMT

Hi,
I came around same issues when i am trying to specify database input. I've tried with adding | limit = 1000 at the end of query, but it doesnt work. Do you maybe have configuraton stanza for your input that is working?

Re: DB Connect Input - Query works but Input does not

MaverickT — Wed, 31 Dec 2014 09:18:18 GMT

I solved this issue by running scheduled search like this:

index=test | head 1  | map search="| dbquery \"test\" \"SELECT Format(orderDate, 'yyyy-mm-dd hh:nn:ss') as orderDateDisplay, memberID, orderID, orderStatus FROM [order] WHERE orderID > $orderID$ \"  limit=10000" | collect index=test sourcetype=orders addtime=false