Invalid setting for jdbc streaming filter plugin

[Mohan_vel]

Hi All i am trying to use jdbc streaming in logstash but i am getting below error.

Below is my config

input {
jdbc {
jdbc_driver_library => "mssql-jdbc-7.4.1.jre12-shaded"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxx;"
jdbc_user => "xxx"
jdbc_password => "xxx"
statement => "Select Policyholdername,Age,Policynumber,Dob,Client_Address from policy"
}
}
filter{
jdbc_streaming {
jdbc_driver_library => "mssql-jdbc-7.4.1.jre12-shaded"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxx;"
jdbc_user => "xxx"
jdbc_password => "xxx"
statement => "select claimnumber,claimtype from claim where policynumber = :Policynumber"
parameters => {"policynumber" => "policynumber"}
target => "claim_details"
}
}
output {
stdout { codec => rubydebug }
}

Also i have installed jdbc_streaming plugin using below command
logstash-plugin install logstash-filter-jdbc_streaming
it's installed successfully
Could some one help me to resolve the issue.

[Badger]

It appears to be expanding the relative path into something that does not exist. Try using an absolute path for jdbc_driver_library.

[Mohan_vel]

Hi Badger thanks for the reply attached screen shot for reference i have pasted the driver inside logstash and i have tried giving absolute path as suggested by you refer below code

filter{
jdbc_streaming {
jdbc_driver_library => "D:/Users/mmurugesan/Desktop/logstash-7.2.0/logstash-core/lib/jars/mssql-jdbc-7.4.1.jre12-shaded"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxxx;"
jdbc_user => "xxxx"
jdbc_password => "xxxx"
statement => "select claimnumber,claimtype from claim where policynumber = :Policynumber"
parameters => {"policynumber" => "policynumber"}
target => "claim_details"
}

Still getting the same error

kindly suggest thanks in advance

[Badger]

@Mohan_vel

jdbc_driver_library => "D:/Users/mmurugesan/Desktop/logstash-7.2.0/logstash-core/lib/jars/mssql-jdbc-7.4.1.jre12-shaded"

I don't think you have File Explorer configured to show file extensions. Does

jdbc_driver_library => "D:/Users/mmurugesan/Desktop/logstash-7.2.0/logstash-core/lib/jars/mssql-jdbc-7.4.1.jre12-shaded.jar"

work?

[Mohan_vel]

Badger, I have tried getting below error

I think if i give the absolute path of the driver it is saying that unable to load the driver refer above screenshot.
Logstash-version-7.2.0
SQLserver - 2014 express edition
SQL driver - mssql-jdbc-7.4.1.jre12-shaded.jar
java - javac 13.0.1

Even for jdbc input plugin if i give the absolute path it's not working. only if i paste the MSSql jdbc driver inside the logstash-7.2.0\logstash-core\lib\jars and make jdbc library as empty then only it's working fine. code given below.

input {
jdbc {
jdbc_driver_library => ""
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxxx;"
jdbc_user => "xxx"
jdbc_password => "xxxx"
statement => "Select Policyholdername,Age,Policynumber,Dob,Client_Address from policy"
}
}
filter{

}
output {
stdout { codec => rubydebug }
}

So ultimately if i give absolute path it's not working, any guess from your end.

[inhinyera16 (Archelle Pagapulan)]

Pls use sqljdbc4.jar and see my comment in your main post
Also copy pasted my config

Hope it helps

[Mohan_vel]

Hi @inhinyera16

I have tried still getting below error.

If i give absolute path of the driver location it saying failed to load driver. in screen shot it print the result in power shell because i place the driver in \logstash-7.2.0\logstash-core\lib\jars

input {
jdbc {
jdbc_driver_library => "D:/Users/mmurugesan/Desktop/driver/sqljdbc4.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxx;"
jdbc_user => "xxxx"
jdbc_password => "xxx"
statement => "Select Policyholdername,Age,Policynumber,Dob,Client_Address from policy"
}
}
filter{

}
output {
stdout { codec => rubydebug }
}

So my point is there is no issue with the driver because if i place it inside jars it's working fine but for abs path which i have provided is not working.

My driver folder is available in desktop attached screenshot.

If i am doing something wrong basically pls let me know. Thank you

[inhinyera16 (Archelle Pagapulan)]

Hmm.

Can you put the jar file under {LS_HOME}/logstash-core/lib/jars
Then in your config file, make below parameters empty
jdbc_driver_library => ""

[ropc (Romain Chanu)]

@Mohan_vel - the error TypeError: failed to coerce jdk.internal.loader.ClassLoaders$AppClassLoader to java.net.URLClassLoader is a known issue and has been fixed in:

• jdbc_static filter plugin version 1.0.7 (c.f Changelog).
• jdbc_streaming filter plugin version 1.0.10. This is a related Github issue for this specific plugin and the Changelog.

The problem in both plugins is actually the same problem and it is explained here.

If you could update the affected plugin to the latest version, that will solve your issue. You could then try to use the jdbc_driver_library with the JDBC driver file path.

The workaround to this issue is to place the JDBC driver in the Logstash Java classpath - which you tried and it works.

[Mohan_vel]

Hi @inhinyera16,

I have tried it it's not working for jdbc_streaming alone it throws error invalid setting must be a path for jdbc_streaming.

[Mohan_vel]

Hi @ropc -

I have upgraded my logstash from 7.2.0 to 7.5.2 loader issue has resolved. Thanks a lot. i have given my config file for reference.

input {
jdbc {
jdbc_driver_library => "D:/Users/xxxxx/Desktop/driver/mssql-jdbc-7.4.1.jre12-shaded.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxxx;"
jdbc_user => "xxxx"
jdbc_password => "xxx"
statement => "Select Policyholdername,Age,Policynumber,Dob,Client_Address from policy"
}
}
filter{
jdbc_streaming {
jdbc_driver_library => "D:/Users/xxxxxx/Desktop/driver/mssql-jdbc-7.4.1.jre12-shaded.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxxx;"
jdbc_user => "xxxx"
jdbc_password => "xxxx"
statement => "select claimnumber,claimtype from claim where policynumber = :policynumber"
parameters => {"policynumber" => "policynumber"}
target => "claim_details"
}
}
output {
stdout { codec => rubydebug }
}

[ropc (Romain Chanu)]

@Mohan_vel You're welcome FYI - the issue was actually fixed in jdbc_streaming filter plugin version 1.0.10 which is shipped in Logstash 7.5.1. Upgrading the plugin or upgrading Logstash is the solution.