# Duplicate records in indexes **URL:** \ **Category:** Logstash\ **Created:** [August 8, 2019, 1:51pm UTC](https://discuss.elastic.co/t/duplicate-records-in-indexes/194443 "2019-08-08T13:51:31Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![SirJune](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sirjune/32/55205_2.png) [@SirJune](https://discuss.elastic.co/u/SirJune)\ **Post date:** [August 8, 2019, 1:51pm UTC](https://discuss.elastic.co/t/duplicate-records-in-indexes/194443/1 "2019-08-08T13:51:32Z")
HI, I have logstash running connecting/reading records from MySQL DB. I have 7 records in a timeframe (say: 01:00:00 - 01:01:00) I have this config file: ``` tracking_column => "datetime" use_column_value =>true statement => "SELECT hostname,datetime,count FROM callcountTable;" schedule => "*/20 * * * *" ``` output { elasticsearch { hosts =\> ["logstashserver01:9200"] index =\> "callcount-logstash-%{+YYYY.MM.dd}" document\_id =\> "%{platform}%{datetime}" } stdout { codec =\> rubydebug } } & indexpattern= "callcount\*" This morning, I found 14 records. 7 from yesterdays' index and 7 from today. 1. Is this an expected behavior that same set of records will be created on next days' index? 2. Is there some config i need such that today's index will only have records where datetime=today? possible solution/workaround: 1. I am thinking of removing the date "YYYY.MM.dd" in the index line and just have one index. So far I have 1.8 million records in the DB table. I'd appreciate feedbacks/tips. thanks, Ricardo ---
**Author:** ![Jenni](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jenni/32/29684_2.png) [@Jenni](https://discuss.elastic.co/u/Jenni)\ **Post date:** [August 8, 2019, 2:14pm UTC](https://discuss.elastic.co/t/duplicate-records-in-indexes/194443/2 "2019-08-08T14:14:59Z")
You set a tracking\_column, but didn't use sql\_last\_value. Therefore you keep processing the same entries every 20 minutes. And your index name is based on the current timestamp, not the datetime column. So Logstash imported the same data into the same index all day long yesterday, but the document\_id was always the same, so there were no duplicates. It just keeps doing the same today with da new index. That's why there are now duplicates. I don't think the import time matters to you, so you could use sql\_last\_value to only import the new data (where datetime \> :sql\_last\_value), then copy the datetime value into @timestamp because %{+YYYY.MM.dd} is based on the @timestamp column of the event. (If you want to keep @timestamp as the import time and not overwrite it, we could alternatively use Ruby with the strftime function to create the string for the index name based on datetime.) ---
**Author:** ![SirJune](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sirjune/32/55205_2.png) [@SirJune](https://discuss.elastic.co/u/SirJune)\ **Post date:** [August 14, 2019, 8:40pm UTC](https://discuss.elastic.co/t/duplicate-records-in-indexes/194443/3 "2019-08-14T20:40:22Z")
Thank you Jenni. So i now have this: ``` use_column_value =>true tracking_column_type => "timestamp" clean_run => true tracking_column => "datetime" last_run_metadata_path => "/var/log/test_logstash_jdbc_last_run" statement => "SELECT datetime,count FROM callcountTable where datetime >= '2019-08-14 00:00:00' order by datetime >:sql_last_value;" ``` it fetched 1980 records and it matches the info of the last row/record in the MySQL database. $ cat /var/log/test\_logstash\_jdbc\_last\_run --- 2019-08-14 16:13:38.000000000 -04:00 After X minutes, 4 new records are in the MySQL DB. I ran the Logstash query again and it added the 4 new records, and it updated the last\_run file. $ cat /var/log/test\_logstash\_jdbc\_last\_run --- 2019-08-14 16:18:43.000000000 -04:00 So far so good. Though i noticed in the stdout, it displayed all 1980++ records again. So the time it took for logstash query to finish is much more longer. I thought, Logstash will only fetch the "new records" so that execution time will be like fast. Is this an expected behaviour? myTable has 1.8 million rows if i remove the date range filter. ---
**Author:** ![Jenni](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jenni/32/29684_2.png) [@Jenni](https://discuss.elastic.co/u/Jenni)\ **Post date:** [August 18, 2019, 3:49pm UTC](https://discuss.elastic.co/t/duplicate-records-in-indexes/194443/4 "2019-08-18T15:49:48Z")
`statement => "SELECT datetime,count FROM callcountTable where datetime >= '2019-08-14 00:00:00' order by datetime >:sql_last_value;"` In this statement you sort the results by a boolean value (β€œIs datetime larger than the tracking value?” – Yes/No) instead of filtering them. So naturally there will be all the old entries again. Isn't this what you wanted to do? `statement => "SELECT datetime,count FROM callcountTable where datetime >= '2019-08-14 00:00:00' AND datetime >:sql_last_value ORDER BY datetime ASC;"` ---
**Author:** ![SirJune](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sirjune/32/55205_2.png) [@SirJune](https://discuss.elastic.co/u/SirJune)\ **Post date:** [August 20, 2019, 9:00pm UTC](https://discuss.elastic.co/t/duplicate-records-in-indexes/194443/5 "2019-08-20T21:00:06Z")
Thanks much Jenni! I think I'm getting the right count of records now. I will post an update after some more testing. BTW, ascencing (ASC) is giving a syntax error when run in logstash. Anyways, the default is ASC. clean\_run =\> true also messed up things on me. I disabled it which defaults to false. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 17, 2019, 9:00pm UTC](https://discuss.elastic.co/t/duplicate-records-in-indexes/194443/6 "2019-09-17T21:00:06Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.