# Jdbc and consistence of data

**URL:** <https://discuss.elastic.co/t/jdbc-and-consistence-of-data/147756>\
**Category:** Logstash\
**Created:** [September 7, 2018, 6:38pm UTC](https://discuss.elastic.co/t/jdbc-and-consistence-of-data/147756 "2018-09-07T18:38:45Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [September 7, 2018, 6:38pm UTC](https://discuss.elastic.co/t/jdbc-and-consistence-of-data/147756/1 "2018-09-07T18:38:45Z")

</div>

I am doing simple test.  
connected to oracle db using jdbc and pulled two field name,date total 3000 record got pulled in to logstash. which are same when I check on oracle db.  
ran that automatically few time as my config says to run it every 10 min and record stayed same (3000)

Now I deleted one record from that table on oracle. but logstash didn't remove that one record from it's log. how do I achieve this? that when information get change in oracle db it should pull that information at next round of run (which is every 10min)

But if I restart logstash.service new number reflects on my dashboard and get (2999)

here is my config file

input {  
jdbc {  
jdbc\_validate\_connection =\> true  
jdbc\_connection\_string =\> "jdbc:oracle:thin:@oradev01:1521/CLIENT"  
jdbc\_user =\> "user"  
jdbc\_password =\> "user"  
jdbc\_driver\_library =\> "/usr/lib/oracle/12.2/client64/lib/ojdbc8.jar"  
jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver"  
statement =\> "SELECT name,entered from CLIENTS where entered \> :sql\_last\_value"

```
    last_run_metadata_path => "/tmp/logstash-Clients.lastrun"
    use_column_value=>true
    tracking_column=>"entered"
    tracking_column_type=>"timestamp"
    clean_run=>true
    record_last_run => true
    schedule => "*/10 * * * *"
   }

```

}

output {  
elasticsearch {  
index =\> "clients-name" }  
}

---

<div class="post-metadata">

**Author:** ![yaauie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yaauie/32/23363_2.png) [@yaauie](https://discuss.elastic.co/u/yaauie)\
**Post date:** [September 7, 2018, 10:40pm UTC](https://discuss.elastic.co/t/jdbc-and-consistence-of-data/147756/2 "2018-09-07T22:40:46Z")

</div>

What are you attempting to accomplish? The JDBC input is working as configured:

- The first time you run it, 3000 rows are emitted from the database, creating 3000 events in Logstash that are persisted in Elasticsearch as 3000 docs. The highest value seen for your `tracking_column` is saved to a file and is used in future requests as `:sql_last_value`.
- The second time the schedule runs the job, the JDBC input uses your configured `tracking_column` and `statement` to only select data whose `entered > :sql_last_value`, which causes the DB to emit zero rows.
- After you have deleted a row, the scheduler runs again, using your configured `tracking_column` to only select data whose `entered > :sql_last_value`, which causes the DB to emit zero rows.
- When you restart Logstash, the input's `clean_run => true` directive causes it to _ignore_ the cached `tracking_column` value, starting over from the beginning. Since there are 2999 rows in the database at this point, all 2999 are emitted.

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [September 10, 2018, 2:07pm UTC](https://discuss.elastic.co/t/jdbc-and-consistence-of-data/147756/3 "2018-09-10T14:07:03Z")

</div>

oh so if I do not have clean\_run=\>true it will not start over and and I will still have 3000 rows. but infact I need 2999 as new row count because a row has been deleted. how do I achieve that ?

basically I want logstash to reflect what is in database. that is if something is deleted from db then delete from it's record. if something is added in to db then add that in to it's count.

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [September 10, 2018, 2:12pm UTC](https://discuss.elastic.co/t/jdbc-and-consistence-of-data/147756/4 "2018-09-10T14:12:57Z")

</div>

Logstash has no way of tracking deleted rows.

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [September 10, 2018, 4:19pm UTC](https://discuss.elastic.co/t/jdbc-and-consistence-of-data/147756/5 "2018-09-10T16:19:46Z")

</div>

I tested clean\_run=\>false and it is what I needed at this time. Thank you

---

<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:** [October 8, 2018, 4:19pm UTC](https://discuss.elastic.co/t/jdbc-and-consistence-of-data/147756/6 "2018-10-08T16:19:52Z")

</div>

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