Splunk DB Connect lookup produces invalid query syntax

[kranzrm]

I have successfully installed and configured DBConnect 3.11 on a search-head and I'm able to query and output data to/from an AWS RDS Aurora database.

My problem happens when I try to run a dbxlookup against my data search results.

From: /opt/splunk/var/log/splunk/splunk_app_db_connect_audit_command.2017-10-15.log

 2017-10-15 20:01:47.139 2886@splunk-sh1 [main] INFO  com.splunk.dbx.connector.logger.AuditLogger - operation= connection_name= stanza_name= state=success sql='SELECT "ip", "dns" FROM (SELECT time, ip, dns FROM `hostip_lookup`.`table_name`) dbxlookup WHERE "ip" IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,)'

I ran this query manually and I am not getting the expected results.

 SELECT "ip", "dns" FROM (SELECT time, ip, dns FROM `hostip_lookup`.`table_name`) dbxlookup

I expected to see the results from my table, instead I see:

 |# ip, dns
 |ip, dns
 |...<repeated>...
 |ip, dns
 |ip, dns

It appears that using double quotes for the column names ( SELECT "ip", "dns" FROM ...) is incorrect. And, instead of returning the record values, it is returning "ip" and "dns" for every record. This is generated by DBConnect and I am only able to change the query in the subselect (within the parenthesis ()).

Taking the quotes away from the column names returns the expected results:

 SELECT ip, dns FROM (SELECT time, ip, dns FROM `hostip_lookup`.`table_name`) dbxlookup

Questions to my fellow Splunk Ninjas:
1. Is DBConnect generating incorrect SQL? Or, have I configured something wrong?
2. Is there any way to not wrap my query within parentheses? Or can I change how Splunk is quoting my column names?

[kranzrm · Accepted solution]

I have found a solution to this problem. The database must have ANSI_QUOTES enabled in the sql_mode parameter.

From: https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_ansi_quotes

 ANSI_QUOTES

 Treat " as an identifier quote character (like the ` quote character) and not as a string quote character. You can still use ` to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.

Relevant StackOverflow Post: https://stackoverflow.com/a/14123649/1440715

For AWS RDS this needs to be set within a parameter group. This cannot be set with the SQL query used on a vanilla MySQL databse. Once the parameter is set (and the group is applied) the database instance needs to be rebooted in order for this setting to take effect.

Working with DB Parameter Groups: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html

[richgalloway]

@kranzm, If your problem is resolved, please accept the answer (it's OK to accept your own answer) to help future readers.