First of all you cannot block automapping, so:<\/p>\n
\r\n# elasticsearch.yml\r\nindex.mapper.dynamic: true\r\n<\/pre>\nPre-mapping of your river doesn’t resolve the problem.<\/p>\n
\r\n# part of the river configuration:\r\n\"type_mapping\": {\r\n \"mytype\" : {\r\n \"properties\" : {\r\n \"location\" : {\r\n \"type\" : \"geo_point\"\r\n }\r\n }\r\n }\r\n<\/pre>\nTip: If your node does not work after installation of the plugin you can try to install the plugin in latest beta version.<\/p>\n
\r\n\/usr\/share\/elasticsearch\/bin\/plugin --install jdbc --url http:\/\/xbib.org\/repository\/org\/xbib\/elasticsearch\/plugin\/elasticsearch-river-jdbc\/1.4.0.3.Beta1\/elasticsearch-river-jdbc-1.4.0.3.Beta1-plugin.zip\r\n<\/pre>\nNext, you have to install the driver for mysql:<\/p>\n
\r\nwget \"http:\/\/dev.mysql.com\/get\/Downloads\/Connector-J\/mysql-connector-java-5.1.34.tar.gz\"\r\ntar -xf .\/mysql-connector-java-5.1.34.tar.gz\r\nmv -v .\/mysql-connector-java-5.1.34\/mysql-connector-java-5.1.34-bin.jar \/usr\/share\/elasticsearch\/plugins\/jdbc\/\r\n<\/pre>\nCreate river:<\/p>\n
\r\ncurl -XPUT 'http:\/\/localhost:9200\/_river\/my_personal_river\/_meta' -d '\r\n{\r\n \"type\" : \"jdbc\",\r\n \"jdbc\" : {\r\n \"url\" : \"jdbc:mysql:\/\/10.0.25.203:3306\/db_fixed\",\r\n \"user\" : \"elasticsearchtst\",\r\n \"password\" : \"************\",\r\n \"sql\" : \"SELECT my_id_column _id, some_title_column header, some_text_column content FROM my_table WHERE _id < 18201014 ;\",\r\n \"index\" : \"clipping\",\r\n \"type\" : \"detail\",\r\n \"index_settings\" : {\r\n \"index\" : {\r\n \"number_of_shards\" : 1,\r\n \"number_of_replicas\" : 0\r\n }\r\n }\r\n }\r\n}\r\n'\r\n# Check status\r\ncurl -XGET 'localhost:9200\/_river\/my_personal_river\/_status'\r\n# Delete\r\ncurl -XDELETE 'localhost:9200\/_river\/clipping_detail'\r\n\r\n\r\n<\/pre>\nCautions, conlusions & misconseption:
\nTo understand the instance of the river you should treat the river as other index. This special index is flexible - you can modify it conform to your needs (shards\/replicas\/types). Elasticsearch internally transport all data from the river into the index which is dedicated.
\nYou can disable the river on some of your nodes with parameter:
\nnode.river: _none_ # disabling any river allocation to it<\/p>\nRECOMENDED:
\nIt is recommended to use timestamps in UTC <\/p>\n\r\n {\r\n \"type\" : \"jdbc\",\r\n \"jdbc\" : {\r\n \"url\" : \"jdbc:mysql:\/\/localhost:3306\/test\",\r\n \"user\" : \"\",\r\n \"password\" : \"\",\r\n \"sql\" : [\r\n {\r\n \"statement\" : \"select * from \\\"products\\\" where \\\"mytimestamp\\\" > ?\",\r\n \"parameter\" : [ \"$river.state.last_active_begin\" ]\r\n }\r\n ],\r\n \"index\" : \"my_jdbc_river_index\",\r\n \"type\" : \"my_jdbc_river_type\"\r\n }\r\n }\r\n<\/pre>\nProblem and solutions:
\nIn case your river doesn't work properly...
\nCheck which node is responsible for the river:<\/p>\n\r\ncurl -XGET '10.0.25.197:9200\/_river\/clipping_detail\/_status?pretty'\r\n{\r\n \"_index\" : \"_river\",\r\n \"_type\" : \"details\",\r\n \"_id\" : \"_status\",\r\n \"_version\" : 1,\r\n \"found\" : true,\r\n \"_source\":{\"node\":{\"id\":\"KTLya3PxQ_Supn19MswMRA\",\r\n \"name\":\"es-lab-201\",\r\n \"transport_address\":\"inet[\/10.0.25.180<\/span>:9300]\"}}\r\n}\r\n<\/pre>\nThen, you can log into 10.0.25.180 box and check log to solve potential problems:<\/p>\n
\r\ntail \/var\/log\/elasticsearch\/elastic_lab.log -n 100 | grep jdbc\r\n[2014-11-18 13:30:49,023][ERROR][river.jdbc.RiverPipeline ] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ZELECT clde_id _id, clde_t header, clde_c content FROM the_tab' at line 1\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"First of all you cannot block automapping, so: # elasticsearch.yml index.mapper.dynamic: true Pre-mapping of your river doesn’t resolve the problem. # part of the river configuration: “type_mapping”: { “mytype” : { “properties” : { “location” : { “type” : “geo_point” } } } Tip: If your node does not work after installation of the plugin […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[11,18],"tags":[],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/914"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=914"}],"version-history":[{"count":19,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/914\/revisions"}],"predecessor-version":[{"id":981,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/914\/revisions\/981"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=914"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=914"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=914"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}