Logstash input jdbc plugin with informix database not working with timestamp

Elastic Stack Logstash
1

Hello,

I am using the JDBC input plugin to perform the sync between informix and Elasticsearch data store. I am using the "sql_last_value" criteria to distinguish old data and new data. I have a created_date field in the table that I use.

==
create table movies_new_2 (
MovieID int,
MovieName varchar(30),
Country varchar(10),
Year int,
created_date datetime YEAR TO FRACTION(5) not null DEFAULT
DATETIME(0001-01-01 17:00:00.000) YEAR TO FRACTION(5)
)

logstash jdbc config is as below:

==

input {
jdbc {

    jdbc_driver_library => "/opt/jenzabar/jx/logstash-2.4.0/lib/ifxjdbc-3.70.JC5.jar"
    jdbc_driver_class => "com.informix.jdbc.IfxDriver"
    jdbc_connection_string => "jdbc:informix-sqli://abc.net:1234/test:informixserver=xyz"
    jdbc_user => "user"
    jdbc_password => "xxxxx"
    jdbc_fetch_size => 1000
    record_last_run => "true"
    statement => "select movieid, MovieName, Country, Year, created_date from movies_new_2 WHERE created_date  > :sql_last_value"
    clean_run => "true"
    use_column_value => "true"
    tracking_column => "created_date"

    }

}

output {

    stdout {
             codec => json_lines
    }

}

However, when I try to execute this I get the following error. note similar configuration worked fine with mysql database.

==
Settings: Default pipeline workers: 8
Pipeline main started
Java::JavaSql::SQLException: Extra characters at the end of a datetime or interval.: select movieid, MovieName, Country, Year, created_date from movies_new_2 WHERE convert_tz(created_date) > '2016-09-30 06:54:35.655000' {:level=>:error}
Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: Extra characters at the end of a datetime or interval.>, :level=>:warn}
Pipeline main has been shutdown
stopping pipeline {:id=>"main"}

==

subesquent I ran the config with ID as the sql_last_value. things started working fine. I switched back to date with clean_run set to true. additionally physically removed ..logstash_jdbc_last_run file. now I get a different error.

==
Java::JavaSql::SQLException: It is not possible to convert between the specified types.: select movieid, MovieName, Country, Year, created_date from movies_new_2 WHERE created_date > 0 {:level=>:error}

turns out sql_last_value is not initialized. what can I try to get past this?

regards
Prasad

2

appreciate any inputs here.