All Apps and Add-ons

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
Communicator

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

jcoates_splunk
Splunk Employee
Splunk Employee

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

0 Karma

hgehrts_splunk
Splunk Employee
Splunk Employee

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

0 Karma

todd_miller
Communicator

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.

0 Karma

todd_miller
Communicator

Back to getting this error.

0 Karma

todd_miller
Communicator

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.

0 Karma

amljohnson
Explorer

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?

0 Karma

amljohnson
Explorer

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

0 Karma

hgehrts_splunk
Splunk Employee
Splunk Employee

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

0 Karma

amljohnson
Explorer

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.

0 Karma

icyfeverr
Path Finder

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.

0 Karma

amljohnson
Explorer

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

0 Karma

icyfeverr
Path Finder

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

0 Karma

todd_miller
Communicator

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

hgehrts_splunk
Splunk Employee
Splunk Employee

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

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

What version of Splunk?

0 Karma

hgehrts_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

todd_miller
Communicator

Thanks to the mod for cleaning up my message! 🙂

0 Karma

ppablo
Community Manager
Community Manager

no problem @todd_miller 🙂

0 Karma

todd_miller
Communicator

D'oh. Sorry.

Splunk Enterprise 6.3.0 and DB Connect v2.0.6

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!