# Data stopped coming after DST change (Logstash JDBC input)

**URL:** <https://discuss.elastic.co/t/data-stopped-coming-after-dst-change-logstash-jdbc-input/301562>\
**Category:** Logstash\
**Created:** [April 4, 2022, 9:10pm UTC](https://discuss.elastic.co/t/data-stopped-coming-after-dst-change-logstash-jdbc-input/301562 "2022-04-04T21:10:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\
**Post date:** [April 4, 2022, 9:10pm UTC](https://discuss.elastic.co/t/data-stopped-coming-after-dst-change-logstash-jdbc-input/301562/1 "2022-04-04T21:10:00Z")

</div>

Hi,

We had the DST change in Australia.  
[**It ends at 2am (which is 3am Daylight Saving Time) on the first Sunday in April** , when clocks are put back one hour.]

The data came in till 1:59 AM in night (April 3rd, 2022) and then stopped.

I am at loss as to what might have gone wrong. I am not even using the time of record creation as basis of pulling the new data.

Layout:  
I am using the JDBC input plugin.

```auto
tracking_column => "resultnumber"
use_column_value => true
tracking_column_type => "numeric"
schedule => "*/5 * * * *"
jdbc_paging_enabled => "false"
jdbc_default_timezone => "Australia/Sydney"
jdbc_page_size => "500"
last_run_metadata_path => "logstash_jdbc_last_run_tracking"

```

As evident I am not using the time of record insertion for pulling the data. I am simply using the increasing resultnumber as the **sql\_last\_value**.

There are records which are available having values greater than the resultnumber stored in the tracking file **logstash\_jdbc\_last\_run\_tracking**.

Here is the redacted query string if it helps.

```auto
select resultnumber,resultstatus,createdon where resultnumber > :sql_last_value and createdon > '2021-01-01' order by resultnumber ASC

```

Any ideas?  
I will update the question if I find something during my investigation.

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [April 4, 2022, 9:27pm UTC](https://discuss.elastic.co/t/data-stopped-coming-after-dst-change-logstash-jdbc-input/301562/2 "2022-04-04T21:27:50Z")

</div>

Are any exceptions logged? Can you try removing any date/time fields from the select? For example, does

```auto
select resultnumber,resultstatus where resultnumber > :sql_last_value and createdon > '2021-01-01' order by resultnumber ASC

```

work? Date/time columns are [automatically converted](https://github.com/logstash-plugins/logstash-input-jdbc/blob/878a73eb67b68366fac74fcc65e9cadd6cf44766/lib/logstash/plugin_mixins/jdbc/jdbc.rb#L287) to Logstash timestamps. I wouldn't expect that to cause a problem when DST ends (times between 2 and 3 AM are ambiguous) rather when DST starts (times between 2 and 3 do not exist and attempts to parse them causes exceptions).

The fact that it worked until 1:59 (when times became ambiguous) rather than 2:59 (when time went backwards) probably tells us something, but I am not sure what.

---

<div class="post-metadata">

**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\
**Post date:** [April 5, 2022, 1:24am UTC](https://discuss.elastic.co/t/data-stopped-coming-after-dst-change-logstash-jdbc-input/301562/3 "2022-04-05T01:24:53Z")

</div>

I got some errors but not sure if this is related. Still investigating.

```auto
{ rufus-scheduler intercepted an error:
     job:
       Rufus::Scheduler::CronJob "*/5 * * * *" {}
     error:
       
       Sequel::InvalidValue
       TZInfo::AmbiguousTime: 2022-04-03 02:00:29 is an ambiguous local time.
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/tzinfo-2.0.4/lib/tzinfo/timezone.rb:525:in `period_for_local'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/extensions/named_timezones.rb:115:in `convert_input_datetime_other'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/timezones.rb:136:in `convert_input_datetime_no_offset'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/timezones.rb:185:in `convert_input_timestamp'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/timezones.rb:94:in `convert_timestamp'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/database/misc.rb:312:in `to_application_timestamp'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/adapters/jdbc.rb:715:in `timestamp_convert'
         org/jruby/RubyMethod.java:123:in `call'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/adapters/jdbc.rb:825:in `process_result_set'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/adapters/jdbc.rb:755:in `block in fetch_rows'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/adapters/jdbc.rb:268:in `block in execute'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/adapters/jdbc.rb:704:in `statement'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/adapters/jdbc.rb:263:in `block in execute'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/connection_pool/threaded.rb:92:in `hold'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/database/connecting.rb:269:in `synchronize'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/adapters/jdbc.rb:262:in `execute'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/dataset/actions.rb:1093:in `execute'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/adapters/jdbc.rb:755:in `fetch_rows'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.51.0/lib/sequel/dataset/actions.rb:152:in `each'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.1.8/lib/logstash/plugin_mixins/jdbc/statement_handler.rb:41:in `perform_query'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.1.8/lib/logstash/plugin_mixins/jdbc/jdbc.rb:214:in `execute_statement'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.1.8/lib/logstash/inputs/jdbc.rb:335:in `execute_query'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.1.8/lib/logstash/inputs/jdbc.rb:298:in `block in run'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:234:in `do_call'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:258:in `do_trigger'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:300:in `block in start_work_thread'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:299:in `block in start_work_thread'
         org/jruby/RubyKernel.java:1442:in `loop'
         /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:289:in `block in start_work_thread'
     tz:
       ENV['TZ']:
       Time.now: 2022-04-05 11:13:40 +1000
     scheduler:
       object_id: 2052
       opts:
         {:max_work_threads=>1}
         frequency: 0.3
         scheduler_lock: #<Rufus::Scheduler::NullLock:0x5de2e9d2>
         trigger_lock: #<Rufus::Scheduler::NullLock:0x231d5b7>
       uptime: 371.44675 (6m11s447)
       down?: false
       threads: 2
         thread: #<Thread:0x11f7ab3f>
         thread_key: rufus_scheduler_2052
         work_threads: 1
           active: 1
           vacant: 0
           max_work_threads: 1
         mutexes: {}
       jobs: 1
         at_jobs: 0
         in_jobs: 0
         every_jobs: 0
         interval_jobs: 0
         cron_jobs: 1
       running_jobs: 1
       work_queue: 0
}  

```

Stackoverflow has a similar [question](https://stackoverflow.com/questions/47166350/logstash-tzinfoambiguoustime-exception-when-parsing-jdbc-column-logstash).  
And the [linked](https://github.com/logstash-plugins/logstash-input-jdbc/issues/121) issue looks uncomfortably familiar.

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [April 5, 2022, 1:47am UTC](https://discuss.elastic.co/t/data-stopped-coming-after-dst-change-logstash-jdbc-input/301562/4 "2022-04-05T01:47:40Z")

</div>

> [@pk.241011](#):
>
> got some errors but not sure if this is related.

Absolutely it is related to that.

---

<div class="post-metadata">

**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\
**Post date:** [April 5, 2022, 2:25am UTC](https://discuss.elastic.co/t/data-stopped-coming-after-dst-change-logstash-jdbc-input/301562/5 "2022-04-05T02:25:25Z")

</div>

Skipping the records between the ambiguous time period of 1AM to 3 AM kind of fixes the issue. But I think there has to be a better way.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 3, 2022, 2:26am UTC](https://discuss.elastic.co/t/data-stopped-coming-after-dst-change-logstash-jdbc-input/301562/6 "2022-05-03T02:26:12Z")

</div>

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