JDBC River SQL Subquery Issue


(Justin Doles) #1

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.
To view this discussion on the web visit 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.


(Jörg Prante) #2

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.
To view this discussion on the web visit 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.


(Justin Doles) #3

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.
To view this discussion on the web visit 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.


(system) #4