logstash-input-jdbc: MySQL / MariaDB Error with pagination

As soon as I active the pagination, I get the following error:

Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLSyntaxErrorException: 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
Query is:
SELECT count(*) AS "COUNT" FROM (SELECT ...) AS "T1" LIMIT 1>, :level=>:warn}

MariaDB / MySQL doesn’t like the double quotes on T1. I use the mariadb-java-client-1.3.2.jar library.

By the way, Is that count query really needed? On my database (3B+ rows), It would take a really long time to complete.

About this issue

Original URL
State: open
Created 9 years ago
Reactions: 1
Comments: 15 (4 by maintainers)

Most upvoted comments

I have the same error with MariaDB 10.1.x and Logstash 5.x. I use the following settings with the MariaDB connector to get it work: jdbc_connection_string => "jdbc:mariadb://127.0.0.1:3306/database?sessionVariables=sql_mode=ANSI_QUOTES"

Note the “sql_mode=ANSI_QUOTES” From the MariaDB documentation: https://mariadb.com/kb/en/mariadb/sql-mode/

ANSI_QUOTES: Changes " to be treated as `, the identifier quote character. This may break old MariaDB applications which assume that " is used as a string quote character.

morganseznec on Mar 22, 2017