# \[JDBC River\] - Unexpected behaviour with versioning **URL:** \ **Category:** Elasticsearch\ **Created:** [February 25, 2013, 10:47am UTC](https://discuss.elastic.co/t/jdbc-river-unexpected-behaviour-with-versioning/10878 "2013-02-25T10:47:42Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Enrique\_Medina\_Monte](https://avatars.discourse-cdn.com/v4/letter/e/4491bb/32.png) [@Enrique\_Medina\_Monte](https://discuss.elastic.co/u/Enrique_Medina_Monte)\ **Post date:** [February 25, 2013, 10:47am UTC](https://discuss.elastic.co/t/jdbc-river-unexpected-behaviour-with-versioning/10878/1 "2013-02-25T10:47:42Z")
Hi, I started to use the JDBC River plugin, but I'm not getting the expected results. Let me explain. I'm trying to achieve a very basic use case, where I have a simple 3 columns table in a MySQL DB, and I want the JDBC River to index them, an be aware of any changes periodically using a poll. Based on the docs, I can create such a JDBC River using: > Once created, I do get the OK confirmation JSON from ES, so in the logs I can see the River is working. The very first time it runs, I can get all the results properly (I'm only using a few records for the sake of simplicity): > However, if I go to the DB, modify a record, and then get back to ES, I don't see that modification being detected whatsoever in the indexed data. After some research, I found that maybe the value changed detection is not yet available in the plugin, so I tried out again by modifying the id itself in the DB without success either. Therefore, what am I doing wrong here? As far as I can read in the plugin docs, this should be a pretty straight-forward scenario, but I just cannot make it work. Any help would be appreciated. Thanks! -- 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:** [February 26, 2013, 12:03am UTC](https://discuss.elastic.co/t/jdbc-river-unexpected-behaviour-with-versioning/10878/2 "2013-02-26T00:03:10Z")
Hi, the change detection is there, if you add a JDBC river, look at this document curl localhost:9200/\_river/my\_jdbc\_river/\_custom/ {"\_index":"\_river","\_type":"my\_jdbc\_river","\_id":"\_custom","\_version":1,"exists":true, "\_source" : {"jdbc":{"created":"2013-02-25T23:51:25.147Z","version":1,"digest":"zLHqWpENr/kn3MPk4Xww00J2zuNaOwdVnoNaIuHW7qI="}}} In the "digest" field, you see a base64 value that is computed against all the rows that have been fetched in the last round. Next round, a new digest is computed and compared against the previous digest to detect a change. Note, default strategy is "oneshot", which only run the river once to make the river usage very easy for demonstration. To activate the rounds, you must change the "strategy" parameter to "simple", and set the "poll" interval (default is "1h"). It would help if you can copy your statements here so I can understand better what you want to achieve. Best regards, Jörg Am 25.02.13 11:47, schrieb Enrique Medina Montenegro: > After some research, I found that maybe the value changed detection is > not yet available in the plugin, so I tried out again by modifying the > id itself in the DB without success either. > > Therefore, what am I doing wrong here? As far as I can read in the > plugin docs, this should be a pretty straight-forward scenario, but I > just cannot make it work. -- 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:** ![lyndon\_camson](https://avatars.discourse-cdn.com/v4/letter/l/85e7bf/32.png) [@lyndon\_camson](https://discuss.elastic.co/u/lyndon_camson)\ **Post date:** [March 20, 2013, 3:22am UTC](https://discuss.elastic.co/t/jdbc-river-unexpected-behaviour-with-versioning/10878/3 "2013-03-20T03:22:09Z")
Hi, I have the same issue and was wondering how this was resolved. I have this users table with id, fname, lname and update\_date. I am using this statement "SELECT \*, id AS \_id, CONCAT(fname, ' ', lname) AS member\_name FROM users WHERE (create\_date BETWEEN DATE\_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()) OR (update\_date BETWEEN DATE\_SUB(NOW(), INTERVAL 1 HOUR) AND NOW())" and using strategy =\> simple, poll =\> 5s when I update a row in the db, say the fname. fname before was "George" then I update it to "Will", it's not updated. Am I missing something here? I have already set an \_id field. On Tuesday, February 26, 2013 8:03:10 AM UTC+8, Jörg Prante wrote: > Hi, > > the change detection is there, if you add a JDBC river, look at this > document > > curl localhost:9200/\_river/my\_jdbc\_river/\_custom/ > {"\_index":"\_river","\_type":"my\_jdbc\_river","\_id":"\_custom","\_version":1,"exists":true, > > "\_source" : > {"jdbc":{"created":"2013-02-25T23:51:25.147Z","version":1,"digest":"zLHqWpENr/kn3MPk4Xww00J2zuNaOwdVnoNaIuHW7qI="}}} > > In the "digest" field, you see a base64 value that is computed against > all the rows that have been fetched in the last round. Next round, a new > digest is computed and compared against the previous digest to detect a > change. > > Note, default strategy is "oneshot", which only run the river once to > make the river usage very easy for demonstration. > > To activate the rounds, you must change the "strategy" parameter to > "simple", and set the "poll" interval (default is "1h"). > > It would help if you can copy your statements here so I can understand > better what you want to achieve. > > Best regards, > > J?rg > > Am 25.02.13 11:47, schrieb Enrique Medina Montenegro: > > > After some research, I found that maybe the value changed detection is > > not yet available in the plugin, so I tried out again by modifying the > > id itself in the DB without success either. > > > > Therefore, what am I doing wrong here? As far as I can read in the > > plugin docs, this should be a pretty straight-forward scenario, but I > > just cannot make it work. -- 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:** ![lyndon\_camson](https://avatars.discourse-cdn.com/v4/letter/l/85e7bf/32.png) [@lyndon\_camson](https://discuss.elastic.co/u/lyndon_camson)\ **Post date:** [March 20, 2013, 4:02am UTC](https://discuss.elastic.co/t/jdbc-river-unexpected-behaviour-with-versioning/10878/4 "2013-03-20T04:02:28Z")
so it seems that digest is not changing even if there are changes made in the database. i'm using v2.0.3 is this a bug? -- 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 20, 2013, 11:02am UTC](https://discuss.elastic.co/t/jdbc-river-unexpected-behaviour-with-versioning/10878/5 "2013-03-20T11:02:52Z")
Thanks for reporting, I will investigate. Jörg Am 20.03.13 05:02, schrieb [lyndon.camson@upraxis.com](mailto:lyndon.camson@upraxis.com): > so it seems that digest is not changing even if there are changes made > in the database. i'm using v2.0.3 > > ## is this a bug? > > 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:** ![lyndon\_camson](https://avatars.discourse-cdn.com/v4/letter/l/85e7bf/32.png) [@lyndon\_camson](https://discuss.elastic.co/u/lyndon_camson)\ **Post date:** [March 21, 2013, 5:07am UTC](https://discuss.elastic.co/t/jdbc-river-unexpected-behaviour-with-versioning/10878/6 "2013-03-21T05:07:27Z")
Thanks. One question, on your wiki > **[Create new page · jprante/elasticsearch-jdbc Wiki](https://github.com/jprante/elasticsearch-jdbc/wiki/Updates-with-versioning)** > > JDBC importer for Elasticsearch. Contribute to jprante/elasticsearch-jdbc development by creating an account on GitHub. "If a document ID is no longer provided by the database between river runs, > Elasticsearch will not index. As a result, the version of the document in > the index gets lower than the actual version managed by the river run, and > a housekeeping procedure is required to remove such obsolete documents." My sql statement will return only subsets of my whole table. if the other rows are not updated, then does that mean that your plugin will delete these id's? Thanks again. On Wednesday, March 20, 2013 7:02:52 PM UTC+8, Jörg Prante wrote: > Thanks for reporting, I will investigate. > > J?rg > > Am 20.03.13 05:02, schrieb [lyndon...@upraxis.com](mailto:lyndon...@upraxis.com) \: > > > so it seems that digest is not changing even if there are changes made > > in the database. i'm using v2.0.3 > > > > ## is this a bug? > > > > 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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@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:** ![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 21, 2013, 4:04pm UTC](https://discuss.elastic.co/t/jdbc-river-unexpected-behaviour-with-versioning/10878/7 "2013-03-21T16:04:42Z")
I truely admit the concept of the JDBC river updates with versions is not very mature and lacks many features. It assumes a simple full set approach - i.e. move the data into ES, stamp the documents with versions, move next data into ES, stamp again, and then remove the documents with older versions. If the SQL statements of the second rund skip data from the first run by intention, there is no chance to detect version changes correctly. Jörg Am 21.03.13 06:07, schrieb [lyndon.camson@upraxis.com](mailto:lyndon.camson@upraxis.com): > My sql statement will return only subsets of my whole table. if the > other rows are not updated, then does that mean that your plugin will > delete these id's? -- 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:** ![lyndon\_camson](https://avatars.discourse-cdn.com/v4/letter/l/85e7bf/32.png) [@lyndon\_camson](https://discuss.elastic.co/u/lyndon_camson)\ **Post date:** [March 22, 2013, 3:03am UTC](https://discuss.elastic.co/t/jdbc-river-unexpected-behaviour-with-versioning/10878/8 "2013-03-22T03:03:19Z")
I understand. I am new to both java and elasticsearch. Perhaps I would need a custom solution for what I am trying to achieve, which is to index updated/created data from a time interval. It would be a nice additional feature for the jdbc-river plugin to do "upserts" for sql queries that are fetching subsets and will just hook on the \_id field as querying a whole set on mysql would be very much slow. Hope you will consider. Thanks again. -- 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:45am UTC](https://discuss.elastic.co/t/jdbc-river-unexpected-behaviour-with-versioning/10878/9 "2017-07-06T02:45:11Z")