Splunk Search

Why is Splunk DB Connect changing the table output of my query?

PPape
Contributor

Hello,

I found a strange behavior with my mySQL Server.

I try to do this query:

select t.change_time, t.create_time_unix, t.tn, t.title, t.timeout, q.name AS Queue, ty.name AS 'type', s.name AS Service 
from ticket as t 
inner join queue as q 
on t.queue_id = q.id 
inner join ticket_type as ty 
on t.type_id = ty.id 
inner join service as s 
on t.service_id = s.id

I expect a table like:

change_time, create_time_unix, tn, title, timeout, Queue, type, Service 

this command works in SQL Studio.

But when I'm using it with the DB Connect App, it gives me back a table like this:

change_time, create_time_unix, tn, title, timeout, name

and name has the data from Service.

Do you guys have an idea what's going wrong and how I can manage this?

Thanks in advance for your help.

1 Solution

mchang_splunk
Splunk Employee
Splunk Employee

This is MySQL JDBC driver issue:
http://www.anujgakhar.com/2012/01/11/railo-mysql-and-column-aliases/
The JDBC specification states that a column name is not changed by “AS”, it’s always the actual name of the column (if such a name exists), so that clients can build a query based on metadata alone. One can only find the name used in the ‘AS’ through ResultSetMetaData’s “getColumnLabel()” method.

Please use this SQL instead:

select t.change_time, t.create_time_unix, t.tn, t.title, t.timeout, q.Queue, ty.type, s.Service
from ticket as t
inner join (select id, name as Queue from queue) as q
on t.queue_id = q.id
inner join (select id, name as 'type' from ticket_type) as ty
on t.type_id = ty.id
inner join (select id, name as Service from service) as s
on t.service_id = s.id

MSSQL doesn't have this issue

View solution in original post

mchang_splunk
Splunk Employee
Splunk Employee

This is MySQL JDBC driver issue:
http://www.anujgakhar.com/2012/01/11/railo-mysql-and-column-aliases/
The JDBC specification states that a column name is not changed by “AS”, it’s always the actual name of the column (if such a name exists), so that clients can build a query based on metadata alone. One can only find the name used in the ‘AS’ through ResultSetMetaData’s “getColumnLabel()” method.

Please use this SQL instead:

select t.change_time, t.create_time_unix, t.tn, t.title, t.timeout, q.Queue, ty.type, s.Service
from ticket as t
inner join (select id, name as Queue from queue) as q
on t.queue_id = q.id
inner join (select id, name as 'type' from ticket_type) as ty
on t.type_id = ty.id
inner join (select id, name as Service from service) as s
on t.service_id = s.id

MSSQL doesn't have this issue

View solution in original post

PPape
Contributor

Okay, the solution with the view is much mor efficient.
But thank you @mchang_splunk for your quick help.

0 Karma

PPape
Contributor

This is it! Thank you very much!

0 Karma

PPape
Contributor

Okay this creates a very huge Database load.

A coworker had the Idea of an View as workaround.
Now I'll test if this is a bit more recource friendly.

0 Karma

PPape
Contributor

Splunk Support answered.

They can reproduce this issue it seems like there is an bug in the JDBC Driver with mySQL.
Because on MSSQL it works correctly.
Splunk Support opend an bug report and gave it to their dev team to verify.

I'll keep you informed when I have some new Infos.

PPape
Contributor

No answer from Splunk Support yet. But I'll keep you updated. Some new ideas here?

0 Karma

PPape
Contributor

Thanks for editing ppablo! 🙂

Any new Ideas here?

0 Karma

ppablo
Community Manager
Community Manager

No problem @PPape

I'm the content manager for Splunk Answers, so I edit most of the posts that come up on the site to make them easier to find for the experts that can answer them and for other users who are searching for solutions to the same questions.

Glad you got a response from Splunk support 🙂 Once you hear back some concrete info, it'll be great if you can share it on this post. Hope you get an answer soon.

Cheers!

0 Karma

PPape
Contributor

Okay I opened an support Case. I will give an answer here if I have some news from Splunk Support

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!