Invalid setting for jdbc streaming filter plugin

Mohan_vel · February 1, 2020, 3:45pm

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 · February 1, 2020, 4:15pm

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 · February 1, 2020, 7:32pm

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 · February 1, 2020, 8:00pm

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 · February 2, 2020, 12:53pm

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 · February 3, 2020, 6:49am

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

Hope it helps

Mohan_vel · February 3, 2020, 8:02am

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 · February 3, 2020, 8:11am

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 · February 3, 2020, 8:15am

@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 · February 3, 2020, 9:42am

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 · February 3, 2020, 10:51am

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 · February 3, 2020, 2:00pm

@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.

system · March 2, 2020, 2:00pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
How can i install jdbc_streaming filter plugin Logstash	3	2076	September 15, 2017
Jdbc streaming and sql error Logstash	2	357	July 19, 2019
Logstash jdbc_streaming, sql statement Logstash	1	362	March 24, 2019
Jdbcstreaming exception Logstash	1	308	September 26, 2018
Logstash filter jdbc_streaming : JDBC:AdapterNotFound: Could not load jdbc/mssql adapter: adapter class not registered in ADAPTER_MAP> Logstash	8	730	July 14, 2021

Invalid setting for jdbc streaming filter plugin

Related topics