SQLSyntaxErrorException


(Harald Hödl) #1

Hi,

im facing an issue while running logstash in combination with mariadb.
I have already figured out that the statement without quotation marks is working.

logstash 5.6.2

Now my Question how can i remove the "", marked bold in the log file ?

console:

MariaDB [(none)]> SELECT count(*) AS COUNT FROM (SELECT * FROM gsc.shecz where shecz_id > 0) AS T1 LIMIT 1
-> ;
+--------+
| COUNT |
+--------+
| 480290 |
+--------+
1 row in set (0.20 sec)

MariaDB [(none)]>

My Config:

input {
jdbc {
jdbc_driver_library => "/usr/data/mariadb-java-client-2.1.2.jar"
jdbc_driver_class => "org.mariadb.jdbc.Driver"
jdbc_connection_string => "jdbc:mariadb://10.192.242.173:3306/gsc"
jdbc_user => ""
jdbc_password => "
"
schedule => "* * * * *"
statement => "SELECT * FROM gsc.shecz where shecz_id > :sql_last_value"
use_column_value => true
tracking_column => "shecz_id"
tracking_column_type => "numeric"
clean_run => true
last_run_metadata_path => "/etc/logstash/conf.d/.logstash_jdbc_last_run"

jdbc_paging_enabled => "true"
jdbc_page_size => "100000"

}
}
output {
elasticsearch {
hosts => ["10.192.242.173:9200"]
index => "gsc.shecz"
}
}

Error:

[2017-10-06T10:07:00,085][ERROR][logstash.inputs.jdbc ] Java::JavaSql::SQLSyntaxErrorException: (conn=235) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"T1" LIMIT 1' at line 1: SELECT count(*) AS "COUNT" FROM (SELECT * FROM gsc.shecz where shecz_id > 0) AS "T1" LIMIT 1
[2017-10-06T10:07:00,087][WARN ][logstash.inputs.jdbc ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLSyntaxErrorException: (conn=235) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"T1" LIMIT 1' at line 1>}

Thanks in Advance
Harald


(Harald Hödl) #2

The Answer is:
SET GLOBAL sql_mode = 'ANSI_QUOTES';


(system) #3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.