# Logstash is not writing latest value in jdbc Sincedb

**URL:** <https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306>\
**Category:** Logstash\
**Created:** [June 18, 2019, 3:52pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306 "2019-06-18T15:52:27Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tabish0009](https://avatars.discourse-cdn.com/v4/letter/t/34f0e0/32.png) [@Tabish0009](https://discuss.elastic.co/u/Tabish0009)\
**Post date:** [June 18, 2019, 3:52pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/1 "2019-06-18T15:52:27Z")

</div>

I am using 5.x version of Logstash and 7.x version of Elasticsearch and Kibana, the issue I am facing is even after setting the path for the sincedb, logstash is not writing the timestamp till the point reading has been complete. Every time it Starts reading the whole data.

I am using the mentioned below configuration.

```
input{
    jdbc{
        jdbc_driver_library => "/usr/share/logstash/bin/mysql-connector-java-8.0.16.jar"
        jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://10.32.5.51:3307/sakila?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC"
        jdbc_user => "root"
        jdbc_password => "root"
        statement => "SELECT * FROM sakila.city WHERE updatedon > :sql_last_value"
        type =>"dailysummary"
        # schedule => "*/5 * * * *"
        use_column_value => true
        tracking_column => updatedon
        record_last_run => true
        last_run_metadata_path => "/usr/share/logstash/bin/since"
    }
}
filter{
    
}

output {
    # stdout {
    # codec=>rubydebug
    # }
	elasticsearch {
        hosts=> "localhost:9200"
        index=> "dailysummary-%{+YYYY-MM-dd}"  
    }
}
```

---

<div class="post-metadata">

**Author:** ![EZprogramming](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ezprogramming/32/57291_2.png) [@EZprogramming](https://discuss.elastic.co/u/EZprogramming)\
**Post date:** [July 8, 2019, 4:19pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/2 "2019-07-08T16:19:23Z")

</div>

Have this in your filter section to get timestamp:

```
  date {
    match => ["log-datestamp", "YYYY-MM-dd HH:mm:ss,SSS"]
    target => "@timestamp"
    timezone => "UTC"
  }       
  date {
    match => ["log-datestamp", "YY-MM-dd HH:mm:ss,SSS"]
    target => "@timestamp"
    timezone => "UTC"
  }    
  date {
    match => ["log-datestamp", "ISO8601"]
    target => "@timestamp"
    timezone => "UTC"
  }    
  date {
    match => ["log-epoch", "UNIX"]  
    target => "@timestamp"
    timezone => "UTC"
  }    
  date {
    match => ["log-epoch", "UNIX_MS"]
    target => "@timestamp"
    timezone => "UTC"
  }
```

---

<div class="post-metadata">

**Author:** ![bayardk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bayardk/32/49705_2.png) [@bayardk](https://discuss.elastic.co/u/bayardk)\
**Post date:** [July 8, 2019, 5:35pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/3 "2019-07-08T17:35:45Z")

</div>

why duplicate clauses (1st 2)?

---

<div class="post-metadata">

**Author:** ![EZprogramming](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ezprogramming/32/57291_2.png) [@EZprogramming](https://discuss.elastic.co/u/EZprogramming)\
**Post date:** [July 8, 2019, 5:37pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/4 "2019-07-08T17:37:20Z")

</div>

It is not duplicate, one starts with YYYY and the other starts with YY.

---

<div class="post-metadata">

**Author:** ![bayardk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bayardk/32/49705_2.png) [@bayardk](https://discuss.elastic.co/u/bayardk)\
**Post date:** [July 8, 2019, 5:39pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/5 "2019-07-08T17:39:48Z")

</div>

Oops, silly human

---

<div class="post-metadata">

**Author:** ![bayardk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bayardk/32/49705_2.png) [@bayardk](https://discuss.elastic.co/u/bayardk)\
**Post date:** [July 9, 2019, 1:33pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/6 "2019-07-09T13:33:25Z")

</div>

I'm here on this thread because my 7.0.1 Logstash/jdbc/since tasks are not working. They're creating duplicate Elasticsearch documents from a single SQL row, even after incorporating the date filters suggested above. Logstash keeps pulling records it already pulled; Kibana's Discovery pane shows the same row arriving repeatedly, but it only exists once in the DB.

My conf:

input {  
jdbc {  
jdbc\_driver\_library =\> "/usr/share/java/mysql-connector-java.jar"  
jdbc\_driver\_class =\> "com.mysql.jdbc.Driver"  
jdbc\_connection\_string =\> "jdbc:mysql://192.168.40:3306/firefly"  
jdbc\_user =\> "\*\*\*"  
jdbc\_password =\> "\*\*_"  
statement =\> "SELECT \* FROM tst\_log WHERE timestamp \>= :sql\_last\_value"  
use\_column\_value =\> true  
tracking\_column =\> "timestamp"  
tracking\_column\_type =\> "timestamp"  
schedule =\> "_ \* \* \* \*"  
last\_run\_metadata\_path =\> "/usr/share/logstash/.log\_logstash\_jdbc\_last\_run"  
}  
}

filter {  
date {  
match =\> ["log-datestamp", "YYYY-MM-dd HH:mm:ss,SSS"]  
target =\> "@timestamp"  
timezone =\> "UTC"  
}  
\<snipped to meet limits, but matches EZProgramming's suggestion\>

---

<div class="post-metadata">

**Author:** ![bayardk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bayardk/32/49705_2.png) [@bayardk](https://discuss.elastic.co/u/bayardk)\
**Post date:** [July 9, 2019, 1:41pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/7 "2019-07-09T13:41:55Z")

</div>

More info. The journal shows repeated instances of this line:

Jul 09 09:34:04 amos.nexus-tech.local logstash[27024]: [2019-07-09T09:34:04,552][INFO][logstash.inputs.jdbc] (4.378876s) SELECT \* FROM vw\_ts\_sno\_events WHERE stamp \>= '2019-07-09 08:54:02'

Only 1 record was created in the SQL database since 00:00:00 July 9, 2019, at precisely 8:54:02; but Kibana shows 39 records received on July 9 in the Discovery panel. Since starting this jdbc job 24 hours ago, it has inflated 16,514 SQL rows into 17,702 Elastic documents.

---

<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:** [July 9, 2019, 1:54pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/8 "2019-07-09T13:54:20Z")

</div>

> [@bayardk](#):
>
> WHERE stamp \>= '2019-07-09 08:54:02

Using \>= means it will repeatedly fetch the most recent row.

---

<div class="post-metadata">

**Author:** ![bayardk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bayardk/32/49705_2.png) [@bayardk](https://discuss.elastic.co/u/bayardk)\
**Post date:** [July 9, 2019, 2:06pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/9 "2019-07-09T14:06:56Z")

</div>

Thanks! I will change immediately, restart, and see what happens over a couple of hours.

---

<div class="post-metadata">

**Author:** ![bayardk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bayardk/32/49705_2.png) [@bayardk](https://discuss.elastic.co/u/bayardk)\
**Post date:** [July 9, 2019, 2:23pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/10 "2019-07-09T14:23:13Z")

</div>

I can tell it's fixed already. The Kibana count for today went from 40+ to 1. Thanks for catching my mistake.

---

<div class="post-metadata">

**Author:** ![Tabish0009](https://avatars.discourse-cdn.com/v4/letter/t/34f0e0/32.png) [@Tabish0009](https://discuss.elastic.co/u/Tabish0009)\
**Post date:** [July 23, 2019, 2:58pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/11 "2019-07-23T14:58:12Z")

</div>

@Badger This also made my life easy and resolved my issue as well. Filter part ha made the things align, Further the repetitions are also removed. THANKS EVERYONE !!!

@bayardk I was also facing the issue of repetitions but now I am getting totally accurate data.

---

<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:** [August 20, 2019, 2:58pm UTC](https://discuss.elastic.co/t/logstash-is-not-writing-latest-value-in-jdbc-sincedb/186306/12 "2019-08-20T14:58:24Z")

</div>

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