# Determining the cause of data loss

**URL:** <https://discuss.elastic.co/t/determining-the-cause-of-data-loss/113554>\
**Category:** Logstash\
**Created:** [December 29, 2017, 9:19am UTC](https://discuss.elastic.co/t/determining-the-cause-of-data-loss/113554 "2017-12-29T09:19:34Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![oguz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/oguz/32/41862_2.png) [@oguz](https://discuss.elastic.co/u/oguz)\
**Post date:** [December 29, 2017, 9:19am UTC](https://discuss.elastic.co/t/determining-the-cause-of-data-loss/113554/1 "2017-12-29T09:19:34Z")

</div>

Hi All,

i'm using jdbc plugin to transfer data from postgres to es. My sample data is 6028 records on postgres. I get different number of records transferred to es each time i try. Sometimes all 6028 records gets transffered to elasticsearch and sometimes fewer.  
There is no error on elasticsearch or logstash logs.  
My logstash config is as below:

```
input {
        jdbc {
                jdbc_connection_string => "jdbc:postgresql://192.168.0.6:5432/dbname"
                jdbc_user => "user"
                jdbc_password => "pass"
                jdbc_driver_library => "/home/postgresql-42.0.0.jar"
                jdbc_driver_class => "org.postgresql.Driver"
                statement => "SELECT * FROM booking_search"
        }
}

filter {
        json {
                source => "booking"
        }
        json {
                source => "join_field"
        }
}

output {
        stdout { codec => json_lines }
        elasticsearch {
                "hosts" => "localhost:9200"
                "index" => "bookings"
                "routing" => "%{booking_id}"
                "document_id" => "%{booking_id}"
        }
}

```

My booking\_ids are unique, as i wrote above sometimes all data is transferred without a problem.

What can i do to find the cause of data loss?

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [December 29, 2017, 9:22am UTC](https://discuss.elastic.co/t/determining-the-cause-of-data-loss/113554/2 "2017-12-29T09:22:41Z")

</div>

> [@oguz](#):
>
> "routing" =\> "%{booking\_id}"

That's kinda pointless with that many records.

> [@oguz](#):
>
> Sometimes all 6028 records gets transffered to elasticsearch and sometimes fewer.

Are you sure it is not [Jdbc input plugin | Logstash Reference [8.11] | Elastic](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_state)?

---

<div class="post-metadata">

**Author:** ![oguz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/oguz/32/41862_2.png) [@oguz](https://discuss.elastic.co/u/oguz)\
**Post date:** [December 29, 2017, 9:56am UTC](https://discuss.elastic.co/t/determining-the-cause-of-data-loss/113554/3 "2017-12-29T09:56:34Z")

</div>

I looked up state part now, i am not using sql\_last\_value parameter. I also tried with clean\_run =\> true just to be sure and still different number of records in each try.

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [December 29, 2017, 9:58am UTC](https://discuss.elastic.co/t/determining-the-cause-of-data-loss/113554/4 "2017-12-29T09:58:02Z")

</div>

How are you calculating the number of records in the table versus what was processed versus what is in Elasticsearch?

---

<div class="post-metadata">

**Author:** ![oguz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/oguz/32/41862_2.png) [@oguz](https://discuss.elastic.co/u/oguz)\
**Post date:** [December 29, 2017, 11:15am UTC](https://discuss.elastic.co/t/determining-the-cause-of-data-loss/113554/5 "2017-12-29T11:15:37Z")

</div>

For logstash, i don't know how to check the processed records, for db and elasticsearch i'm running a count query.

```
curl -XGET localhost:9200/bookings/_count?pretty -H 'Content-Type: application/json' -d ' 
{
"query" : {
"match_all" : { }
}
}'

{
  "count" : 5833,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  }
}
```

---

<div class="post-metadata">

**Author:** ![oguz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/oguz/32/41862_2.png) [@oguz](https://discuss.elastic.co/u/oguz)\
**Post date:** [December 29, 2017, 12:31pm UTC](https://discuss.elastic.co/t/determining-the-cause-of-data-loss/113554/6 "2017-12-29T12:31:59Z")

</div>

I added a file output:

```
 file {
                path => "/var/log/logstash/logstash-%{+YYYY-MM-dd}.log"
                codec => json_lines
        }

```

Then found the booking ids:

`curl -XGET "localhost:9200/bookings/_search?pretty&from=0&size=10000" | grep '"_id"' | cut -d '"' -f 1,4 --output-delimiter=',' > booking-ids.txt`

Then found the difference on postgres using these ids.

When i check with an id from txt file, i can find it in elasticsearch. And when i use one of the missing ids (postgres diff) i cannot find it in elasticsearch.

Returning back to file output, when i look for a missing id inside the logstash output file, i cannot find it there. So it seems that logstash didn't try to send it anyway. Any idea why that might happen?

---

<div class="post-metadata">

**Author:** ![oguz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/oguz/32/41862_2.png) [@oguz](https://discuss.elastic.co/u/oguz)\
**Post date:** [January 4, 2018, 10:07am UTC](https://discuss.elastic.co/t/determining-the-cause-of-data-loss/113554/7 "2018-01-04T10:07:44Z")

</div>

If anyone is interested, i gave up on using logstash and went for bulk api instead. Now i can tranfer my data without any loss. There are other unsolved topics with the same problem. So this might really be a bug...

---

<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:** [February 1, 2018, 10:07am UTC](https://discuss.elastic.co/t/determining-the-cause-of-data-loss/113554/8 "2018-02-01T10:07:51Z")

</div>

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