Stuck connections after period of idle

[Kevin Yeung]

Hi all

Thank you for your attention. We got a bit stuck with stuck connections with after some idle period and would appreciate any help or things to try.

We're using v1.4.1 of activerecord-oracle_enhanced-adapter with ojdbc6.jar in a Sinatra app on jruby 1.7.2. My controller calls a helper class method and returns a json string. The helper class method simply queries an Oracle database for data. The code looks like this:

class App < Sinatra::Base

  get '/customer/:id_number' do |id_number|

    MyHelper.customer(id_number).to_json

  end

end

class MyHelper

  @dbconfig = YAML.load_file("../config/database.yml")

  @dbenv = @dbconfig["#{settings.environment}"]

  secure = Security3DES.new

  key = secure.load_key

  iv = secure.load_iv

  @dbenv["password"] = secure.decrypt(@dbenv["password"], key, iv)

  puts "establish_connection"

  ActiveRecord::Base.establish_connection(@dbenv)

  def self.customer(id_number)

    begin

      query = <<-SQL

        SELECT * FROM customer WHERE cust_id = '#{id_number}'

      SQL

      puts 1

      ActiveRecord::Base.connection.exec_query(query)

      puts 2

    rescue => e

      puts 3

      puts e.message

    ensure

      puts 4

      ActiveRecord::Base.clear_active_connections!

      puts 5

    end

   end

end

We have a shell script that hits the controller 50 times, sleeps for 2 hours and repeats. We can see one call to establish_connection at the beginning (expected). What we don't expect is that right after the sleep, the first connection to the database always gets stuck. What we'd see in the log is just "1". After 15 minutes, the client times out and makes another call to the controller. This second call seems to have the effect of "waking up" the connection pool, in that it always resumes thread 1 and so we'd see "2", "4" and "5". Concurrently, we'd also see thread 2's log messages: "1", "2", "4" and "5". We know because the actual code prints Thread.current.object_id as part of the message.

We tried adding ActiveRecord::Base.connection.verify! right before exec_query but that only shifts the stuck call to the verify line.

Did we use the adapter correctly? Is there something else we can try?

Thanks

Kevin Yeung