# JDBC Update policy issue **URL:** \ **Category:** Elasticsearch\ **Created:** [March 29, 2013, 12:55pm UTC](https://discuss.elastic.co/t/jdbc-update-policy-issue/11378 "2013-03-29T12:55:33Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![ajitpals](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ajitpals/32/2105_2.png) [@ajitpals](https://discuss.elastic.co/u/ajitpals)\ **Post date:** [March 29, 2013, 12:55pm UTC](https://discuss.elastic.co/t/jdbc-update-policy-issue/11378/1 "2013-03-29T12:55:33Z")
Hi All, I'm trying ES + MySQL with JDBC plugin. I have created a small table in MySQL CREATE TABLE users ( > id INT NOT NULL AUTO\_INCREMENT PRIMARY KEY, > > name VARCHAR(100), > > department VARCHAR(100), > > created TIMESTAMP DEFAULT NOW() > > ); I made sample river with CURL curl -XPUT 'localhost:9200/samdoc/' -d '{}' curl -XPUT '[http://locakhost:9200/\_river/dbusers/\_meta](http://locakhost:9200/_river/dbusers/_meta)' -d '{ > ``` > "type" : "jdbc", > > "jdbc" : { > > ``` > > "strategy" : "simple", > > "poll" : "10m", > > ``` > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select * , id as _id from users" > > }, > > "index" : { > > "index" : "dbusers", > > "type" : "jdbc", > > ``` > > "versioning" : true > > ``` > } > > ``` > > }' ES index the SQL table and polls the data at every 2 min. Each polling request is making the version update in index. I have updated the database table and on next poll the ES index didn't reflect the changes. I also tried to refresh the index, but no updates were available in the ES. I have restarted the ES cluster for checking & noticed two things: 1: The ES index get created with new value and version is 1. 2: If any changes are made in the SQL during ES downtime time, entire table is index again and all previous version are deleted. How to reflect the updates in the table to the ES index? Did i miss something? Thanks Ajit -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [March 29, 2013, 1:14pm UTC](https://discuss.elastic.co/t/jdbc-update-policy-issue/11378/2 "2013-03-29T13:14:43Z")
Thanks for repoting. There is an issue in the update mechanism, I have to look into it. But it is correct that all values are indexed again and old documents get deleted, there is no other way to provide the "newest" data to ES. Therefore I will better document it and add a river strategy which can handle incremental updates (with more SQL statements). For bettertracing, I opened an issue for this > > > I'm trying ES + MySQL with JDBC plugin. I have created a small table in MySQL > > \`…\`\` > CREATE TABLE users ( > > id INT NOT NULL AUTO\_INCREMENT PRIMARY KEY, > > name VARCHAR(100), > > department VARCHAR(100), > > created TIMESTAMP DEFAULT NOW() > > ); > \`\`\` > > I made sample river with CURL > > \`\`\` > curl -XPUT 'localhost:9200/samdoc/' -d '{}' > > curl -XPUT 'http://locakhost:9200/\_river/dbusers/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "strategy" : "simple", > > "poll" : "10m", > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select \* , id as \_id from users" > > }, > > "index" : { > > "index" : "dbusers", > > "type" : "jdbc", > > "versioning" : true > > } > > }' > \`\`\` > > ES index the SQL table and polls the data at every 2 min. Each polling request is making the version update in index. I have updated the database table and on next poll the ES index didn't reflect the changes. I also tried to refresh the index, but no updates were available in the ES. > > I have restarted the ES cluster for checking & noticed two things: > 1: The ES index get created with new value and version is 1. > 2: If any changes are made in the SQL during ES downtime time, entire table is index again and all previous version are deleted. > > How to reflect the updates in the table to the ES index? Jörg Am 29.03.13 13:55, schrieb Ajitpal Singh: > Hi All, > > I'm trying ES + MySQL with JDBC plugin. I have created a small table > in MySQL > > ``` > CREATE TABLE users ( > > id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, > > name VARCHAR(100), > > department VARCHAR(100), > > created TIMESTAMP DEFAULT NOW() > > ); > > ``` > > I made sample river with CURL > > ``` > curl -XPUT 'localhost:9200/samdoc/' -d '{}' > > curl -XPUT 'http://locakhost:9200/_river/dbusers/_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "strategy" : "simple", > > "poll" : "10m", > > "driver" : "com.mysql.jdbc.Driver", > > "url" : "jdbc:mysql://localhost:3306/test", > > "user" : "", > > "password" : "", > > "sql" : "select * , id as _id from users" > > }, > > "index" : { > > "index" : "dbusers", > > "type" : "jdbc", > > "versioning" : true > > } > > }' > > ``` > > ES index the SQL table and polls the data at every 2 min. Each polling > request is making the version update in index. I have updated the > database table and on next poll the ES index didn't reflect the > changes. I also tried to refresh the index, but no updates were > available in the ES. > > I have restarted the ES cluster for checking & noticed two things: > 1: The ES index get created with new value and version is 1. > 2: If any changes are made in the SQL during ES downtime time, entire > table is index again and all previous version are deleted. > > How to reflect the updates in the table to the ES index? Did i miss > something? > > ## Thanks Ajit > > You received this message because you are subscribed to the Google > Groups "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send > an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:43am UTC](https://discuss.elastic.co/t/jdbc-update-policy-issue/11378/3 "2017-07-06T02:43:36Z")