Jdbc_static: Getting a sql syntax error I did'nt write

Elastic Stack Logstash
1

With logstash I am trying to Extract some tables, Transform them locally on the logstash mashine, and then Load the result to Elasticsearch. The reason for this solution is due to very limited computing power on the source server, a MariaDB.

  • I have tested the input{} separately, it works, so the connection to the mariadb is sound.
  • I have tested the jdbc_static filter against a microsoftSQL server. So logstash has writing privileges in is current environment.
  • I have tested the SQL syntax on the MariaDB server directly
  • I'm running logstash 6.8 and java 8 (java version "1.8.0_211")
  • I have tried earlier versions of mariadb jdbc connection (mariadb-java-client-2.4.2.jar, mariadb-java-client-2.2.6-sources, mariadb-java-client-2.3.0-sources)

I am familier with this logstash 6.2 issue but I am using logstash 6.8
My conf file

input {
  jdbc {
    
    jdbc_driver_library => "C:/Logstash/logstash-6.8.0/plugin/mariadb-java-client-2.4.2.jar"
    jdbc_driver_class => "Java::org.mariadb.jdbc.Driver"
    
    jdbc_connection_string => "jdbc:mariadb://xx.xx.xx
    jdbc_user => "me"
    jdbc_password => "its secret"

    schedule => "* * * * *"
    statement => "SELECT unqualifiedversionid__ FROM   AuditEventFHIR WHERE myUnqualifiedId = '0000134b-fc7f-4c3a-b681-8150068d6dbb'"

  }
}
filter {
   jdbc_static {
        loaders => [ 
          {
            id => "AuditEventFHIR"
            query => "SELECT  
                    myUnqualifiedId
                    ,unqualifiedversionid__
                    ,type_
                    FROM AuditEventFHIR
                    where myUnqualifiedId = '0000134b-fc7f-4c3a-b681-8150068d6dbb'
                    "
            local_table => "l_ae"
          }
        ]

        local_db_objects => [ 

          {
            name => "l_ae"
            index_columns => ["myUnqualifiedId"]
            columns => [
              ["myUnqualifiedId", "varchar(256)"],
              ["unqualifiedversionid__", "varchar(24)"],
              ["type_", "varchar(256)"]
            ]
          }

        ]
 
        local_lookups => [ 
          {
            id => "rawlogfile"
            query => "
               select myUnqualifiedId from l_ae
                "  
            target => "sql_output"
          }
        ]

        jdbc_driver_library => "C:/Logstash/logstash-6.8.0/plugin/mariadb-java-client-2.4.2.jar"
        jdbc_driver_class => "Java::org.mariadb.jdbc.Driver"
        
        jdbc_connection_string => "jdbc:mariadb://xx.xx.xx.xx"
        jdbc_user => "me"
        jdbc_password => "its secret"
        }
}
output {
    stdout { codec => rubydebug }
}

and this is the errors

[ERROR][logstash.filters.jdbc.readonlydatabase] Exception occurred when executing loader Jdbc query count {:exception=>"Java::JavaSql::SQLSyntaxErrorException: (conn=1490) 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 8", :backtrace=>["org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(org/mariadb/jdbc/internal/util/exceptions/ExceptionMapper.java:242)", "org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(org/mariadb/jdbc/internal/util/exceptions/ExceptionMapper.java:171)", "org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(org/mariadb/jdbc/MariaDbStatement.java:248)", "org.mariadb.jdbc.MariaDbStatement.executeInternal(org/mariadb/jdbc/MariaDbStatement.java:338)", "org.mariadb.jdbc.MariaDbStatement.executeQuery(org/mariadb/jdbc/MariaDbStatement.java:512)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:425)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:292)"]}

I don't understand this error. I don't have a LIMIT in my sql code

Got several other errors, but I expect that fixing the first will fix the rest

3

Does this help?

4

Its looks very related, but I am not sure how to use "The answer"

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

5

I am guessing here, since I do not have a DB to test with, but I would prefix that to your select statement.

6

I don't have a solution, but I am getting wiser SET GLOBAL sql_mode = 'ANSI_QUOTES'; changes the database setup

From https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
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 they are interpreted as identifiers.

As written here, the offending piece of sql code is a count number of rows in the table that I am trying to load with jdbc_static{loaders... When downloading a table, it needs a checksum to know when its complete. That is why logstash-input-jdbc works on this server, but logstash-filter-jdbc_static don't. logstash-input-jdbc don't need the checksum

I tried to implement the SET GLOBAL sql_mode = 'ANSI_QUOTES'; into my query statement, but you need to be a superuser to make global database changes.

7

I got to change the setting on our own mariaDB database to sql_mode = 'ANSI_QUOTES' and then the jdbc_static script works on our own setup

1 Like
8

Good to know. The next time I see a question about this error I will know which post to link to.

1 Like
9

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