Logstash JDBC Plugin: IllegalArgumentException "HOUR_OF_DAY: 2 -> 3"


(J C ) #1

Hello. I am trying to import a MySQL table into Elasticsearch using Logstash (version 5.6.4). I get the following error:

[2018-11-27T12:48:46,216][WARN ][logstash.inputs.jdbc     ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaLang::IllegalArgumentException: HOUR_OF_DAY: 2 -> 3>}

Here is my config file:

input {
	jdbc {
		jdbc_connection_string => "jdbc:mysql://localhost:3306/db_name?useLegacyDatetimeCode=false&serverTimezone=America%2FChicago"
		jdbc_user => "mysqlUser"
		jdbc_password => "mysqlPassword"
		jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.11.jar" 
		jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
		sql_log_level => "debug"
		statement => "SELECT * FROM weights LEFT JOIN users ON users.user_id WHERE weights.user_id=users.user_id"
		type => "weights" 
	}
}

filter {
    mutate { replace => { type => "%{type}" } }
    if [type] == "weights" {
    	mutate {
            add_field => {
    	        "index_timestamp" => "%{phone_tstmp}"
            }
        }
    }
	date {
        match => ["index_timestamp", "ISO8601", "yyyy-MM-dd'T'HH:mm:ss.SSS"]
        target => ["@timestamp"]
    }
	date {
        match => [ "index_timestamp", "ISO8601" ]
    	target => ["index_timestamp"]
    }
}

output {
    elasticsearch {
    	"hosts" => "https://user:pw@server:9200/"
        "index" => "indexName"
        "document_type" => "%{type}"
    }
}

I tried setting the sql_log_level to debug, but I don't see any new messages in the Logstash log file. If I change the timezone to UTC in the jdbc_connection_string, I'm able to import the table successfully. However, I need the timezone to be America/Chicago. When the exception shows up in the log file, the pipeline immediately stops:

[2018-11-27T12:48:46,530][WARN ][logstash.agent           ] stopping pipeline {:id=>"main"}                                           

How can I go about debugging this? Thanks!