After intalling Splunk DB Connect 2.0.6 on our Search Head Cluster members, why are we getting "is not an example of the schema" errors on a lookup?

[todd_miller]

Looks like Splunk DB Connect 2.0.6 fixed the issues with the local execution of the scripts. Now I'm facing the following problems. I've installed DBXv2 on our Search Head Cluster members, built the connections and identities and have validated that connectivity to the DB exists. I've also created a lookup from one of our indexes against the database and I'm receiving the following error (I'm deliberately obfuscating information in the error that may be sensitive to my org). I'm also unable to delete connections or identities once they're saved.

10/15/2015 06:03:22 [INFO] [health.py] CONNECTION=<obfuscated> DATABASE=<obfuscated> DATABASE_TYPE=mysql DB_JDBC_URL=jdbc:mysql://<obfuscated>:3306/<obfuscated> DB_SERVER=<obfuscated> DB_USER=splunk DURATION=0.384140968323 ERROR_CODE=400 FUNCTION=py_dblookup JDBC_USE_SSL=False LABEL=dblookup[mi_lookup://usertype] LOGIN_USER=admin MEMORY_USED=0000 MESSAGE="The datum {u'database': {'connection': u'<obfuscated>', 'policy': 'reload', 'serviceClass': 'com.splunk.dbx2.MySQLJDBC', 'output_fields': u'userType', 'username': 'splunk', 'jdbcUrlSSLFormat': 'jdbc:mysql://<obfuscated>:3306/<obfuscated>?useSSL=true&requireSSL=true&verifyServerCertificate=false', 'input_fields': u'sso', 'TRANS_OBJECT_ID': 'f508895d-f885-48e5-91e4-2eb2d5680b84', 'useConnectionPool': '1', 'jdbcDriverClass': 'com.mysql.jdbc.Driver', 'lookupSQL': u'SELECT * FROM `<obfuscated>`.`identity`', 'jdbcUrlFormat': 'jdbc:mysql://<obfuscated>:3306/<obfuscated>', 'password': '<obfuscated>', 'readonly': True, 'ui_query_mode': u'simple'}, u'name': u'usertype'} is not an example of the schema [
  {
    'type': 'string', 
    'name': 'name'
  }, 
  {
    'type': {
      'type': 'map', 
      'values': 'string'
    }, 
    'name': 'database'
  }
]" SESSION_KEY=************ STATE=completed TRANS_OBJECT_ID=f508895d-f885-48e5-91e4-2eb2d5680b84

[jcoates_splunk]

please double-check the JDBC URL format in db_connections.conf. http://docs.splunk.com/Documentation/DBX/2.0.6/DeployDBX/database_typesspec

[hgehrts_splunk]

I checked the same and added the java class of the driver plus the URL. No difference. The thing is: I get these messages while setting up a lookup configuration under 'operation' in the db connect app. During the setup, everything works: connection to the DB, Querying, even the lookup test. But after I hit 'save' and want to use the defined lookup in search: it is failing with the error "Script for lookup table 'db_connect_assetlookup' returned error code 1. Results may be incorrect."
I have reproduced the error and opened a Case for this: 280769

[todd_miller]

Strangely enough, I'm not getting this error anymore. In fact, I actually got SOME data back. But it's only in the preview. Very weird.

[todd_miller]

Back to getting this error.

[todd_miller]

Well I actually get some data again using a lookup command within the DBXv2 app's search functionality. It's VERY slow but it does seem to work. The key was to specify the following after the search criteria:

| lookup local=true <lookup-name> <column> as <field-name> OUTPUTNEW <field-name>

Automatic lookups continue to fail.

[amljohnson]

Yeah the previews showed up for me eventually too - it seems like a strange sporadic bug. I'm still not seeing any results even with the OUTPUTNEW command.

Support hasn't found a fix for this yet for me, it seems like this may have shipped prematurely?

[amljohnson]

Any response on the support case? I'm about to open one myself as we're seeing the same error

[hgehrts_splunk]

yes, I created a case. Number is 280769 and it is still under investigation. Please open a new one and make a note that your one might be the same as the one above. I think support still needs to check if that's true.

Since the indexing of the DB content was working fine, I was able to get around the problem with doing a manual lookup into the DB via a query like
index=events_to_enrich | head 10| eval "keycolumnfromtable" = host |join type=outer hostname [search index=indexed_assetdb | fields keycolumnfromtable column1fromtable column2fromtable]| fields - host

[amljohnson]

where do you find the index name that's being used for a lookup (i.e. indexed_assetdb in your example)? It would make sense that the lookup values are stored in a temporary index but I can't find where that's defined. I only see the lookup table names.

[icyfeverr]

amljohnson, based on his note, it sounds like he setup a DB Input, which is indexing the database on the server at set intervals, and using that to correlate his data instead of using the DB Lookup, which I believe queries the db directly at the time of search.

[amljohnson]

Great - thanks for the heads up. That makes sense. There are definitely creative ways I could make this functionality work as a lookup function, but I'm going to see how Splunk support handles this first...

[icyfeverr]

Hi hgehrts, Are you using Advanced Query Mode? That is where I have been seeing the most problems with that error. What I have determined so far is that if you are using 'JOIN' statements it seems to throw this error when searching, not through the wizard. I removed the joins and did the linkage in the WHERE statement instead and that has seem to resolve my issue, not that I agree with it, as I think I should be able to use JOIN statements, but thought I would post my findings thus far.

Error Example:
SELECT ce.id, err.error_msg
FROM rdb.error_id ce
JOIN rdb.d_error err ON err.d_error_id = ce.d_error_id

Fixed Example:
SELECT ce.id, err.error_msg
FROM rdb.error_id ce, rdb.d_error err
WHERE err.d_error_id = ce.d_error_id

[todd_miller]

There doesn't appear to be a JDBC URL defined in the db_connections.conf:

[<database_definitionstanza_obfuscated>]
connection_type = mysql
database = <database_name_obfuscated>
host = db_host.nameobfuscated.com
identity = <database_id_obfuscated>
port = 3306
readonly = 1

[hgehrts_splunk]

it's in ../default/db_connection_types.conf.
I copied some lines into my db_connections.conf under local, but still no luck with lookups and the message you have also comes up during setup:

[myassets]
connection_type = mysql
database = test
host = localhost
identity = mysqllogin
port = 3306
readonly = 0
disabled = 0
jdbcUseSSL = false
jdbcDriverClass = com.mysql.jdbc.Driver
serviceClass = com.splunk.dbx2.MySQLJDBC
jdbcUrlFormat = jdbc:mysql://localhost:3306/test

[alacercogitatus]

What version of Splunk?

[hgehrts_splunk]

I have seen a similar problem on 6.3 on linux and db connect 2.0.5 after configuring a db lookup. Currently trying to rebuild the issue on my own system.

[todd_miller]

Thanks to the mod for cleaning up my message! 🙂

[ppablo]

no problem @todd_miller 🙂

[todd_miller]

D'oh. Sorry.

Splunk Enterprise 6.3.0 and DB Connect v2.0.6