Problem with the jdbc import

[jury89]

Hi i'm having problems with the jdbc importer version 2.2.0.0.

This is my config file myscript.sh:

#!/bin/sh

export JDBC_IMPORTER_HOME=/opt/elasticsearch-jdbc/elasticsearch-jdbc-2.2.0.0
bin=$JDBC_IMPORTER_HOME/bin
lib=$JDBC_IMPORTER_HOME/lib

echo '
{
    "type" : "jdbc",
    "jdbc" : {
        "url" : "jdbc:mysql://localhost:13306/vd3api",
        "user" : "root",
        "password" : "root",
        "sql": "/opt/elasticsearch-jdbc/elasticsearch-jdbc-2.2.0.0/elasticsearch-jdbc-query.sql",
        "elasticsearch" : {
             "host" : "172.17.0.1",
             "port": 9200
        },
        "index" : "vd3api",
        "type" : "publication"
    }
}' | java \
-cp "${lib}/*" \
-Dlog4j.configurationFile=${bin}/log4j2.xml \
org.xbib.tools.Runner \
org.xbib.tools.JDBCImporter

and when i run sh myscript.sh i get this error in the log file

[13:00:30,507][INFO ][importer.jdbc            ][pool-3-thread-1] strategy standard: settings = {elasticsearch.host=172.17.0.1, elasticsearch.port=9200, index=vd3api, password=root, sql=/opt/elasticsearch-jdbc/elasticsearch-jdbc-2.2.0.0/elasticsearch-jdbc-query.sql, type=publication, url=jdbc:mysql://localhost:13306/vd3api, user=root}, context = org.xbib.elasticsearch.jdbc.strategy.standard.StandardContext@54ebdec1
[13:00:30,519][INFO ][importer.jdbc.context.standard][pool-3-thread-1] found sink class org.xbib.elasticsearch.jdbc.strategy.standard.StandardSink@51ab5ba0
[13:00:30,529][INFO ][importer.jdbc.context.standard][pool-3-thread-1] found source class org.xbib.elasticsearch.jdbc.strategy.standard.StandardSource@17e4cdc0
[13:00:30,658][INFO ][org.xbib.elasticsearch.helper.client.BaseTransportClient][pool-3-thread-1] creating transport client on Linux Java HotSpot(TM) 64-Bit Server VM Oracle Corporation 1.8.0_74-b02 25.74-b02 with effective settings {autodiscover=false, client.transport.ignore_cluster_name=false, client.transport.nodes_sampler_interval=5s, client.transport.ping_timeout=5s, cluster.name=elasticsearch, flush_interval=5s, host.0=172.17.0.1, max_actions_per_request=10000, max_concurrent_requests=2, max_volume_per_request=10mb, name=importer, port=9200, sniff=false}
[13:00:30,730][INFO ][org.elasticsearch.plugins][pool-3-thread-1] [importer] modules [], plugins [helper], sites []
[13:00:31,758][INFO ][org.xbib.elasticsearch.helper.client.BaseTransportClient][pool-3-thread-1] trying to connect to [172.17.0.1:9200]
[13:00:37,076][INFO ][org.elasticsearch.client.transport][pool-3-thread-1] [importer] failed to get node info for {#transport#-1}{172.17.0.1}{172.17.0.1:9200}, disconnecting...
org.elasticsearch.transport.ReceiveTimeoutTransportException: [][172.17.0.1:9200][cluster:monitor/nodes/liveness] request_id [0] timed out after [5001ms]
    at org.elasticsearch.transport.TransportService$TimeoutHandler.run(TransportService.java:645) ~[elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_74]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_74]
    at java.lang.Thread.run(Thread.java:745) [?:1.8.0_74]
[13:00:37,137][ERROR][importer.jdbc            ][pool-3-thread-1] error while processing request: no cluster nodes available, check settings {autodiscover=false, client.transport.ignore_cluster_name=false, client.transport.nodes_sampler_interval=5s, client.transport.ping_timeout=5s, cluster.name=elasticsearch, flush_interval=5s, host.0=172.17.0.1, max_actions_per_request=10000, max_concurrent_requests=2, max_volume_per_request=10mb, name=importer, port=9200, sniff=false}
org.elasticsearch.client.transport.NoNodeAvailableException: no cluster nodes available, check settings {autodiscover=false, client.transport.ignore_cluster_name=false, client.transport.nodes_sampler_interval=5s, client.transport.ping_timeout=5s, cluster.name=elasticsearch, flush_interval=5s, host.0=172.17.0.1, max_actions_per_request=10000, max_concurrent_requests=2, max_volume_per_request=10mb, name=importer, port=9200, sniff=false}
    at org.xbib.elasticsearch.helper.client.BulkTransportClient.init(BulkTransportClient.java:165) ~[elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at org.xbib.elasticsearch.helper.client.ClientBuilder.toBulkTransportClient(ClientBuilder.java:102) ~[elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at org.xbib.elasticsearch.jdbc.strategy.standard.StandardSink.createIngest(StandardSink.java:348) ~[elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at org.xbib.elasticsearch.jdbc.strategy.standard.StandardSink.beforeFetch(StandardSink.java:100) ~[elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at org.xbib.elasticsearch.jdbc.strategy.standard.StandardContext.beforeFetch(StandardContext.java:180) ~[elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at org.xbib.elasticsearch.jdbc.strategy.standard.StandardContext.execute(StandardContext.java:161) ~[elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at org.xbib.tools.JDBCImporter.process(JDBCImporter.java:179) ~[elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at org.xbib.tools.JDBCImporter.newRequest(JDBCImporter.java:165) [elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at org.xbib.tools.JDBCImporter.newRequest(JDBCImporter.java:51) [elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at org.xbib.pipeline.AbstractPipeline.call(AbstractPipeline.java:50) [elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at org.xbib.pipeline.AbstractPipeline.call(AbstractPipeline.java:16) [elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_74]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_74]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_74]
    at java.lang.Thread.run(Thread.java:745) [?:1.8.0_74]
[13:00:42,137][INFO ][org.elasticsearch.client.transport][elasticsearch[importer][generic][T#1]] [importer] failed to get node info for {#transport#-1}{172.17.0.1}{172.17.0.1:9200}, disconnecting...
org.elasticsearch.transport.ReceiveTimeoutTransportException: [][172.17.0.1:9200][cluster:monitor/nodes/liveness] request_id [1] timed out after [5001ms]
    at org.elasticsearch.transport.TransportService$TimeoutHandler.run(TransportService.java:645) ~[elasticsearch-jdbc-2.2.0.0-uberjar.jar:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_74]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_74]
    at java.lang.Thread.run(Thread.java:745) [?:1.8.0_74]

Any idea on the reason?

Thank you, Jury

[jprante]

Use port 9300, not 9200.

[jury89]

Thank you for the help :+1:

[ElsayedAwdElsayed]

please where is log file?

[jury89]

I don't know, if I recall it correctly I got that error printed out in the console.

[jprante]

The JDBC importer logs are in the logs directory where you unpacked the JDBC importer zip.

[ElsayedAwdElsayed]

Thank you very much, I have another issue , I run the jdbc importer(orcal) correctly and the index is created in elastic seach but the rows in the orcal table doesn't exit in the elastic search index .

This is the log file

[15:36:02,975][INFO ][importer.jdbc ][pool-3-thread-1] strategy standard: settings = {driver=oracle.jdbc.OracleDriver, elasticsearch.cluster=my-application, elasticsearch.host=localhost, elasticsearch.port=9300, index=egypt, password=impwd, sql=select ID as id,NAME as firstName from cm.EGYPT, type=misr, url=jdbc:oracle:thin:@DEVELOP-TEST:1521:xlp, user=importer}, context = org.xbib.elasticsearch.jdbc.strategy.standard.StandardContext@2b129b69 [15:36:02,979][INFO ][importer.jdbc.context.standard][pool-3-thread-1] found sink class org.xbib.elasticsearch.jdbc.strategy.standard.StandardSink@1a63e307 [15:36:02,983][INFO ][importer.jdbc.context.standard][pool-3-thread-1] found source class org.xbib.elasticsearch.jdbc.strategy.standard.StandardSource@1b784192 [15:36:03,012][INFO ][org.xbib.elasticsearch.helper.client.BaseTransportClient][pool-3-thread-1] creating transport client on Windows 7 Java HotSpot(TM) 64-Bit Server VM Oracle Corporation 1.8.0_91-b14 25.91-b14 with effective settings {autodiscover=false, client.transport.ignore_cluster_name=false, client.transport.nodes_sampler_interval=5s, client.transport.ping_timeout=5s, cluster.name=my-application, flush_interval=5s, host.0=localhost, max_actions_per_request=10000, max_concurrent_requests=16, max_volume_per_request=10mb, name=importer, port=9300, sniff=false} [15:36:03,038][INFO ][org.elasticsearch.plugins][pool-3-thread-1] [importer] modules [], plugins [helper], sites [] [15:36:03,408][INFO ][org.xbib.elasticsearch.helper.client.BaseTransportClient][pool-3-thread-1] trying to connect to [localhost/127.0.0.1:9300] [15:36:03,444][INFO ][org.xbib.elasticsearch.helper.client.BaseTransportClient][pool-3-thread-1] connected to [{Stuntmaster}{2aRmv5JaQYmWq52fX91mTg}{127.0.0.1}{localhost/127.0.0.1:9300}]

[jprante]

@ElsayedAwdElsayed Please open a new issue. Do not take over other person's issues, it is not polite.

[ElsayedAwdElsayed]

I 'am sorry eng @jprante I don't know the policy of these issues forgive me .

[vfa-cuongnv2]

I see, maybe you not add node cluster.

Sample

"elasticsearch" : { "cluster" : "elasticsearch", "host" : "localhost", "port" : 9300 }

[pirat8]

I have the same problem. Elasticsearch version = 5.4.0 and elasticsearch-jdbc-2.3.4.0 .

"elasticsearch" : { "cluster" : "my_cluster_name", "host" : "localhost", "port" : "9300", "autodiscover" : "false" }, (yes, my cluster has name "my_cluster_name")

I also tried with autodiscover=true, no difference. Any idea on the reason?

Thomas

[jprante]

@pirat8 please open a separate issue, do not take over old issues. JDBC importer 2.3.4.0 is for Elasticsearch 2.3.4 only.