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
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.
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?
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