Input crashes on TZInfo::AmbiguousTime Error when setting jdbc_default_timezone

[proligde]

Hi there! First of all - thanks a lot for this plugin - it's great!

However, when using my MySQL DB's timestamp in elasticsearch/kibana I noticed it was off by 1h since the MySQL timestamp is interpreted as UTC but in fact is CET.

So i added:
jdbc_default_timezone => "Europe/Berlin"

But now my logstash-pipeline crashes fatal resulting in an endless loop of trying to read data from MySQL with this error:

A plugin had an unrecoverable error. Will restart this plugin.
  Plugin: <LogStash::Inputs::Jdbc jdbc_connection_string=>"jdbc:mysql://localhost:3306/MYDBNAME?zeroDateTimeBehavior=convertToNull", jdbc_user=>"MYDBUSER", jdbc_password=><password>, jdbc_driver_library=>"/driver/mysql-connector-java-5.1.35-bin.jar", jdbc_driver_class=>"com.mysql.jdbc.Driver", jdbc_default_timezone=>"Europe/Berlin", statement_filepath=>"/config/queries/shop_order_item.sql", type=>"shop_order_item", codec=><LogStash::Codecs::Plain charset=>"UTF-8">, jdbc_paging_enabled=>false, jdbc_page_size=>100000, jdbc_validate_connection=>false, jdbc_validation_timeout=>3600, jdbc_pool_timeout=>5, sql_log_level=>"info", parameters=>{"sql_last_value"=>1970-01-01 00:00:00 UTC}, last_run_metadata_path=>"/var/lib/logstash/.logstash_jdbc_last_run", use_column_value=>false, clean_run=>false, record_last_run=>true, lowercase_column_names=>true>
  Error: TZInfo::AmbiguousTime: 2015-10-25T02:49:45+00:00 is an ambiguous local time. {:level=>:error}

I'm aware that this might probably more like an tzinfo related problem (tzinfo/tzinfo#32) but I don't see any other possibility to make it work correctly.

Exporting a UNIX-timestamp in MySQL and using a date input filter combined with the "unix" timestamp parser is (for whatever reason) painfully slow resulting in jammed elasticsearch input queues.

Best - Max

[razafinr]

Hi!

I am having the same error:

Error: TZInfo::AmbiguousTime: 2015-10-25T02:10:03+00:00 is an ambiguous local time

It is quite a problem that this is causing logstash to restart endlessly.

[proligde]

As far as I understand the issue logstash-input-jdbc would have to let us configure (like tzinfo does it) what fallback (pick first or second point in time) to perform in case of an ambigous time stamp. As long as it doesn't to this it's somewhat "correct" to fail fatally.

Here is my understanding of the whole thing - please correct me if I'm wrong:

• We have source timestamps in let's say CE(S)T timezone supporting daylight saving time.
• Events with timestamps during summer/winter-time shifting (twice a year) can't be mapped precisely because at 3am the clocks are set back to 2am, so every timestamp between 2am and 3am exists twice.
• Thus events within this time range can't be mapped correctly and throw an fatal error.
• As far as I understand tzinfo has an option to force such timestamps to either the earlier or the later timestamp equivalent in UTC.
• When we could configure that in logstash-input-jdbc's yml as well to pass it to tzinfo we could get rid of the problem for the cost of losing the exakt time during summer/winter-time shifting which can't be avoided due to logical reason pointed out before.

I know - that's all evil stuff and everyone should only write UTC timestamps to avoid such awkwardness but in reality there are a lot of databases out there logging stuff in local timezones that support daylight saving time shifts - so support for that would be great :-)

[razafinr]

I totally agree with you (timezones are just so annoying to handle).

Probably the local_to_utc conversion is global to all logstash plugins and not for the jdbc input only ? I don't know if it is a specific fix for this plugin or for logstash itslef.

[proligde]

Good point - I don't know either. If one of the contributors of this plugin could confirm or negate that we could keep this ticket here or move it to the proper elastic project instead.

