JDBC importer : Data not received on Elasticsearch index

Elastic Stack Elasticsearch
1

Hello,

Below is my script to import data from MySQL server to Elasticsearch.

`DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
bin=${DIR}/../bin
lib=${DIR}/../lib

echo '
{
    "type" : "jdbc",
    "jdbc" : {
        "schedule" : "0 0-59 0-23 ? * *",
        "url" : "jdbc:mysql://testdb.amazonaws.com:3306/TestDB",
        "user" : "root",
        "password" : "Test2016",
        "sql" : "select * from AddressBook",
        "index" : "myjdbc",
        "type" : "mytype",
        	"elasticsearch" : {
            "cluster" : "elasticsearch",
            "host" : "elastic.test.com",
            "port" : 9300
        }
    }
}
' | java \
    -cp "${lib}/*" \
    -Dlog4j.configurationFile=${bin}/log4j2.xml \
    org.xbib.tools.Runner \
    org.xbib.tools.JDBCImporter`

When i execute this scrip Using below command ./test.sh from JDBC importbin directory..I do not get any result..
I have created myjdbc index on Elasticsearch but i dont receive any data on this index from MySQL table..Please help me to figure out where i made mistake..Thank you.

2

Thank you for your interest in JDBC importer.

Your script looks correct. Please look into the log files for messages. Do not post host names, ports, or password in public forums!

JDBC importer is a community-supported software for Elasticsearch. You can also ask for help at https://github.com/jprante/elasticsearch-jdbc/issues

3

Hi,

below is my scrip, but do not get any data from MySQL to Elasticsearch.I have checked my elasticsearch logs but i don't see any data transfer from MySQL to elasticsearch.

DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
bin=${DIR}/../bin
lib=${DIR}/../lib

echo '
{
    "type" : "jdbc",
    "jdbc" : {
        "url" : "jdbc:mysql://testdb/TestES",
        "user" : "root",
        "password" : "test2016",
        "sql" : "select * from Profile",
        "index" : "mysql",
        "type" : "mysql",
        "elasticsearch" : {
            "cluster" : "elasticsearch",
            "host" : "localhost",
            "port" : 9300
        },
        "max_bulk_actions" : 20000,
        "max_concurrent_bulk_requests" : 10,
        "index_settings" : {
            "index" : {
                "number_of_shards" : 1,
                "number_of_replica" : 0
            }
        }
    }
}
' | java \
    -cp "${lib}/*" \
    -Dlog4j.configurationFile=${bin}/log4j2.xml \
    org.xbib.tools.Runner \
    org.xbib.tools.JDBCImporter

I have database name TestES and table name profile.
What i understand is, When i use "select * from Profile statement, it will get all rows from profile table and index it into mysql index of elasticsearch.

  1. Do i need to provide mapping for this data into script ??. Or elasticsearch will automatically handle data mapping that are coming from MySQL.?
4

hi,

I found below exception..

 [importer] exception caught on transport layer [[id: 0x56a7eccc, /172.31.26.30:56672 => elastic.vaster.com/172.31.28.244:9300]], closing connection
java.lang.NullPointerException
	at org.elasticsearch.transport.netty.MessageChannelHandler.handleException(MessageChannelHandler.java:207) ~[elasticsearch-2.3.3.jar:2.3.3]
	at org.elasticsearch.transport.netty.MessageChannelHandler.handlerResponseError(MessageChannelHandler.java:202) ~[elasticsearch-2.3.3.jar:2.3.3]
	at org.elasticsearch.transport.netty.MessageChannelHandler.messageReceived(MessageChannelHandler.java:136) ~[elasticsearch-2.3.3.jar:2.3.3]
	at org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:70) ~[netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerContext.sendUpstream(DefaultChannelPipeline.java:791) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:296) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.handler.codec.frame.FrameDecoder.unfoldAndFireMessageReceived(FrameDecoder.java:462) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.handler.codec.frame.FrameDecoder.callDecode(FrameDecoder.java:443) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.handler.codec.frame.FrameDecoder.messageReceived(FrameDecoder.java:303) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:70) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:559) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:268) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:255) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.socket.nio.NioWorker.read(NioWorker.java:88) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.socket.nio.AbstractNioWorker.process(AbstractNioWorker.java:108) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.socket.nio.AbstractNioSelector.run(AbstractNioSelector.java:337) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.socket.nio.AbstractNioWorker.run(AbstractNioWorker.java:89) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.channel.socket.nio.NioWorker.run(NioWorker.java:178) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.util.ThreadRenamingRunnable.run(ThreadRenamingRunnable.java:108) [netty-3.10.5.Final.jar:?]
	at org.jboss.netty.util.internal.DeadLockProofWorker$1.run(DeadLockProofWorker.java:42) [netty-3.10.5.Final.jar:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_91]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_91]
	at java.lang.Thread.run(Thread.java:745) [?:1.8.0_91]

Can you please help to figure out this issue as well..thank you..