Jdbc_static not looking up

[michaeleino]

after a long time, getting the plugin starts... it can't resolve the actual values from the derby DB!

local_lookups => [
  {
id => "get-status"
query => "SELECT name from localstatus WHERE id = :stID"
parameters => {stID => "[statusID]"}
target => "statusdata"
  }
  ]

I got this in the logstash logs:

[2019-01-16T23:54:23,503][DEBUG][logstash.filters.jdbc.lookup] Executing Jdbc query {:lookup_id=>"get-status", :statement=>"SELECT name from localstatus WHERE id = :statusID", :parameters=>{:statusID=>"1"}}
[2019-01-16T23:54:23,504][WARN ][logstash.filters.jdbc.lookup] Exception when executing Jdbc query {:lookup_id=>"get-status", :exception=>"Java::JavaSql::SQLSyntaxErrorException: Comparisons between 'INTEGER' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')", :backtrace=>["org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(org/apache/derby/impl/jdbc/SQLExceptionFactory)", "org.apache.derby.impl.jdbc.Util.generateCsSQLException(org/apache/derby/impl/jdbc/Util)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.EmbedConnection.handleException(org/apache/derby/impl/jdbc/EmbedConnection)", "org.apache.derby.impl.jdbc.ConnectionChild.handleException(org/apache/derby/impl/jdbc/ConnectionChild)", "org.apache.derby.impl.jdbc.EmbedStatement.execute(org/apache/derby/impl/jdbc/EmbedStatement)", "org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(org/apache/derby/impl/jdbc/EmbedStatement)"]}

When I manually set the query statement to:

query => "SELECT name from localstatus WHERE id = 1"

it returns what is should return!

Here is the tables:

local_db_objects => [
{
name => "localstatus"
index_columns => ["id"]
columns => [
["id", "int"],
["name", "varchar(255)"]
]
}
]
loaders => [{
id => "get-status"
query => "SELECT id, name FROM status ORDER BY id"
local_table => "localstatus"
}
]

logstash version: docker.elastic.co/logstash/logstash:6.5.4
with mysql driver:
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_driver_library => "/usr/share/logstash/config/jlib/mysql-connector-java-5.1.47.jar"

[Badger]

OK, so

query => "SELECT name from localstatus WHERE id = 1"

works. But

query => "SELECT name from localstatus WHERE id = :stID"
parameters => {stID => "[statusID]"}

does not. The error message says

Comparisons between 'INTEGER' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')"

The way I read it :stID is CHAR (UCS_BASIC) and id is INTEGER. You want :stID to be an integer. The error message is telling you to fix that using a CAST.

You are left with an SQL question, not a logstash question.

[michaeleino]

Thnks badger... I was just wondering which of them are not integer ! the event field, or the imported mysql DB..
However I'm getting the statusID as statusID=%{NUMBER:statusID} also tried to do it as statusID=%{INT:statusID} it didn't work, as i was supposing it should capture the event variable as INT ...

adding below solved the issue... thanks a lot

mutate {
convert => {
"serviceID" => "integer"
"statusID" => "integer"
}
}

OR optionally, we can set it on the capture directly like:

%{INT:serviceID:int}

To get a number field, see this paragraph in the grok filter docs:

Optionally you can add a data type conversion to your grok pattern. By default all semantics are saved as strings. If you wish to convert a semantic’s data type, for example change a string to an integer then suffix it with the target data type. For example %{NUMBER:num:int} which converts the num semantic from a string to an integer. Currently the only supported conversions are int and float.