Logstash filter jdbc_streaming : JDBC:AdapterNotFound: Could not load jdbc/mssql adapter: adapter class not registered in ADAPTER_MAP>

Elastic Stack Logstash
1

I'm trying to use logstash filter plugin jdbc_streaming,

my logstash version is 7.13
OS centos 7
openjdk 11.0.11 2021-04-20 LTS
jdbc 6.0 : "/home/jdbc/sqljdbc_6.0/enu/jre7/sqljdbc41.jar"

my logstash config

input {
  generator {
    lines => [
"CBN00000020121",
"CBN00000040121"
    ]
    count => 1
  }
}
filter{
csv{
   columns => ["noinvoice"]
   separator => ","
}
{
jdbc_streaming {
    jdbc_driver_library => "/home/jdbc/sqljdbc_6.0/enu/jre7/sqljdbc41.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:mssql://someip:1433/mydatabase"
    jdbc_user => "user"
    jdbc_password => "password"
    statement => "select EmailTime from TestInvoice.dbo.storing_ssis WHERE invoiceno= :noinvoice"
    parameters => {"noinvoice" => "noinvoice"}
    target => "EmailTime"
  }
}
output{
stdout{codec => rubydebug}
}

When I run by /usr/share/logstash/bin/logstash -f fjdbc.conf
it returns

Using bundled JDK: /usr/share/logstash/jdk
OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.
WARNING: Could not find logstash.yml which is typically located in $LS_HOME/config or /etc/logstash. You can specify the path using --path.settings. Continuing using the defaults
Could not find log4j2 configuration at path /usr/share/logstash/config/log4j2.properties. Using default config which logs errors to the console
[INFO ] 2021-06-10 15:55:36.686 [main] runner - Starting Logstash {"logstash.version"=>"7.13.1", "jruby.version"=>"jruby 9.2.16.0 (2.5.7) 2021-03-03 f82228dc32 OpenJDK 64-Bit Server VM 11.0.11+9 on 11.0.11+9 +indy +jit [linux-x86_64]"}
[WARN ] 2021-06-10 15:55:37.253 [LogStash::Runner] multilocal - Ignoring the 'pipelines.yml' file because modules or command line options are specified
[INFO ] 2021-06-10 15:55:39.668 [Api Webserver] agent - Successfully started Logstash API endpoint {:port=>9600}
[INFO ] 2021-06-10 15:55:41.520 [Converge PipelineAction::Create<main>] Reflections - Reflections took 69 ms to scan 1 urls, producing 24 keys and 48 values
[ERROR] 2021-06-10 15:55:43.988 [[main]-pipeline-manager] javapipeline - Pipeline error {:pipeline_id=>"main", :exception=>#<Sequel::AdapterNotFound: Could not load jdbc/mssql adapter: adapter class not registered in ADAPTER_MAP>, :backtrace=>["/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.44.0/lib/sequel/database/connecting.rb:97:in `load_adapter'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.44.0/lib/sequel/adapters/jdbc.rb:380:in `adapter_initialize'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.44.0/lib/sequel/database/misc.rb:154:in `initialize'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.44.0/lib/sequel/database/connecting.rb:57:in `connect'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.44.0/lib/sequel/core.rb:124:in `connect'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.0.7/lib/logstash/plugin_mixins/jdbc_streaming.rb:62:in `prepare_jdbc_connection'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.0.7/lib/logstash/filters/jdbc_streaming.rb:122:in `register'", "org/logstash/config/ir/compiler/AbstractFilterDelegatorExt.java:75:in `register'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:228:in `block in register_plugins'", "org/jruby/RubyArray.java:1809:in `each'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:227:in `register_plugins'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:586:in `maybe_setup_out_plugins'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:240:in `start_workers'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:185:in `run'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:137:in `block in start'"], "pipeline.sources"=>["/etc/logstash/fjdbc.conf"], :thread=>"#<Thread:0x67751750 run>"}
[INFO ] 2021-06-10 15:55:43.991 [[main]-pipeline-manager] javapipeline - Pipeline terminated {"pipeline.id"=>"main"}
[ERROR] 2021-06-10 15:55:44.015 [Converge PipelineAction::Create<main>] agent - Failed to execute action {:id=>:main, :action_type=>LogStash::ConvergeResult::FailedAction, :message=>"Could not execute action: PipelineAction::Create<main>, action_result: false", :backtrace=>nil}
[INFO ] 2021-06-10 15:55:44.146 [LogStash::Runner] runner - Logstash shut down.

how i can specify the adapter class?

update

Since I'm using jdk 11, I should've use jdbc 7.2.2. I've specified the path too into /root/jdbc2/sqljdbc_7.2/enu/mssql-jdbc-7.2.2.jre11.jar but it still returning the same error

update2

I've move the jar into /usr/share/logstash/logstash-core/lib/jars/ then change jdbc_driver_library=>""
it still returnin

[ERROR] 2021-06-11 17:07:46.257 [Converge PipelineAction::Create<main>] jdbcstreaming - Invalid setting for jdbc_streaming filter plugin:

  filter {
    jdbc_streaming {
      # This setting must be a path
      # File does not exist or cannot be opened
      jdbc_driver_library => ""
      ...
    }
  }

update 3

I've move the path of the jar, it was /root/jdbc2/sqljdbc_7.2/enu/mssql-jdbc-7.2.2.jre11.jar into /etc/logstash/jar/mssql-jdbc-7.2.2.jre11.jar
when I run again it returning the same error as:

[ERROR] 2021-06-11 17:13:34.597 [[main]-pipeline-manager] javapipeline - Pipeline error {:pipeline_id=>"main", :exception=>#<Sequel::AdapterNotFound: Could not load jdbc/mssql adapter: adapter class not registered in ADAPTER_MAP>, :backtrace=>["/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.44.0/lib/sequel/database/connecting.rb:97:in `load_adapter'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.44.0/lib/sequel/adapters/jdbc.rb:380:in `adapter_initialize'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.44.0/lib/sequel/database/misc.rb:154:in `initialize'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.44.0/lib/sequel/database/connecting.rb:57:in `connect'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.44.0/lib/sequel/core.rb:124:in `connect'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.0.7/lib/logstash/plugin_mixins/jdbc_streaming.rb:62:in `prepare_jdbc_connection'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.0.7/lib/logstash/filters/jdbc_streaming.rb:122:in `register'", "org/logstash/config/ir/compiler/AbstractFilterDelegatorExt.java:75:in `register'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:228:in `block in register_plugins'", "org/jruby/RubyArray.java:1809:in `each'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:227:in `register_plugins'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:586:in `maybe_setup_out_plugins'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:240:in `start_workers'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:185:in `run'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:137:in `block in start'"], "pipeline.sources"=>["/etc/logstash/fjdbc.conf"], :thread=>"#<Thread:0x2a713d1d run>"}

update 4

I freshly redownload jdbc 7.2 as

pwd /home/

mkdir jdbc2

wget https://download.microsoft.com/download/4/D/C/4DCD85FA-0041-4D2E-8DD9-833C1873978C/sqljdbc_7.2.2.0_enu.tar.gz

gzip -d sqljdbc_7.2.2.0_enu.tar.gz

tar -xf sqljdbc_7.2.2.0_enu.tar

use it as final fjdbc.conf

input {
  generator {
    lines => [
"CBN00000020121",
"CBN00000040121"
    ]
    count => 1
  }
input {
  generator {
    lines => [
"CBN00000020121",
"CBN00000040121"
    ]
    count => 1
  }
}
filter{
jdbc_streaming {
#    jdbc_driver_library => "/root/jdbc2/sqljdbc_7.2/enu/mssql-jdbc-7.2.2.jre11.jar"
#    jdbc_driver_library => "/etc/logstash/jar/mssql-jdbc-7.2.2.jre11.jar"
    jdbc_driver_library => "/home/jdbc2/sqljdbc_7.2/enu/mssql-jdbc-7.2.2.jre11.jar"
#    jdbc_driver_class => ""
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
   jdbc_connection_string => "jdbc:mssql://someip:1433/TestInvoice"
#    jdbc_user => "sa"
    jdbc_password => "BIDWT3st!"
    statement => "select EmailTime from TestInvoice.dbo.storing_ssis WHERE invoiceno= :noinvoice"
    parameters => {"noinvoice" => "noinvoice"}
    target => "EmailTime"
  }
}
output{
stdout{codec => rubydebug}
}
2

This is what I have to connect to microsoft sqlserver

 jdbc {
        jdbc_validate_connection => false
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_connection_string => "jdbc:sqlserver://server1;databaseName=REPORTING;user=username;password=mypassword;"
        jdbc_user => "username"
        jdbc_password => "mypassword"
        statement => "select * from REPORTING.dbo.v_CONTROLS"
        schedule => "00 06 * * *"
        clean_run=>true
        record_last_run => false
       }

I have copy mssql-jdbc-7.4.1.jre8.jar in to /usr/share/logstash/logstash-core/lib/jars/

1 Like
3

Dear Sachin,
thank you so much for your reply. I've done this on update 2. I've copied the jar files into /usr/share/logstash/logstash-core/lib/jars but it still doesn't work.
I will try to do your conf of jdbc_connection_string, but do you have any idea why it <Sequel::AdapterNotFound: Could not load jdbc/mssql adapter: adapter class not registered in ADAPTER_MAP>

Thank you

4

Dear Sachin,
I've tried on your suggestion.
I'm installing new jdbc as 8.2 it still suit jdk 11.
copy it to /usr/share/logstash/logstash-core/lib/jars/ , removing line jdbc_driver_library, change jdbc_connection_string. It still returning the same error as

[ERROR] 2021-06-14 20:43:54.934 [[main]-pipeline-manager] javapipeline - Pipeline error {:pipeline_id=>"main", :exception=>#<Sequel::AdapterNotFound: Could not load jdbc/mssql adapter: adapter class not registered in ADAPTER_MAP>, :backtrace=>["/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.45.0/lib/sequel/database/connecting.rb:97:in `load_adapter'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.45.0/lib/sequel/adapters/jdbc.rb:380:in `adapter_initialize'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.45.0/lib/sequel/database/misc.rb:154:in `initialize'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.45.0/lib/sequel/database/connecting.rb:57:in `connect'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.45.0/lib/sequel/core.rb:124:in `connect'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-filter-jdbc_streaming-1.0.10/lib/logstash/plugin_mixins/jdbc_streaming.rb:89:in `prepare_jdbc_connection'", "/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-filter-jdbc_streaming-1.0.10/lib/logstash/filters/jdbc_streaming.rb:120:in `register'", "org/logstash/config/ir/compiler/AbstractFilterDelegatorExt.java:75:in `register'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:228:in `block in register_plugins'", "org/jruby/RubyArray.java:1809:in `each'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:227:in `register_plugins'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:586:in `maybe_setup_out_plugins'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:240:in `start_workers'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:185:in `run'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:137:in `block in start'"], "pipeline.sources"=>["/etc/logstash/fjdbc.conf"], :thread=>"#<Thread:0x511185bf run>"}
[INFO ] 2021-06-14 20:43:54.937 [[main]-pipeline-manager] javapipeline - Pipeline terminated {"pipeline.id"=>"main"}
[ERROR] 2021-06-14 20:43:54.962 [Converge PipelineAction::Create<main>] agent - Failed to execute action {:id=>:main, :action_type=>LogStash::ConvergeResult::FailedAction, :message=>"Could not execute action: PipelineAction::Create<main>, action_result: false", :backtrace=>nil}
[INFO ] 2021-06-14 20:43:55.078 [LogStash::Runner] runner - Logstash shut down.
5

this looks like defiantly something to do with driver version mismatch.
you have to do lot of different test as I don't know your configuration. I also spend almost two days testing out different combination and searching on internet and then configuration that I posted work.

this one was the key
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"

Curretnly i have
ls -la /usr/share/logstash/logstash-core/lib/jars/mssql-jdbc-7.4.1.jre8.jar
-rw-r--r-- 1 logstash logstash 1209659 Sep 17 2019 /usr/share/logstash/logstash-core/lib/jars/mssql-jdbc-7.4.1.jre8.jar

6

Hi sachin, Thank you for your opinion. Yea, It seems so, do you have any reference where abut this plugins jdbc requirements? or it just suit with every jdbc?

7

when I was testing I started from one point and started writing down which one didn't work and started moving one version up, as it also depend on what version of microsoft sqlserver you have

1 Like
8

Hello Sachin,
Yes! I was kind of desperate so I recreate the whole conf file and test each line. Afterall it works and I don't know what makes it so long. Thank you so much for your assistance.

9

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