Logstash JDBC Static Filter Can't Connect to SQLite DB

Dustin527 · April 17, 2023, 9:34pm

Hi

I am having trouble getting the JDBC static filter to work with an SQLite DB. I am using the xerial sqlite jdbc lib on Debian and the latest logstash package. I even have a small java program that can connect to and query the database with no issue. I can't help but wonder if I'm running into a bug but I can't find any other reports of this exact same issue.

Steps to reproduce:

Install latest logstash

I am using 8.7.0 with the bundled JDK.

Install xerial jdbc

libxerial-sqlite-jdbc-java:
  Installed: 3.36.0.3+dfsg1-3~bpo11+1
  Candidate: 3.36.0.3+dfsg1-3~bpo11+1
  Version table:
 *** 3.36.0.3+dfsg1-3~bpo11+1 100
        100 http://deb.debian.org/debian bullseye-backports/main amd64 Packages
        100 /var/lib/dpkg/status

Create a sqlite db and schema. Mine is just a couple of tables.
Setup the filter to point at the db

test.config snippet

filter {
 jdbc_static {
...
    jdbc_driver_library => "/usr/share/java/sqlite-jdbc.jar"
    jdbc_driver_class => "org.sqlite.JDBC"
    jdbc_connection_string => "jdbc:sqlite:/home/user/test.db"
}

Run logstash with the test.config and a test input as logstash user with everything owned and writable by the logstash user.

logstash -f test.config < test_input

Here is a snippet of the error I'm seeing. Can provide more detail as needed.

[INFO ] 2023-04-17 19:00:26.664 [[main]-pipeline-manager] jdbcstatic - derby.system.home is: /usr/share/logstash/data/plugins/shared/derby_home
[ERROR] 2023-04-17 19:00:26.813 [[main]-pipeline-manager] javapipeline - Pipeline error {:pipeline_id=>"main", :exception=>#<LogStash::Filters::Jdbc::ConnectionJdbcException: Java::JavaSql::SQLException: Error opening connection>, :backtrace=>["org.sqlite.SQLiteConnection.open(org/sqlite/SQLiteConnection.java:244)"...

I think I have ruled out a permissions issue and as I've stated already a small java program that uses the sqlite jdbc to connect to this same db works and can query the data in it.

Any help is greatly appreciated.

Dustin527 · April 18, 2023, 2:48pm

Same issue with the JDBC streaming filter as well

Dustin527 · April 18, 2023, 3:22pm

If it helps

logstash java version
/usr/share/logstash/jdk/bin/java --version
openjdk 17.0.6 2023-01-17
OpenJDK Runtime Environment Temurin-17.0.6+10 (build 17.0.6+10)
OpenJDK 64-Bit Server VM Temurin-17.0.6+10 (build 17.0.6+10, mixed mode, sharing)

system java version
java --version
openjdk 11.0.18 2023-01-17
OpenJDK Runtime Environment (build 11.0.18+10-post-Debian-1deb11u1)
OpenJDK 64-Bit Server VM (build 11.0.18+10-post-Debian-1deb11u1, mixed mode, sharing)

Dustin527 · April 18, 2023, 3:30pm

If I compile my java program with the bundled logstash jdk it also give a connection error so it seems this version of the jdk is not compatible with the jdbc filter somehow

leandrojmp · April 18, 2023, 3:48pm

Dustin527:

[ERROR] 2023-04-17 19:00:26.813 [[main]-pipeline-manager] javapipeline - Pipeline error {:pipeline_id=>"main", :exception=>#<LogStash::Filters::Jdbc::ConnectionJdbcException: Java::JavaSql::SQLException: Error opening connection>, :backtrace=>["org.sqlite.SQLiteConnection.open(org/sqlite/SQLiteConnection.java:244)"...

Where is the rest of the error? Please share the entire line.

Dustin527 · April 18, 2023, 4:43pm

The rest of the trace is just the function calls leading up to the open call and doesn't seem all that helpful.

I've actually managed get this working by using export LS_JAVA_HOME=<system java path> to get logstash to use the system jdk at version 11. It seems that the bundled jdk at version 17 is incompatible with the latest version of the xerial jdbc driver that's available with debian bullseye.

Here is the full error anyway:

[ERROR] 2023-04-18 16:39:55.346 [[main]-pipeline-manager] javapipeline - Pipeline error {:pipeline_id=>"main", :exception=>#<LogStash::Filters::Jdbc::ConnectionJdbcException: Java::JavaSql::SQLException: Error opening connection>, :backtrace=>["org.sqlite.SQLiteConnection.open(org/sqlite/SQLiteConnection.java:244)", "org.sqlite.SQLiteConnection.<init>(org/sqlite/SQLiteConnection.java:61)", "org.sqlite.jdbc3.JDBC3Connection.<init>(org/sqlite/jdbc3/JDBC3Connection.java:28)", "org.sqlite.jdbc4.JDBC4Connection.<init>(org/sqlite/jdbc4/JDBC4Connection.java:21)", "org.sqlite.JDBC.createConnection(org/sqlite/JDBC.java:115)", "org.sqlite.JDBC.connect(org/sqlite/JDBC.java:90)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke(jdk/internal/reflect/NativeMethodAccessorImpl.java:77)", "jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(jdk/internal/reflect/DelegatingMethodAccessorImpl.java:43)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:568)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:472)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:330)", "RUBY.connect(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/sequel-5.65.0/lib/sequel/adapters/jdbc.rb:237)", "RUBY.new_connection(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/sequel-5.65.0/lib/sequel/database/connecting.rb:248)", "RUBY.make_new(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/sequel-5.65.0/lib/sequel/connection_pool.rb:154)", "RUBY.assign_connection(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/sequel-5.65.0/lib/sequel/connection_pool/threaded.rb:209)", "RUBY.acquire(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/sequel-5.65.0/lib/sequel/connection_pool/threaded.rb:139)", "RUBY.hold(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/sequel-5.65.0/lib/sequel/connection_pool/threaded.rb:91)", "RUBY.synchronize(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/sequel-5.65.0/lib/sequel/database/connecting.rb:293)", "RUBY.test_connection(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/sequel-5.65.0/lib/sequel/database/connecting.rb:302)", "RUBY.initialize(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/sequel-5.65.0/lib/sequel/database/misc.rb:188)", "org.jruby.RubyClass.new(org/jruby/RubyClass.java:890)", "org.jruby.RubyClass$INVOKER$i$newInstance.call(org/jruby/RubyClass$INVOKER$i$newInstance.gen)", "RUBY.connect(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/sequel-5.65.0/lib/sequel/database/connecting.rb:57)", "RUBY.connect(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/sequel-5.65.0/lib/sequel/core.rb:124)", "RUBY.verify_connection(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/logstash-integration-jdbc-5.4.1/lib/logstash/filters/jdbc/basic_database.rb:107)", "RUBY.post_create(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/logstash-integration-jdbc-5.4.1/lib/logstash/filters/jdbc/read_only_database.rb:48)", "RUBY.create(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/logstash-integration-jdbc-5.4.1/lib/logstash/filters/jdbc/basic_database.rb:33)", "RUBY.build_remote_db(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/logstash-integration-jdbc-5.4.1/lib/logstash/filters/jdbc/loader.rb:18)", "RUBY.prepare_runner(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/logstash-integration-jdbc-5.4.1/lib/logstash/filters/jdbc_static.rb:203)", "org.jruby.RubyArray.collectArray(org/jruby/RubyArray.java:2633)", "org.jruby.RubyArray.map(org/jruby/RubyArray.java:2667)", "org.jruby.RubyArray$INVOKER$i$0$0$map.call(org/jruby/RubyArray$INVOKER$i$0$0$map.gen)", "RUBY.prepare_runner(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/logstash-integration-jdbc-5.4.1/lib/logstash/filters/jdbc_static.rb:200)", "RUBY.register(/usr/share/logstash/vendor/bundle/jruby/2.6.0/gems/logstash-integration-jdbc-5.4.1/lib/logstash/filters/jdbc_static.rb:158)", "org.jruby.RubyClass.finvoke(org/jruby/RubyClass.java:572)", "org.jruby.RubyBasicObject.callMethod(org/jruby/RubyBasicObject.java:348)", "org.logstash.config.ir.compiler.FilterDelegatorExt.doRegister(org/logstash/config/ir/compiler/FilterDelegatorExt.java:89)", "org.logstash.config.ir.compiler.AbstractFilterDelegatorExt.register(org/logstash/config/ir/compiler/AbstractFilterDelegatorExt.java:75)", "org.logstash.config.ir.compiler.AbstractFilterDelegatorExt$INVOKER$i$0$0$register.call(org/logstash/config/ir/compiler/AbstractFilterDelegatorExt$INVOKER$i$0$0$register.gen)", "RUBY.register_plugins(/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:237)", "org.jruby.RubyArray.each(org/jruby/RubyArray.java:1865)", "org.jruby.RubyArray$INVOKER$i$0$0$each.call(org/jruby/RubyArray$INVOKER$i$0$0$each.gen)", "RUBY.register_plugins(/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:236)", "RUBY.maybe_setup_out_plugins(/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:608)", "RUBY.start_workers(/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:249)", "RUBY.run(/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:194)", "RUBY.start(/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:146)", "org.jruby.RubyProc.call(org/jruby/RubyProc.java:309)", "java.lang.Thread.run(java/lang/Thread.java:833)"], "pipeline.sources"=>["/home/dustin.cannon/logstash_test/static-test.config"], :thread=>"#<Thread:0x1d1f53e7@/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:134 run>"}

Dustin527 · April 18, 2023, 4:52pm

So the debian package of libxerial is 3.36 but I see on their releases that jdk 17 isn't mentioned until version 3.39 which has a note on the changelog " 062440a build on jdk 17 (#747)"

I will give this a shot with the latest libxerial sqlite jdbc and see how that goes.

system · May 16, 2023, 4:52pm

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