adapter/jdbc does not reconnect when DB2 "SqlNonTransientConnectionException" is raised

[edmocosta]

Complete Description of Issue

Sequel is not reconnecting when a JDBC DB2SqlNonTransientConnectionException is raised. Instead, it maintains the stale connection in the pool, trying to reuse it indeterminately.

The problem seems to be on the adapters/jdbc#disconnect_error? method. This specific exception is not being wrapped into another exception, letting the :cause nil.

Changing it to the following solved the problem, as the actual disconnection error is thrown and is available on the exception variable.

-cause = exception.respond_to?(:cause) ? exception.cause : exception
+cause = exception.respond_to?(:cause) && exception.cause ? exception.cause : exception

I didn't test it using other JDBC drivers, but I think it might be affecting others as well, depending on how those exceptions are being raised.

The :connection_validator extension can be used as a workaround, but I believe those disconnections should be handled by default.

Steps to reproduce
1 - Run the example code
2- While running, kill the DB connection

Thank you!

Simplest Possible Self-Contained Example Showing the Bug

require 'java'
require 'logger'
require 'sequel'
require 'sequel/adapters/jdbc'
require 'jcc-11.5.7.0.jar'

logger = Logger.new(STDOUT)
logger.level = 'DEBUG'

options_hash = {
  :user => '<user>',
  :password => '<password>',
  :pool_timeout => 10,
  :keep_reference => false,
  :driver => ::Sequel::JDBC.load_driver('com.ibm.db2.jcc.DB2Driver'),
  :logger => logger
}

db = ::Sequel.connect("jdbc:db2://localhost:50000/testdb", options_hash)

loop do
  begin
    db['SELECT id FROM TEST.MY_TABLE'].each { |row| puts row }
    sleep 1
  rescue => e
    logger.error e.message
  end
end

Full Backtrace of Exception (if any)

No response

SQL Log (if any)

No response

Ruby Version

No response

Sequel Version

5.71.0

[jeremyevans]

Thanks for the report. I'll look into this right away.

[jeremyevans]

I pushed a fix, can you test the master branch and confirm whether it fixes the issue for you? I didn't make a change to the generic jdbc adapter, because I found this issue occurs both for the ibmdb adapter and the jdbc/db2, so I handled it in the shared DB2 support. I think the change you are proposing for the generic jdbc adapter makes sense though, but I'll probably wait on that until next week.

[edmocosta]

Hi @jeremyevans!

Thank you very much for your quick response and fix!
I've tested it locally using the master branch and the problem was solved. Sequel successfully detected the database disconnection and discarded the stale connection from the pool :)