All Apps and Add-ons

Why does the dbxlookup not return results?

boromir
Path Finder

So here is the issue.

We have a distr. environment with a DBConnect 3.5.1 running on a HF.

DBinputs and DBoutputs are seeing heavy use and are working,  I used dblookup as well for a while( about a month ago) and it worked just fine.

Today though neither the old  dbxlookups, nor any of my new ones work. They return empty columns(column that should have been filled is there but no values are present).

Here is a test example :

| makeresults count=10
| streamstats count as id

| dbxlookup connection="myconnection" query="SELECT * FROM `my_db`.`tbl_id_to_name`" "id" AS "id" OUTPUT "name" AS "name"

I have an old environment which i use for tests with older DBC, and the same queries work over there(and as said, they worked a few weeks ago).   I have triple-quadruple checked if the tables used for lookups have data inside, and yes they do......I am baffled, no idea what is going on.

any suggestions?

 

 

Labels (3)
Tags (3)
0 Karma
1 Solution

boromir
Path Finder

So...

Here is the outcome. And for some it could be a solution. 

After testing I can confirm that if MariaDB driver is used for the DB Connection, the normal SELECT(in case of a SQL query) works just fine, inputs are also fine, but when used for lookup the format switches to using Quotation marks instead of single quotations. Thus the return is an empty set, which brings no value to the lookup. 

With MySQL driver this does not happen.(that is one solution if you could afford it)

I however, needed the MariaDB driver, so the solution that we found is actually changing a configuration on the SQL side , adding ANSI_QUOTES in sql.mode. This resolved the issue.

So...I hope it helps 🙂

Have fun!

RD

 

View solution in original post

0 Karma

boromir
Path Finder

OK, OK....

I have an update on the matter. After some troubleshooting and log collection on the SQL side....I see that in both cases(Old environment, where things are still working, and in the new one, where it stopped) the SELECT is formed and sent towards the SQL side, however the command format is different, thus in one of the cases, the result is an empty set, resulting in failed dbxlookup.

I hope this makes sense... but let me try to illustrate :

working select :

SELECT `id`, `user_id` FROM (SELECT * FROM `my_db`.`tbl_mytbl`) dbxlookup WHERE `id` IN (1,2,3,4,5,6,7,8,9,10,11,12,13);

Non-working select:

SELECT "id", "user_id" FROM (SELECT * FROM `my_db`.`tbl_mytbl`) dbxlookup WHERE "id" IN ('1','2','3','4','5','6','7','8','9','10','11','12','13');

 

Now... the quotes and apostrophes  are the immediate reason, because, if I manually execute the selects with one format it works, with the other...as said (Empty set).

Where is the difference, though.   One is using MySQL connector for the DBC and the non-working case uses MariaDB connector.

For good or for bad.... I need to use MariaDB connector for other reasons, but also need to find how to resolve the SELECT format....

 

Any ideas?

 

regards!

RD

 

 

0 Karma

boromir
Path Finder

So...

Here is the outcome. And for some it could be a solution. 

After testing I can confirm that if MariaDB driver is used for the DB Connection, the normal SELECT(in case of a SQL query) works just fine, inputs are also fine, but when used for lookup the format switches to using Quotation marks instead of single quotations. Thus the return is an empty set, which brings no value to the lookup. 

With MySQL driver this does not happen.(that is one solution if you could afford it)

I however, needed the MariaDB driver, so the solution that we found is actually changing a configuration on the SQL side , adding ANSI_QUOTES in sql.mode. This resolved the issue.

So...I hope it helps 🙂

Have fun!

RD

 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...