I guess in the end there should be a global or plugin specific setting, leading to these calls:

tz.local_to_utc(d) { |periods| periods.first }
or
tz.local_to_utc(d) { |periods| periods.last }

as mentioned in the linked tzinfo issue.

[mbertani]

I have the same issue here. I am importing data from a database with local timestamps. Even handling the exception and returning a _dateparsefailure in logstash would help. Then we could redirect the output to a file an parse accordingly.

[razafinr]

Coming back to this issue, I think that the problem comes from the named_timezoneextension used by Sequel.

As you can see here:

https://github.com/jeremyevans/sequel/blob/ac925ce9556f33d56f49b84d905d307c6a621716/lib/sequel/extensions/named_timezones.rb

There is an attribute tzinfo_disambiguatorwhich could be used to remove the ambiguity by choosing wich period to use periods.first or periods.last in the application.

If I could send a PR I would, but I am really unfamiliar with ruby language so I hope this will help to fix this issue.

[hameno]

Any update or workaround for this? My import job is now stuck with this error...

[passing]

we are using the following workaround to convert CET dates without timezone to UTC in the SQL statement (running against oracle database):

select from_tz(cast(<column> as timestamp), 'CET') at time zone ('UTC') "@timestamp"
from <table>
where ...

then ensure that timezone setting of the local machine or "jdbc_default_timezone" is UTC

maybe something similar is possible with MySQL?

[yaauie]

Unfortunately, the setting linked for supplying a tzinfo_disambiguator is a global setting for Sequel's named timezone extension, which means we don't have a way of configuring a particular input's handling.

In a brief spike, I have created a timezone proxy object using SimpleDelegator, whose period_for_local has an implicit preference for DST being either enabled or disabled, which seems to work:

# Quacks like a Timezone
class TimezoneWrapper < SimpleDelegator
  ##
  # Wraps a ruby timezone object in an object that has an explicit preference in time conversions
  # either for or against having DST enabled.
  #
  # @param timezone [String,TZInfo::Timezone]
  # @param dst_enabled_on_overlap [Boolean] (default: nil) when encountering an ambiguous time,
  # declare a preference for selecting the option with DST either enabled or disabled.
  def self.wrap(timezone, dst_enabled_on_overlap)
    timezone = TZInfo::Timezone.get(timezone) if timezone.kind_of?(String)
    new(timezone, dst_enabled_on_overlap)
  end

  ##
  # @api private
  def initialize(timezone, dst_enabled_on_overlap)
    super(timezone) # SimpleDelegator
    @dst_enabled_on_overlap = dst_enabled_on_overlap
  end

  ##
  # @override `Timezone#period_for_local`
  # inject an implicit preference for DST being either enabled or disabled if called
  # without an explicit preference
  def period_for_local(value, dst_enabled_on_overlap=nil, &global_disambiguator)
    dst_enabled_on_overlap = @dst_enabled_on_overlap if dst_enabled_on_overlap.nil?
    __getobj__.period_for_local(value, dst_enabled_on_overlap, &global_disambiguator)
  end
end

In this way, we could set the Database#timezone=(tz) with our proxy object, which would resolve ambiguous timestamps as configured:

if @jdbc_default_timezone
  @database.timezone = TimezoneWrapper.wrap(@jdbc_default_timezone, @jdbc_default_timezone_dst_enabled_on_overlap)
end

A couple open questions:

• what should this has-opinions-on-DST timezone proxy be called?
• how should we expose the option? jdbc_default_timezone_dst_enabled_on_overlap is a bit cumbersome.

(cc: @guyboertje @jakelandis)

[marbin]

I agree with @mbertani: Would help a lot just to get a proper exception and a _dateparsefailure or something similar. Now, logstash silently crashes and drops events. Took me a long time to figure out what was going on.

[marbin]

I don't really understand why this is not labeled as a bug. If I understand this correctly, the jdbc_default_timezone cannot be used in countries using daylight saving time.