Failing to run a feeder on Windows

[bgiromini]

I am using version 1.4.08 of the plugin and 1.40 for ES. Here is the bat file I am using. I based it off of the mysql bat file from the project.

@echo off

SETLOCAL

if NOT DEFINED ES_HOME goto err

set DIR=%~dp0

set FEEDER_CLASSPATH=%DIR%/*

REM ???
echo {^
    "elasticsearch" : {^
         "cluster" : "elasticsearch",^
         "host" : "localhost",^
         "port" : 9300^
    },^
    "type" : "feeder_test",^
    "jdbc" : {^
        "url" : "jdbc:sqlserver://someserver.database.windows.net:1433;database=db;encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;",^
        "user" : "username",^
        "password" : "password",^
        "sql" :  "SELECT * FROM [Table]",^
        "treat_binary_as_string" : true,^
        "index" : "znode"^
      }^
}

"%JAVA_HOME%\bin\java" -cp "%FEEDER_CLASSPATH%" "org.xbib.elasticsearch.plugin.jdbc.feeder.Runner" "org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder"
goto finally

:err
echo JAVA_HOME and ES_HOME environment variable must be set!
pause

:finally

ENDLOCAL

When I run this from the command line, I get the following error:

java.lang.IncompatibleClassChangeError: class org.xbib.elasticsearch.plugin.jdbc .state.RiverStatesMetaData$Factory has interface org.elasticsearch.cluster.metad ata.MetaData$Custom$Factory as super class at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:800) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:14 2) at org.xbib.elasticsearch.plugin.jdbc.classloader.uri.URIClassLoader.def ineClass(URIClassLoader.java:310) at org.xbib.elasticsearch.plugin.jdbc.classloader.uri.URIClassLoader.acc ess$100(URIClassLoader.java:63) at org.xbib.elasticsearch.plugin.jdbc.classloader.uri.URIClassLoader$1.r un(URIClassLoader.java:133) at java.security.AccessController.doPrivileged(Native Method) at org.xbib.elasticsearch.plugin.jdbc.classloader.uri.URIClassLoader.fin dClass(URIClassLoader.java:126) at java.lang.ClassLoader.loadClass(ClassLoader.java:425) at java.lang.ClassLoader.loadClass(ClassLoader.java:358) at org.xbib.elasticsearch.plugin.jdbc.state.RiverStatesMetaData. (RiverStatesMetaData.java:39) at org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder.(JDBCFee der.java:76) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct

orAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC onstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at java.lang.Class.newInstance(Class.java:374) at org.xbib.elasticsearch.plugin.jdbc.feeder.Runner.main(Runner.java:45)

Anyone have any suggestions how I can fix this issue?

[bgiromini]

I finally got a bat file to work.

@echo off

SETLOCAL

if NOT DEFINED ES_HOME goto err

set DIR=%~dp0

set FEEDER_CLASSPATH=%DIR%/*

echo {^
    "elasticsearch" : {^
         "cluster" : "elasticsearch",^
         "host" : "localhost",^
         "port" : 9300,^
         "type" : "jdbc",^
         "schedule" : "0 0-59 0-23 ? * *"^
    },^
    "jdbc" : {^
        "url" : "url",^
        "user" : "user",^
        "password" : "password",^
        "sql" :  "sql",^
        "treat_binary_as_string" : true,^
        "index" : "yrdy",^
        "type" : "feeder_test"^
      }^
}^ | "%JAVA_HOME%\bin\java" -cp "%FEEDER_CLASSPATH%" "org.xbib.elasticsearch.plugin.jdbc.feeder.Runner" "org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder"
goto finally

:err
echo JAVA_HOME and ES_HOME environment variable must be set!
pause

:finally

ENDLOCAL

I can see my index being created but the river never runs. Any suggestions on what I am doing wrong?

[mradamlacey]

Maybe modify your schedule (or remove it entirely)?

You could try:

"schedule" : "0 * * * * *"

to run every minute.

It wasn't entirely clear where your script file was located (and therefore what jars were being found by your FEEDER_CLASSPATH variable.

I placed and ran the script from ES_HOME and made the following change:

set FEEDER_CLASSPATH=%DIR%/lib/*;%DIR%/plugins/jdbc/*

I removed the schedule proprety and works like a charm.

[bgiromini]

I had the script file in ES_HOME. I will give this tip a try. Thanks for working on this!

[d0lur1g]

I success configure with yours advices. With a simple line of sql, it's run.

But with multiline SQL, it's not run.

What is it the sql syntax with multiline?

  "jdbc" : {
    "url" : "url",
    "user" : "user",
    "password" : "password",
    "sql" :  "SELECT MBugs.id AS _id,
            MProjects.name AS Projet,
            MCategory.name AS Categorie,
            MBugs.summary AS Resume,
            CASE MBugs.status
            WHEN 90 THEN 'Fermé'",
            FROM mantis_bug_table MBugs
            INNER JOIN mantis_project_table MProjects ON MBugs.project_id = MProjects.id
            INNER JOIN mantis_user_table MUsers ON MBugs.reporter_id = MUsers.id
            INNER JOIN mantis_category_table MCategory ON MBugs.category_id = MCategory.id
            LEFT OUTER JOIN mantis_user_table MUsers2 ON MBugs.handler_id = MUsers2.id
            WHERE
            MBugs.project_id > 1100
            ORDER BY MProjects.name, MBugs.severity, MBugs.priority DESC"
    "treat_binary_as_string" : true,
    "index" : "yrdy",
    "type" : "feeder_test"
  }

And the error is :

  {    "elasticsearch" : {         "cluster" : "elasticsearch",         "host" : "localhost",         "port" : 9300,         "type" : "jdbc",         "schedule" : "0 0-59 0-23 ? * *"    },    "jdbc" : {        "url" : "jdbc:mysql://a99mantis001.cdweb.biz:3306/mantis",        "user" : "user",        "password" : "user",        "sql" : "SELECT MBugs.id AS _id,
  'MProjects.name' is not recognized as an internal or external command,
  operable program or batch file.
  'MCategory.name' is not recognized as an internal or external command,
  operable program or batch file.
  'MBugs.summary' is not recognized as an internal or external command,
  operable program or batch file.
  'CASE' is not recognized as an internal or external command,
  operable program or batch file.
  'WHEN' is not recognized as an internal or external command,

  etc...

It's parse at line break.

[d0lur1g]

It works. In fact, I put everything on a single line. That does not like the indentation.

Thanks!

[r2d2-cc]

For anyone having problems setting up elasticsearch-jdbc-1.7.0.1 on Windows there is a simple way using Powershell:

jdbc_mysql.json

{
   "type":"jdbc",
   "jdbc":{
      "url":"jdbc:mysql://localhost:3306/test",
      "user":"test",
      "password":"test",
      "sql":"SELECT id as _id, id, name,email FROM test",
      "index":"users",
      "type":"user",
      "autocommit":"true"
   }
}

jdbc_mysql.ps1

function Get-PSVersion {
    if (test-path variable:psversiontable) {$psversiontable.psversion} else {[version]"1.0.0.0"}
}
$powershell = Get-PSVersion
if($powershell.Major -le 2 ){
    Write-Error "Oh, so sorry, this script requires Powershell 3 (due to convertto-json)"
    exit
}
if((Test-Path   env:\JAVA_HOME) -eq $false){
    Write-Error "Environment variable JAVA_HOME must be set to your java home"
    exit
}
curl -XDELETE "http://localhost:9200/users"

$DIR = "D:\programs\elasticsearch\plugins\elasticsearch-jdbc-1.7.0.1\"
$FEEDER_CLASSPATH="$DIR\lib"
$FEEDER_LOGGER="file://$DIR\bin\log4j2.xml"

java -cp "$FEEDER_CLASSPATH\*" -"Dlog4j.configurationFile=$FEEDER_LOGGER" "org.xbib.tools.Runner" "org.xbib.tools.JDBCImporter" jdbc_mysql.json

I described the whole process here http://r2d2.cc/2015/08/05/elastic-search-on-windows-with-mysql-sql-server-2014-and-import-feeder/

[sgandikota]

this is not working as described. I had 2.2.2 of ES with 2.2.0 of JDBC importer and tried to run the PS1 file and nothing happens. it does not insert data