Search code examples
javajdbclogstashjodatime

Exception when executing JDBC query - Illegal instant due to time zone offset transition with Logstash

I have the following JDBC input:

input {
  jdbc {
    id => "mypipeline"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_driver_library => "/usr/share/logstash/drivers/mysql-connector-j-8.0.32.jar"
    jdbc_connection_string => "my-connection-string"
    jdbc_password => "my-user"
    jdbc_user => "my-pass"
    jdbc_fetch_size => 5000
    schedule => "*/5 * * * *"
    statement => "SELECT l.id, l.name, l.log_date FROM logs l WHERE l.created_at >= :sql_last_value"
  }
}

The log_date column is as follows:

log_date DATE NULL DEFAULT NULL,

Warning; created_at and log_date are not the same column. The query that's being executed is as follows:

SELECT l.id, l.name, l.log_date FROM logs l WHERE l.created_at >= '1970-01-01 02:00:00'

which is wrong, since Istanbul does not go into DST, should be '1970-01-01 03:00:00' For values such as 1949-04-10 for log_date, I am receiving this error:

[2023-06-02T11:50:30,470][WARN ][logstash.inputs.jdbc     ][main][mypipeline] Exception when executing JDBC query {:exception=>Sequel::DatabaseError, :message=>"Java::OrgJodaTime::IllegalInstantException: Illegal instant due to time zone offset transition (daylight savings time 'gap'): 1949-04-10T00:00:00.000 (Europe/Istanbul)", :cause=>"#<Java::OrgJodaTime::IllegalInstantException: Illegal instant due to time zone offset transition (daylight savings time 'gap'): 1949-04-10T00:00:00.000 (Europe/Istanbul)>"}

How do I resolve this?

Solution

  • "Illegal instant due to time zone offset transition" - Istanbul does not currently change between daylight saving time and standard time, but it has done in the past.

    It is currently observing what is effectively DST year-round - and has done so since March 27th 2016, when the clocks moved forward by 1 hour from a UCT timezone offset of +2 hours to +3 hours.

    More specifically regarding the "Illegal instant" error: In 1949 on 10th April at midnight, the clocks moved forward by 1 hour - so the local time of 1949-04-10 00:00 never actually happened. That is why it is a "gap" value, as noted in the error message.

    This time zone data is captured in the IANA Time Zone Database (TZDB), which I assume is what your application uses, behind the scenes (e.g. if you are using Joda-Time).

    You can use an online tool such as this one (and probaby others):

    enter image description here

    WARNING - I cannot vouch for the accuracy of the data on this web site, but it does match the TZDB rule for this specific example, which I extracted as follows using Java (which uses TZDB data):

     - on 1949-04-10 at 00:00
 - the clocks moved forward by 1 hr (daylight saving)
 - from TZ offset +02:00 to offset +03:00

    Regarding your comment:

    "should be '1970-01-01 03:00:00'"

    For 1970, the TZDB indicates that there were no adjustments made. So, the effective offset from UTC was the one previously made in 1964:

     - on 1964-10-01 at 00:00
 - the clocks moved back by 1 hr
 - from TZ offset +03:00 to offset +02:00

    And that +02:00 is what you are (correctly) seeing in your data for that 1970 datetime.

    If you want to avoid using a datetime which falls into one of these gaps (caused by the clocks moving forward), then you can do that programmatically - for example:

    Other mainstream languages should have similar capabilities.

    Also, since you mentioned Joda, maybe you can consider using Java's java.time classes now (if you have a suitable version of Java), instead of using Joda-Time:

    Note that from Java SE 8 onwards, users are asked to migrate to java.time (JSR-310) - a core part of the JDK which replaces this project.

    Specific solution to the case with Logstash

    Adding jdbc_default_timezone => "GMT" to Logstash configuration and altering the timezone of the host machine will make Logstash to query the database without getting this error.