DB Connect Input - Query works but Input does not

[rturk]

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)

[ViniciusANT]

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.

[MaverickT]

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?

[MaverickT]

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