ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool within 5.000 seconds. all pooled connections were in use

Question

I am trying to use multiple databases in single rails application. I have created a model ReportUser to access report_users table defined in different database which has been specified in config/support_database.yml . Normal models activeRecord queries are working, but ReportUser.count is not working. I managed to create table and migrations, but when I am trying to access model with secondary database using active record queries then, I am getting connection timeout error from second database.

Here are few things which I followed.

Created a different config/database_support.yml file

default: &default
adapter: postgresql
encoding: unicode
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: sm_development

test:
  <<: *default
  database: sm_test

config/initializers/general.rb

SUPPORT_DB = YAML.load_file(File.join(Rails.root, "config", "database_support.yml"))[Rails.env.to_s]

app/models/support_base.rb

class SupportBase < ActiveRecord::Base 
  self.abstract_class = true 
  establish_connection(SUPPORT_DB)
end

app/models/report_user.rb

class ReportUser < SupportBase
end

db_support/migrate/2017_create_report_users.rb

class CreateReportUsers < ActiveRecord::Migration[5.0]
  def change
    create_table :report_users do |t|
      t.string :first_name
      t.string :last_name
      t.string :email
    end
  end
end

lib/tasks/databases.rake

namespace :support do
  desc "Configure the variables that rails need in order to look up for the db configuration in a different folder"
  task :set_custom_db_config_paths do
    ENV['SCHEMA'] = 'db_support/schema.rb'
    Rails.application.config.paths['db'] = ['db_support']
    Rails.application.config.paths['db/migrate'] = ['db_support/migrate']
    Rails.application.config.paths['db/seeds.rb'] = ['db_support/seeds.rb']
    Rails.application.config.paths['config/database'] = ['config/database_support.yml']
  end

  namespace :db do
    task :create => :set_custom_db_config_paths do
        Rake::Task["db:create"].invoke
    end
    task :migrate => :set_custom_db_config_paths do
        Rake::Task["db:migrate"].invoke
    end
    task :rollback => :set_custom_db_config_paths do
        Rake::Task["db:rollback"].invoke
    end
  end

  namespace :test do
    task :check => [:environment] do
        puts "users count = #{User.count}"
        puts "report_users = #{ReportUser.count}"
    end
  end
end

After configuring all this and running last rake task rake support:test:check, I am getting this error

ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool within 5.000 seconds (waited 5.001 seconds); all pooled connections were in use
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:202:in `block in wait_poll'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:193:in `loop'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:193:in `wait_poll'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:154:in `internal_poll'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:278:in `internal_poll'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:148:in `block in poll'
from /Users/harsh/.rvm/rubies/ruby-2.3.1/lib/ruby/2.3.0/monitor.rb:214:in `mon_synchronize'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:158:in `synchronize'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:148:in `poll'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:709:in `acquire_connection'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:501:in `checkout'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:364:in `connection'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:875:in `retrieve_connection'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_handling.rb:128:in `retrieve_connection'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/connection_handling.rb:91:in `connection'
from /Users/harsh/.rvm/gems/ruby-2.3.1@rails5.0/gems/activerecord-5.0.0.1/lib/active_record/model_schema.rb:354:in `load_schema!'

puts "users count = #{User.count}" is working fine, But puts "report_users = #{ReportUser.count}" giving connection timeout error.

My Question is How to use `ReportUser.count` in rails console. Because when I am executing this `ReportUser.count` I am getting the database connection error. ReportUser is a model which has table in different database which is mentioned in database_support.yml file. This database is different from the default database which is usually present in database.yml. I hope this clears the question. — Harshvardhan Parihar, May 21 '17 at 18:08
This is working in rails 4.2. I think there is some issue of connections in rails 5.0. Does anyone have idea about this? — Harshvardhan Parihar, May 24 '17 at 17:51

score -2 · Answer 1 · answered May 27 '17 at 12:57

-2

Finally I have found the solution. Just Change config/database_support.yml

default: &default
adapter: postgresql
encoding: unicode
pool: 5

development:
  <<: *default
  database: sm_development

test:
  <<: *default
  database: sm_test

answered May 27 '17 at 12:57

Harshvardhan Parihar

27
2
3

1

So all you changed was to stop reading the pool number from the environment? Any idea why this helped? Also, if you weren't setting `RAILS_MAX_THREADS` in the environment, the result would be identical... – Nathan Wallace Aug 05 '20 at 14:47
Most likely because they does not parse the erb in the yml file. – mollerhoj Jan 07 '22 at 16:07