Logstash Jdbc connection error

Hi, We are running a 3 node elastic cluster to index logs from a firewall.

Filebeat and Redis Queue are used to forward the logs to Logstash for necessary parsing, after which it is indexed in to the Elastic cluster.

Data regarding the ip address and Endusers is maintained in a local mysql database on the Logstash machine for enrichment of firewall logs. Logstash jdbc static filter plugin is used to ingest data from the mysql database to Logstash and further the user detail enrichment of the firewall logs is being done.
The jdbc static filter configuration part in the Logstash configuration file used for enrichment is as follows.

jdbc_static
   {
      loaders =>[
                  {
                    id => "user-details"
                    query => "SELECT user-name, ip_address FROM eusers"
                    local_table => "edata"
                  }
                ]

      local_db_objects => [
                          {
                            name => "edata"
                            index_columns => ["ip_address"]
                            columns =>[
                                        ["name","varchar(100)"]
                                        ["ip_address","varchar(15)"]
                                      ]
                          }
                        ]
      local_lookups => [
                        {
                          id => "local-edata"
                          query => "SELECT name from edata where ip_address = ?"
                          prepared_parameters => ["[srcip]"]
                          target => "user"
                        }
                      ]
      add_field => { user_name => "%{[user][0][name]}"}
      remove_field => ["user"]
      staging_directory => "/tmp/logstash/jdbc_static/import_data"
      loader_schedule => "*/2 * * * *" # run loaders every half hours
      jdbc_user => "end-users"
      jdbc_password => "**********"
      jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
      jdbc_driver_library => "/usr/share/logstash/logstash-core/lib/jars/mysql-connector-j-8.0.32.jar"
      jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/enduserdb"

However, for past few days the end user enrichment has stopped working and while logstash service is started the following error is thrown.

2024-03-28T10:12:17,089][ERROR][logstash.javapipeline    ][main] Pipeline error {:pipeline_id=>"main", :exception=>#<LogStash::Filters::Jdbc::ConnectionJdbcException: org.apache.derby.jdbc.EmbeddedDriver not loaded>, :backtrace=>["/usr/share/logstash/vendor/bundle/jruby/3.1.0/gems/sequel-5.75.0/lib/sequel/adapters/jdbc.rb:66:in `load_driver'", "/usr/share/logstash/vendor/bundle/jruby/3.1.0/gems/logstash-integration-jdbc-5.4.8/lib/logstash/filters/jdbc/basic_database.rb:98:in `verify_connection'", "/usr/share/logstash/vendor/bundle/jruby/3.1.0/gems/logstash-integration-jdbc-5.4.8/lib/logstash/filters/jdbc/read_write_database.rb:58:in `post_create'", "/usr/share/logstash/vendor/bundle/jruby/3.1.0/gems/logstash-integration-jdbc-5.4.8/lib/logstash/filters/jdbc/basic_database.rb:33:in `create'", "/usr/share/logstash/vendor/bundle/jruby/3.1.0/gems/logstash-integration-jdbc-5.4.8/lib/logstash/filters/jdbc/lookup_processor.rb:36:in `initialize'", "org/jruby/RubyClass.java:917:in `new'", "/usr/share/logstash/vendor/bundle/jruby/3.1.0/gems/logstash-integration-jdbc-5.4.8/lib/logstash/filters/jdbc_static.rb:207:in `prepare_runner'", "/usr/share/logstash/vendor/bundle/jruby/3.1.0/gems/logstash-integration-jdbc-5.4.8/lib/logstash/filters/jdbc_static.rb:158:in `register'", "org/logstash/config/ir/compiler/AbstractFilterDelegatorExt.java:75:in `register'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:237:in `block in register_plugins'", "org/jruby/RubyArray.java:1989:in `each'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:236:in `register_plugins'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:611:in `maybe_setup_out_plugins'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:249:in `start_workers'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:194:in `run'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:146:in `block in start'"], "pipeline.sources"=>["/etc/logstash/conf.d/fortigate.conf"], :thread=>"#<Thread:0xb681932 /usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:134 run>"}
[2024-03-28T10:12:17,091][INFO ][logstash.javapipeline    ][main] Pipeline terminated {"pipeline.id"=>"main"}
[2024-03-28T10:12:17,099][ERROR][logstash.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}
[2024-03-28T10:12:17,109][INFO ][logstash.runner          ] Logstash shut down.
[2024-03-28T10:12:17,116][FATAL][org.logstash.Logstash    ] Logstash stopped processing because of an error: (SystemExit) exit
org.jruby.exceptions.SystemExit: (SystemExit) exit
	at org.jruby.RubyKernel.exit(org/jruby/RubyKernel.java:808) ~[jruby.jar:?]
	at org.jruby.RubyKernel.exit(org/jruby/RubyKernel.java:767) ~[jruby.jar:?]
	at usr.share.logstash.lib.bootstrap.environment.<main>(/usr/share/logstash/lib/bootstrap/environment.rb:90) ~[?:?]


We have tried replacing the jar file with the latest available jar file from the mysql website.

Please let us know how this can be solved?

Thank you.

I'm not sure if it's related to the MySQL part because the first line of what you shared shows:

ConnectionJdbcException: org.apache.derby.jdbc.EmbeddedDriver not loaded

Which is related to me to the internal database used for local lookups.

That looks a lot like this issue. The fix was committed a couple of days ago, so it is probably not in any release. Downgrade to 8.12.1?

Hi, Thanks for your response. The error has been resolved after upgrade of elastic and logstash to latest version i.e 8.13.

Hi, Thank you for your response. The error has been resolved after upgrade of elastic and logstash to latest version i.e 8.13.