Logstash not closing jdbc connection

[sabbeggar]

Logstash information:

Please include the following information:

1. Logstash version (e.g. bin/logstash --version) : 8.8.2
2. Logstash installation source (e.g. built from source, with a package manager: DEB/RPM, expanded from tar or zip archive, docker)
3. How is Logstash being run (e.g. as a service/service manager: systemd, upstart, etc. Via command line, docker/kubernetes) : comman line
4. How was the Logstash Plugin installed : pipeline

JVM (e.g. java -version): openJDK 11

If the affected version of Logstash is 7.9 (or earlier), or if it is NOT using the bundled JDK or using the 'no-jdk' version in 7.10 (or higher), please provide the following information:

1. JVM version (java -version) java version "1.8.0_271" Java(TM) SE Runtime Environment (build 1.8.0_271-b37) Java HotSpot(TM) 64-Bit Server VM (build 25.271-b37, mixed mode)

2. JVM installation source (e.g. from the Operating System's package manager, from source, etc). :

3. Value of the JAVA_HOME environment variable if set : jdk1.8.0_271

OS version (uname -a if on a Unix-like system): SunOS 5.11 11.4.41.107.2 i86pc i386 i86pc vmware

Description of the problem including expected versus actual behavior: Connections to database are never closed: currently every query in the config creates a new connection that is never closed. Problem for us, as with have ~160 scheduled queries in the config. So we constantly have ~160 connections open on the database.

Steps to reproduce:

Please include a minimal but complete recreation of the problem, including (e.g.) pipeline definition(s), settings, locale, etc. The easier you make for us to reproduce it, the more likely that somebody will take the time to look at it.

#Session username, id, elasped_time_minutes, status
jdbc {
    type => "bddquery"
    jdbc_driver_library => "/users/sth00/data/ojdbc8.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    jdbc_connection_string => "jdbc:oracle:thin:@abcdefgh:1521/my_db"
    id => "bdd_query_10"
    last_run_metadata_path => "/users/sth00/data/.logstash_jdbc_last_run_abcdefgh_my_db_bdd_query_10"
    record_last_run => true
    jdbc_validate_connection => true
    jdbc_user => "LOGSTASH"
    jdbc_password => "LOGSTASH_PSSWORD"
    schedule => "10,40 * * * *"
    statement => "SELECT username session_username, sid session_id, round(last_call_et/60, 2) as session_elapsed_time_minutes, status session_status from v$session"
    add_field => {"query_type" => "oracle_sessions"}
    add_field => {"host" => "abcdefgh"}
    add_field => { "TIMESTAMP" => "%{@timestamp}" }
    sequel_opts => {
        max_connections => 4
    }
}

1.

2. tested with ojdbc6.jar and ojdbc8.jar => same issue

3. 

https://github.com/logstash-plugins/logstash-integration-jdbc/blob/main/lib/logstash/plugin_mixins/jdbc/jdbc.rb on execute_statement, there should lines to open/close connections only when needed

Provide logs (if relevant):

[andsel]

Hi @sabbeggar thanks for reporting this but I think that's expected. JDBC input leverages a connection pool, to avoid continuously open and close connections to the database, which is a costly operation. So it keeps the connection open in a pool.