# Duplicate Data problem **URL:** \ **Category:** Elasticsearch\ **Created:** [June 15, 2016, 8:32am UTC](https://discuss.elastic.co/t/duplicate-data-problem/52838 "2016-06-15T08:32:11Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Rajkumar\_E](https://avatars.discourse-cdn.com/v4/letter/r/9de053/32.png) [@Rajkumar\_E](https://discuss.elastic.co/u/Rajkumar_E)\ **Post date:** [June 15, 2016, 8:32am UTC](https://discuss.elastic.co/t/duplicate-data-problem/52838/1 "2016-06-15T08:32:11Z")
I have problem to push mysql data to elasticsearch using mysql replicator. I have two table for chat\_\_user and chat\_message. chat\_user: ``` id name socketid 1 raj 123 2 kumar 1234 ``` chat\_message: ``` id chat_from chat_to message 1 123 1235 hello 2 1235 123 how can i help you? 3 123 1235 HOw track my order? ``` then, John this two tables Using "chat\_message.chat\_from = chat\_user.socketid OR chat\_message.chat\_to = chat\_user.socketid" Myquery: ``` SELECT * FROM `chat_message` INNER JOIN `chat_user` ON chat_message.chat_from = chat_user.socketid OR chat_message.chat_to = chat_user.socketid ``` Result: ``` chat_from chat_to message id name socketid 123 1235 hello 1 raj 123 1235 123 how can i help you? 1 raj 123 123 1235 HOw track my order? 1 raj 123 ``` If I push this data to elasticsearch, only push last row data. ``` 123 1235 HOw track my order? 1 raj 123 ``` Because Duplication occur in primary key I set primary key chat\_user id is a primary key in td-agent configuration file. Td-Agent Configuration File: ``` #### ## Output descriptions: ## # HTTP input # POST http://localhost:8888/?json= # POST http://localhost:8888/td.myapp.login?json={"user"%3A"me"} # @see http://docs.fluentd.org/articles/in_http @type http port 8888 ## live debugging agent @type debug_agent bind localhost port 24230 #### ## Examples: ## @type mysql_replicator host localhost username root password gworks.mobi2 database livechat query SELECT * FROM `chat_message` INNER JOIN `chat_user` ON chat_message.chat_from = chat_user.socketid OR chat_message.chat_to = chat_user.socketid; primary_key id interval 10s enable_delete yes tag replicator.history5.histestb.${event}.${primary_key} @type stdout @type mysql_replicator_elasticsearch host localhost port 9200 tag_format (?[^\.]+)\.(?[^\.]+)\.(?[^\.]+)\.(?[^\.]+)$ flush_interval 5s max_retry_wait 1800 flush_at_shutdown yes buffer_type file buffer_path /var/log/td-agent/buffer/mysql_replicator_elasticsearch.* ``` Reference : [https://github.com/elastic/elasticsearch/issues/18882](https://github.com/elastic/elasticsearch/issues/18882) I need to push all data to elasticsearch, Suggest me How to solve this Problem? . ---
**Author:** ![ywelsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ywelsch/32/7751_2.png) [@ywelsch](https://discuss.elastic.co/u/ywelsch)\ **Post date:** [June 15, 2016, 9:19am UTC](https://discuss.elastic.co/t/duplicate-data-problem/52838/2 "2016-06-15T09:19:07Z")
> [@Rajkumar\_E](#): > > mysql\_replicator\_elasticsearch As I'm not familiar with the plugin, I don't really know how to help you here. I see, however, that you opened an issue as well for the plugin that does the replication. > > > I working EFK. I push mysql data to elasticsearch using mysql-replicator but, do…esn't move alla data. > > if select that query to run my mysql database it working fine, same query to put td-agent configuration it not push all the data. > > This is td-agent configuration file: > > \`\`\` > #### > ## Output descriptions: > ## > > #### > ## Source descriptions: > ## > > ## built-in TCP input > ## @see http://docs.fluentd.org/articles/in\_forward > \ > @type forward > \ > > ## built-in UNIX socket input > #\ > # type unix > #\ > > # HTTP input > # POST http://localhost:8888/\?json=\ > # POST http://localhost:8888/td.myapp.login?json={"user"%3A"me"} > # @see http://docs.fluentd.org/articles/in\_http > \ > @type http > port 8888 > \ > > ## live debugging agent > \ > @type debug\_agent > bind localhost > port 24230 > \ > > \ > @type mysql\_replicator > host localhost > username root > password xxxxxx > database livechat > query SELECT \* FROM \`chat\_master\` a, \`chat\_history\` b WHERE a.socketid=b.chat\_from OR a.socketid=b.chat\_to; > primary\_key id > interval 10s > enable\_delete yes > tag replicator.test.chat.${event}.${primary\_key} > \ > #\ > # @type stdout > #\ > > \ > @type mysql\_replicator\_elasticsearch > host localhost > port 9200 > tag\_format (?\\[^\\.\]+)\\.(?\\[^\\.\]+)\\.(?\\[^\\.\]+)\\.(?\\[^\\.\]+)$ > flush\_interval 5s > max\_retry\_wait 1800 > flush\_at\_shutdown yes > buffer\_type file > buffer\_path /var/log/td-agent/buffer/mysql\_replicator\_elasticsearch.\* > \ > \`\`\` > > Suggest me How to resolve this Problem? Someone seems to be helping you there, so I would have preferred that you properly link that here instead of just cross-posting to various platforms. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [June 15, 2016, 9:27am UTC](https://discuss.elastic.co/t/duplicate-data-problem/52838/3 "2016-06-15T09:27:23Z")
In the result set you seem to have id set to 1 for all records. It is therefore not a suitable primary key. As you use this as document ID in Elasticsearch, you are updating the same document over and over. You need to correct your query so that the id field is unique, e.g. by making sure it corresponds to the id from the chat\_message table, assuming this is unique. ---
**Author:** ![Rajkumar\_E](https://avatars.discourse-cdn.com/v4/letter/r/9de053/32.png) [@Rajkumar\_E](https://discuss.elastic.co/u/Rajkumar_E)\ **Post date:** [June 15, 2016, 12:05pm UTC](https://discuss.elastic.co/t/duplicate-data-problem/52838/4 "2016-06-15T12:05:49Z")
I have solved my problem , for removing chat\_user **id** field. run join query: ``` SELECT * FROM `chat_message` INNER JOIN `chat_user` ON chat_message.chat_from = chat_user.socketid OR chat_message.chat_to = chat_user.socketid ``` now i got no duplication result. ``` id chat_from chat_to message name socketid 1 123 1235 hello raj 123 2 1235 123 how can i help you? raj 123 3 123 1235 How track my order? raj 123 ``` so, all Record are pushed to elasticsearch. its worked for me. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 10:43pm UTC](https://discuss.elastic.co/t/duplicate-data-problem/52838/5 "2017-07-05T22:43:39Z")