Error with Cassandra and jdbc_paging_enabled => true


(Giuseppe) #1

Hi,
I use Logstash with jdbc plugin and Cassandra.

Elasticsearch: 6.1.2
logstash-input-jdbc: 4.3.3
Cassandra: 3.11.1
Jdbc driver: dbschema.CassandraJdbcDriver

This is my config:
input {
jdbc {
clean_run => "false"
jdbc_connection_string => "jdbc:cassandra://0.0.0.0:9042"
jdbc_user => "myuser"
jdbc_password => "mypass"
jdbc_driver_library => "/home/logstash_driver/CassandraDBSchema/cassandrajdbc1.1.jar"
jdbc_driver_class => "com.dbschema.CassandraJdbcDriver"
statement => "select * from mydatabase.products_to_elastic WHERE lastmodified > :sql_last_value ALLOW FILTERING"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
schedule => "* * * * *"
tracking_column => "lastmodified"
tracking_column_type => "timestamp"
record_last_run => "true"
sql_log_level => "info"
jdbc_default_timezone => "UTC"
}
}

I have this error when i enable pagination:

[2018-01-30T12:53:03,090][ERROR][logstash.inputs.jdbc ] Java::ComDatastaxDriverCoreExceptions::SyntaxError: line 1:32 no viable alternative at input '(' (...() AS "COUNT" FROM [(]...): SELECT count() AS "COUNT" FROM (select * from mydatabase.products_to_elastic WHERE lastmodified > '2018-01-30 11:59:02.709784' ALLOW FILTERING) AS "T1" LIMIT 1
[2018-01-30T12:53:03,097][WARN ][logstash.inputs.jdbc ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComDatastaxDriverCoreExceptions::SyntaxError: line 1:32 no viable alternative at input '(' (...(*) AS "COUNT" FROM [(]...)>}

Is possible than other DB support this query, but with Cassandra it doesn't work.

If I set jdbc_paging_enabled => "false"

I don't have this error, but I obtain another error:

[2018-01-30T13:31:00,462][ERROR][logstash.inputs.jdbc ] Java::ComDatastaxDriverCoreExceptions::InvalidQueryException: Unable to coerce '2018-01-30 11:59:02.709784' to a formatted date (long): select * from mydatabase.products_to_elastic WHERE lastmodified > '2018-01-30 11:59:02.709784' ALLOW FILTERING
[2018-01-30T13:31:00,464][WARN ][logstash.inputs.jdbc ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComDatastaxDriverCoreExceptions::InvalidQueryException: Unable to coerce '2018-01-30 11:59:02.709784' to a formatted date (long)>}

It seems that the cassandra date and logstash are not compatible.
If I try this query directly on Cassandra doesn't work: ( select * from mydatabase.products_to_elastic WHERE lastmodified > '2018-01-30 11:59:02.709784' ALLOW FILTERING )

If I change the date format it works well: (select * from mydatabase.products_to_elastic WHERE lastmodified > '2018-01-30 11:59:02.709' ALLOW FILTERING )

'2018-01-30 11:59:02.709784' -> '2018-01-30 11:59:02.709'

I try to use filters to change the data, but filter works with data after the input.
it's possible that I have to change any jdbc configurations, but I don't know where.


(system) #2

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