Logstash 2.3.3 not closing JDBC connections

[Kirangurrala]

Please post all product and debugging questions on our forum. Your questions will reach our wider community members there, and if we confirm that there is a bug, then we can open a new issue here.

Issue: JDBC is never closing the connection from the database(Oracle), it opened for running the sql request.

For all general issues, please provide the following details for fast resolution:

• Version: 2.3.3

• Operating System: Linux

• Config File (if you have sensitive info, please remove it):
  input {
  jdbc {
  # Connection credentials
  jdbc_connection_string => "jdbc:oracle:thin:@host:PORT/"
  jdbc_user => ""
  jdbc_password => ""
  jdbc_driver_library => "$HOME/logstash-2.3.3/db/ojdbc6-11.2.0.4.jar"
  jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"

  #Enable Pagination - This will cause a sql statement to be broken up into multiple queries
  #                  - Each query will use limits and offsets to collectively retrieve the full result-set
  jdbc_paging_enabled => "true"
  jdbc_page_size => "500"
  
  jdbc_validation_timeout => "240"
  
  sequel_opts => {
                 login_timeout    => "60"
                 }
  
  #Schedule of when to periodically run statement, in Cron format for example: "* * * * *"
  schedule => "*/5 * * * *"
  
  # SQL monitoring Statement
  statement => "select col1,col2,start_date from test_logstash where active = 1 and start_date > :sql_last_value"
  }
  

  }
  output {
  stdout {}
  }

• Sample Data:
  COL1 COL2 START_DATE ACTIVE
  502 503 29-JUN-16 02.58.52 PM 1
  311 312 27-JUN-16 04.17.54 PM 1

• Steps to Reproduce:

• Create a table test_logstash with 4columns - COL1, COL2, START_DATE, ACTIVE

• Inserting the data above into the table

• Then using the above conf statement, start the logstash.

• JDBC will connect to oracle and the JDBC user LOGON time on oracle will never change for every run of the above sql statement (which means that JDBC is not disconnecting the previously opened connection)

[dikmanwork]

I have same problem in MYSQL. version 2.4

[mrjameshamilton]

This is a big problem. When logstash starts it opens the connection, even if the scheduling is used. I have ~500 SQL queries scheduled and when logstash starts it opens ~500 connections, even though it doesn't need to open them yet. And it never closes them. There should be a pool of a few connections to share; or even open and close the connection for each query that is run.

[jordansissel]

Leaking connections is definitely not behavior we want the plugin to have. Thank you for reporting.

[tkvedaras]

+1 The same

[Kirangurrala]

Guys, We have found a solution to this problem. We somehow managed to slightly change the jdbc plugins source code in order to achieve what we want. Will share the fix what we have done, shortly with you. Hope it helps everyone.

[achennuru]

Hi @Kirangurrala , Could you please share your fix?
It would help me a lot. Thanks in advance.

[mrjameshamilton]

Are there any updates on this issue?

[mrjameshamilton]

Hi, I submitted a pull request to fix this. Thanks.

[jakelandis]

The fix for this has been released:

bin/logstash-plugin install --version "4.3.0" logstash-input-jdbc

[lonelywolf4git]

The fix for this has been released:

bin/logstash-plugin install --version "4.3.0" logstash-input-jdbc

I have the same issue with Logstash 7.2 version when sync mongo to Eslastic. it will open new connection for each schedule.

[2019-07-12T09:34:00,216][INFO ][org.mongodb.driver.connection] Opened connection [connectionId{localValue:53, serverValue:197}] to 192.168.99.34:27017
[2019-07-12T09:34:00,218][INFO ][org.mongodb.driver.cluster] Monitor thread successfully connected to server with description ServerDescription{address=192.168.99.34:27017, type=STANDALONE, state=CONNECTED, ok=true, version=ServerVersion{versionList=[4, 0, 4]}, minWireVersion=0, maxWireVersion=7, maxDocumentSize=16777216, logicalSessionTimeoutMinutes=30, roundTripTimeNanos=793800}
Warning: Nashorn engine is planned to be removed from a future JDK release
[2019-07-12T09:34:00,236][INFO ][org.mongodb.driver.connection] Opened connection [connectionId{localValue:54, serverValue:198}] to 192.168.99.34:27017
[2019-07-12T09:34:30,130][INFO ][org.mongodb.driver.cluster] Cluster created with settings {hosts=[192.168.99.34:27017], mode=SINGLE, requiredClusterType=UNKNOWN, serverSelectionTimeout='30000 ms', maxWaitQueueSize=500}
Warning: Nashorn engine is planned to be removed from a future JDK release
[2019-07-12T09:34:30,136][INFO ][org.mongodb.driver.connection] Opened connection [connectionId{localValue:55, serverValue:199}] to 192.168.99.34:27017
[2019-07-12T09:34:30,137][INFO ][org.mongodb.driver.cluster] Monitor thread successfully connected to server with description ServerDescription{address=192.168.99.34:27017, type=STANDALONE, state=CONNECTED, ok=true, version=ServerVersion{versionList=[4, 0, 4]}, minWireVersion=0, maxWireVersion=7, maxDocumentSize=16777216, logicalSessionTimeoutMinutes=30, roundTripTimeNanos=648100}
[2019-07-12T09:34:30,145][INFO ][org.mongodb.driver.connection] Opened connection [connectionId{localValue:56, serverValue:200}] to 192.168.99.34:27017

So I use the command " netstat -an|grep 27017|wc -l" to check connection number, and found the number is incremented for each schedule time.
Any suggestion? thanks!