SQLRecoverableException: ORA-17008: Closed connection

Elastic Stack Logstash
1

Hello,

Since two weeks we have problem that from time to time our logstash pipeline which is connecting to oracle databse with jdbc_streaming filter is throwing, exception=>#<Sequel::DatabaseDisconnectError: Java::JavaSql::SQLRecoverableException: ORA-17008: Closed connection

 jdbc_streaming {
      jdbc_driver_library => "/usr/share/logstash/config/bin/ojdbc11.jar"
      jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
      jdbc_user => "${ls_prod_user}"
      jdbc_password => "${ls_prod_pass}"
      jdbc_connection_string => "jdbc:oracle:thin:@//prodhost.back.dom:1533/prod.dom" 
      jdbc_validate_connection => true
      jdbc_validation_timeout => 300
      sequel_opts => {
        max_connections => 4
        pool_timeout => 180
      }
      parameters => {
        "store" => "%{store}"
        "seq" => "%{_seq}"
        "converteddate" => "%{converted_date}"
        "wkst" => "%{wkst}"
      }
      statement => "our query"
      target => "data"
    }

On network level connection is possible netcat and telnet are working fine. Our workaround is to restart container where logstash is running, after restart the connection is possible and logstash is processing data without problem.

Did somebody have similar problem and found some solution to automatically refresh/reestablish connection to db?

2

Hi @Witcherek , welcome to our community.

Have you checked if the Oracle database is logging anything about these closed connections in the alert logs?

Thanks

3

Hello @Alex_Salgado-Elastic

From DB guys it seems that they are restarting database on weekends, in logstash logs the time is very similar to our problem with connection.

4

The filter expects the sequel library to manage the connection pool. There was a PR earlier this year to bump the library dependency to make sure this particular exception is handled by it.

/usr/share/logstash/vendor/bundle/jruby/3.1.0/gems/logstash-integration-jdbc-5.5.3/logstash-integration-jdbc.gemspec should include s.add_runtime_dependency 'sequel', '>= 5.74.0'. If not, you may be able to fix this by upgrading logstash.