# Logstash jdbc-input plugin recording sql last run time incorrectly

**URL:** <https://discuss.elastic.co/t/logstash-jdbc-input-plugin-recording-sql-last-run-time-incorrectly/146514>\
**Category:** Logstash\
**Created:** [August 29, 2018, 10:50am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-plugin-recording-sql-last-run-time-incorrectly/146514 "2018-08-29T10:50:04Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![RishikeshDarandale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rishikeshdarandale/32/34901_2.png) [@RishikeshDarandale](https://discuss.elastic.co/u/RishikeshDarandale)\
**Post date:** [August 29, 2018, 10:50am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-plugin-recording-sql-last-run-time-incorrectly/146514/1 "2018-08-29T10:50:04Z")

</div>

Here, I have MySQL server running in timezone `America/Chicago` (CDT/CST). I have verified this with following sql query

```
SELECT @@global.time_zone, @@session.time_zone;

```

My simple table looks like below:

SELECT \* FROM brand ORDER BY updated\_ts;

 ![Screenshot%20from%202018-08-29%2016-07-01](https://us1.discourse-cdn.com/elastic/original/3X/f/b/fb500d5676fd429f65c4ffe1b39c8c94551fa0e0.png)

I have a logstash docker image 6.4.0 ( **Container is running in UTC** ) with following configuration details:

```
    ###
    # Configuration file for brand ingestion
    #
    # Directory structure of logstash for docker is mentioned here.
    # https://www.elastic.co/guide/en/logstash/current/dir-layout.html#docker-layout
    ###
    input {
      jdbc {
       #	JDBC driver location
    jdbc_driver_library => "/usr/share/logstash/bin/mysql-connector-java-8.0.12.jar"
    #	JDBC driver - updated one- use this
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    #	DB connection string
    # Note: If logstash running system timezone is different than MySQL server timezone,
    # then please update it as per MySQL server timezone
    # SELECT @@global.time_zone, @@session.time_zone;
    jdbc_connection_string => "jdbc:mysql://SERVER:3306/SCHEMA?serverTimezone=America/Chicago&useSSL=false"
    jdbc_user => "USER"
    jdbc_password => "XXXX"
    #	If true, then will not persist previous execution state. Setting false as we will have updates also
    clean_run => false
    # Set the timezone
    # Verify the timezone of the MySQL server and update here if necessary. This will be crucial in case of
    # recording the last run time.
    jdbc_default_timezone => "America/Chicago"
    # schedule
    # TODO: update the schedule, currently every five minutes for testing
    schedule => "5 * * * * *"
    #	SQL
    # Note: Do not end the sql with separator(;)
    # Specify the ORDER BY "tracking_column", if record_last_run is true to record the correct time
    statement => "SELECT
                    b.brand_id AS 'brand_id',
                    b.brand_name AS 'brand_name',
                    b.brand_logo AS 'brand_logo',
                    b.updated_ts as 'updated_ts'
                  FROM
                    brand b
                  WHERE b.updated_ts > :sql_last_value
                  ORDER BY updated_ts, b.brand_id ASC"
    # Specify the fetch size, if not provided jdbc driver default is used
    jdbc_fetch_size => 1000
    #	Need to set to use the specified column as an identifier of the persistent state
    use_column_value => true
    #	Name of the column to track previous exexution or persistence
    #	Need to take the most updated one
    tracking_column => "updated_ts"
    #	Type of the tracking column - very important!
    tracking_column_type => "timestamp"
    #	It will record or store each running state in 'last_run_metadata_path'
    record_last_run => true
    #	The place to keep the tracks, lets use data directory
    last_run_metadata_path => "/usr/share/logstash/data/logstash_jdbc_last_run_ingest_brand_01.txt"
  }
}

filter {
  # Trim leading and trailing blank spaces
  mutate {
    strip => ["brand_name"]
  }
}

output {
  stdout {
    codec => rubydebug {
      metadata => true
    }
  }
}

```

First time the query gets fired correctly and fetch required records as below

```
   SELECT
      b.brand_id AS 'brand_id',
      b.brand_name AS 'brand_name',
      b.brand_logo AS 'brand_logo',
      b.updated_ts as 'updated_ts'
  FROM
     brand b
  WHERE b.updated_ts > '1969-12-31 18:00:00'
  ORDER BY updated_ts, b.brand_id ASC

```

Now, the last run time gets recorded as

```
--- !ruby/object:DateTime '2018-08-29 10:16:16.000000000 -05:00'

```

Looks like this is incorrect, this should be '2018-08-29 5:16:16.000000000 -05:00'

NOTE: here I will be loosing records that will get updated during '2018-08-29 5:16:16.000000000 CDT' to '2018-08-29 10:16:16.000000000 CDT'

Next time queries are getting executed as below:

```
   SELECT
      b.brand_id AS 'brand_id',
      b.brand_name AS 'brand_name',
      b.brand_logo AS 'brand_logo',
      b.updated_ts as 'updated_ts'
  FROM
     brand b
  WHERE b.updated_ts > '2018-08-29 10:16:16'

```

Can you please suggest what I doing incorrect here?

---

<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:** [September 26, 2018, 10:50am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-plugin-recording-sql-last-run-time-incorrectly/146514/2 "2018-09-26T10:50:15Z")

</div>

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