# JDBC River SQL Subquery Issue **URL:** \ **Category:** Elasticsearch\ **Created:** [December 10, 2013, 5:39pm UTC](https://discuss.elastic.co/t/jdbc-river-sql-subquery-issue/14794 "2013-12-10T17:39:15Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Justin\_Doles](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/justin_doles/32/40730_2.png) [@Justin\_Doles](https://discuss.elastic.co/u/Justin_Doles)\ **Post date:** [December 10, 2013, 5:39pm UTC](https://discuss.elastic.co/t/jdbc-river-sql-subquery-issue/14794/1 "2013-12-10T17:39:15Z")
In my testing it seems that JDBC river is caching the subquery in my statement. Below is the river I'm testing with: { "type" : "jdbc", "jdbc" : { "strategy" : "simple", "poll" : "5s", "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://127.0.0.1:6033/test", "user" : "user", "password" : "password", "sql" : "SELECT `orders`.`id` AS `_id`, `orders`.`name`, `orders`.`description`, BinaryToGuid(`orders`.`guid`), `orders`.`number`, `orders`.`date` FROM `test`.`orders` WHERE `orders`.`date` \> (SELECT `orders_meta`.`last_run` FROM `test`.`orders_meta`);", "acksql" : "UPDATE `test`.`orders_meta` SET `last_run` = UTC\_TIMESTAMP() WHERE `id` = 1;" }, "index" : { "index" : "orders", "type" : "order" } } The poll gets 7 items which is fine on the first run. Subsequent runs should get 0 since there are no new items. But they get 7 items again which tells me that it's using the same `last_run` it selected the first time. Any thoughts on how to correct this? I was looking through the source of JDBC river, but I'm not that familiar with Java (though I understand what I see). Justin -- 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). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/ef5cb3fa-fb87-430e-8a35-82680a3a71c0%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/ef5cb3fa-fb87-430e-8a35-82680a3a71c0%40googlegroups.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:** [December 10, 2013, 5:46pm UTC](https://discuss.elastic.co/t/jdbc-river-sql-subquery-issue/14794/2 "2013-12-10T17:46:06Z")
I think this is also a matter of committing, so autocommit: true in the river configuration (which is disabled by default) should help. Jörg -- 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). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHEQyqwnUj\_wt4W%3DFC3vHpDdzDJcytDrw0zQZfLW0%3DQfQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHEQyqwnUj_wt4W%3DFC3vHpDdzDJcytDrw0zQZfLW0%3DQfQ%40mail.gmail.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Justin\_Doles](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/justin_doles/32/40730_2.png) [@Justin\_Doles](https://discuss.elastic.co/u/Justin_Doles)\ **Post date:** [December 10, 2013, 5:54pm UTC](https://discuss.elastic.co/t/jdbc-river-sql-subquery-issue/14794/3 "2013-12-10T17:54:36Z")
autocommit: true did the trick. I think it's time to up my caffeine intake... Thanks. Justin On Tuesday, December 10, 2013 12:46:06 PM UTC-5, Jörg Prante wrote: > I think this is also a matter of committing, so autocommit: true in the > river configuration (which is disabled by default) should help. > > Jörg -- 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). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/d57f123f-45f1-4be9-a8b7-5aa42f28f048%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d57f123f-45f1-4be9-a8b7-5aa42f28f048%40googlegroups.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:02am UTC](https://discuss.elastic.co/t/jdbc-river-sql-subquery-issue/14794/4 "2017-07-06T02:02:10Z")
Closed on July 6, 2017, 2:02am UTC