Sorry I am newbie to Elasticsearch and I am using elasticsearch-river-jdbc to connect to myql database,everything is working fine except for the fact that everytime it run as per schedule its inserting duplicate record.This is what I am using
curl -XPUT 'localhost:9200/_river/my_jdbc_river/_meta' -d '{
"type" : "jdbc",
"schedule" : "0 0-59 0-23 ? * *",
"jdbc" : {
"url" : "jdbc:mysql://localhost:3306/test",
"user" : "test",
"password" : "test",
"sql" : "select * from test"
}
}'
I go through some docs where it's mentioned that we can run sql query select based on _id but my question is when we created this river only then this unique id is created and that is created on the Elasticsearch side so as per my understanding mysql has no knowledge about this.Please let me know if I am missing something
So if I am writing sql satement like this
"sql" : "select id as _id,a1,a2 from test"
[2015-03-10 13:16:00,018][ERROR][river.jdbc.RiverPipeline ] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'id' in 'field list'
java.io.IOException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'id' in 'field list'