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

[PPape]

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.

[mchang_splunk · Accepted solution]

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

[PPape]

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

[PPape]

This is it! Thank you very much!

[PPape]

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.

[PPape]

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]

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

[PPape]

Thanks for editing ppablo! 🙂

Any new Ideas here?

[ppablo]

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!

[PPape]

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