MySQL / MariaDB Error with pagination

[benjaminrigaud]

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.

[jerkkao]

Is your SQL statement ended with a semicolon? If it is, try to remove it.

[untergeek]

@benjaminrigaud have you tried this with the recent updates to the plugin?

[joschi99]

I have the same error using MariaDB 5.5 with latest logstash 2.3.3 and mariadb jdbc 1.4.6. There is no ";" at the end of the SQL.

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 * from logs) AS "T1" LIMIT 1: SELECT count(*) AS "COUNT" FROM (SELECT * from logs) AS "T1" LIMIT 1 {:level=>:error, :file=>"sequel/database/logging.rb", :line=>"87", :method=>"log_each"}
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 * from logs) AS "T1" LIMIT 1>, :level=>:warn, :file=>"logstash/plugin_mixins/jdbc.rb", :line=>"219", :method=>"execute_statement"}

[joschi99]

I tried the same using the actual mysql 5.5 jdbc connector on maria db 5.5 and works.

[gm06041]

i have the same error using MariaDB 5.5. i think the problem is due to " (double quotation). when i query with including double quotation, server returns error.

Error Code: 1064. 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 3

but, when i query with without double quotation, server returns no error.

so, as is: SELECT count() AS "COUNT" FROM (SELECT * from logs) AS "T1" LIMIT 1 to be: SELECT count() AS "COUNT" FROM (SELECT * from logs) AS T1 LIMIT 1

[joschi99]

Try to use the Mysql jdbc connector with your MariaDB. For me this works.

[ljufa]

I have same issue with mariadb driver (actual driver version 1.5.5) while mysql driver works fine. Unfortunately I can't use mysql driver because of restrictive license. This query is generated by jdbc plugin so please change code to not put double quotes on T1 when mariadb dialect in use.

[PhaedrusTheGreek]

Also see https://github.com/logstash-plugins/logstash-input-jdbc/issues/138

[guyboertje]

Reading this knowledge base article on MariaDB identifiers one can see that identifiers can be quoted and must be quoted if the alias contains a space, is a reserved word or has special characters in it. The default character for quoting is the backtick. The article says that the MariaDB system can be put into ANSI quotes " mode but this is not something we should recommend.

Because the underlying Sequel gem that we use will use backticks for mysql we need to ensure that the adapters/jdbc/mysql.rb is loaded by making the connection string start with jdbc:mysql:

I would suggest using the mariadb driver jar with a mysql connection_string preamble, e.g. jdbc:mysql://localhost:port/db

[guyboertje]

@ljufa Read ^. Please try the MariaDB driver jar with the mysql connection string.

[ljufa]

Getting error below when use jdbc:mysql://localhost:port/db connection string with mariadb driver.

Unable to connect to database. Tried 1 times", :error_message=>"com.mysql.jdbc.Driver not loaded, try installing jdbc-mysql gem", :level=>:error}

Also I forgot to say T1 quote error occurs only when jdbc_paging_enabled => "true"

[guyboertje]

@ljufa I see.

In the days since my last comment, I have read and traced the source code quite extensively. I now know that Sequel will parse the connection string and when it sees the mysql: part will load mysql specific code that "flavors" its code for mysql - unfortunately this will try to load the com.mysql.jdbc.Driver even though the MariaDB driver is loaded by our plugin code.

This means that to really use the MariaDB driver jar, Sequel will need to be extended. This will not happen anytime soon.

[ljufa]

Thanks for reply, in early mariadb driver versions thy used to provide com.mysql.jdbc.Driver class in the jar file. That could be reason why it worked before (if did).

I'll wait for patch on logstash or mariadb side :)

[morganseznec]

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.

[yangjm]

Thank you @morganseznec , your solution works well for me.