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

Question

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)

My config 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 => "auditevent"
            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 }
}

I am getting this and several other errors, but I suspect fixing the first will fix the rest. But key is that no were in my code are the words "LIMIT 1"

[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)"]}

Answer 1

The jdbc_static loader, makes a hidden SQL query select count(*) from table limit 1 to get a checksum when downloading the table. This query contains " and mariaDB don't like that.

UNLESS you add 'ANSI_QUOTES' to the sql_mode

Batch command

SET GLOBAL sql_mode = 'ANSI_QUOTES'

Another option is to set the session to allow ansi_quotes

jdbc_connection_string => "jdbc:mariadb://xx.xx.xx/databasename?sessionVariables=sql_mode=ANSI_QUOTES"