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?

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

Splunk Employee
Splunk Employee

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

0 Karma

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 'dbconnectassetlookup' returned error code 1. Results may be incorrect."
I have reproduced the error and opened a Case for this: 280769

0 Karma

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

Communicator

Back to getting this error.

0 Karma

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

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

Explorer

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

0 Karma

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=eventstoenrich | head 10| eval "keycolumnfromtable" = host |join type=outer hostname [search index=indexed_assetdb | fields keycolumnfromtable column1fromtable column2fromtable]| fields - host

0 Karma

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

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

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

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.errormsg
FROM rdb.error
id ce
JOIN rdb.derror err ON err.derrorid = ce.derror_id

Fixed Example:
SELECT ce.id, err.errormsg
FROM rdb.error
id ce, rdb.derror err
WHERE err.d
errorid = ce.derror_id

0 Karma

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

Splunk Employee
Splunk Employee

it's in ../default/dbconnectiontypes.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

SplunkTrust
SplunkTrust

What version of Splunk?

0 Karma

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

Communicator

Thanks to the mod for cleaning up my message! 🙂

0 Karma

Community Manager
Community Manager

no problem @todd_miller 🙂

0 Karma

Communicator

D'oh. Sorry.

Splunk Enterprise 6.3.0 and DB Connect v2.0.6

0 Karma