No connection pool for 'ActiveRecord::Base' found for the 'reading' role.

[Gokul-Edcast]

We are implementing multi-db on our app with help of ros-apartment gem. In our application we are using primary db as well as replica db for reading. We are switching write db with help of ros-apartment gem(_Apartment::Tenant.switch!(tenantname)) and using read db with help of ActiveRecord::Base.connects_to(database: {reading: replica_db.to_sym }). Loading the read replica db config dynamically using an initialiser file.

app/config/initializers/db_config_load.rb

Rails.application.config.to_prepare do
   multi_db_enable = PropertyStore.get_variable('MULTI_DB_ENABLED')
   multi_db_enable = multi_db_enable.present? ? multi_db_enable.to_bool : false
   if !Rails.env.test? && multi_db_enable
    result_db_config = DsDetail.all.each_with_object({}) do |db_detail, db_hash|
      next if db_detail.blank?

     db_hash[db_detail.replica_db.to_sym] = {
        "adapter" => "mysql2",
        "database" => "#{db_detail.try(:replica_db)}",
        "username" => "#{db_detail.try(:replica_username)}",
        "password" => "#{db_detail.decrypt_password(db_detail.replica_password)}",
        "host" => "#{db_detail.try(:replica_db_url)}",
        "port" => db_detail.try(:replica_port),
        "pool" => db_detail.try(:replica_db_pool) || ENV['DB_POOL'] || ENV['MAX_THREADS'] || 5,
        "checkout_timeout" => ENV['DB_CHECKOUT_TIMEOUT'] || 5,
        "reaping_frequency" => db_detail.try(:reaping_frequency) || ENV['DB_REAP_FREQ'] || 10,
        "replica" => true,
        "reconnect" => true,
        "role" => "reading" 

      }
    end
    new_config = Rails.application.config.database_configuration.merge(result_db_config)
    ActiveRecord::Base.configurations = ActiveRecord::DatabaseConfigurations.new(new_config)

    result_db_config.keys.each do |db_key|
      ActiveRecord::Base.connects_to(database: { reading: db_key })
      puts "#{db_key} Connection Pool: #{ActiveRecord::Base.connection_pool.stat}"
      puts "#{db_key} Connection Pool: #{ActiveRecord::Base.connection_pool.connected?}"
    end
  end
rescue StandardError => e
  Rails.logger.error("DbConfigLoad::Error: #{e.message}")
end

`

In some place in our code we are using reading role to read data from read replica. ApplicatioRecord.rb connects_to database: { writing: :primary, reading: :primary_replica }

Connection Pool for the replica_db is 25 When we hit multiple time on the api's we got the below error No connection pool for 'ActiveRecord::Base' found for the 'reading' role. In our mysql rds instance we have around 650 connections and it is using only 190 pools but still we got the connection pool error.

Guys any idea why the connection pool error, is it because of db config issue? or ros-aprtment tenant switch using all the connection pool? How ros-apartment gem is handling connection pool in rails?

System configuration

• Database: mysql Ver 14.14 Distrib 5.7.44, for osx10.17 (x86_64) using EditLine wrapper

• Apartment version: ros-apartment (2.11.0)

• Apartment config (in config/initializers/apartment.rb or so):

  • use_schemas: (true or false)

• Rails (or ActiveRecord) version: Rails. 7.0

• Ruby version: Ruby : 3.0.6

[mnovelo]

Thanks for your report! I haven't read through this in-depth, but I know that we use a read-replica Postgres along with our Postgres primary without issue in Apartment. I'm attending a conference this week so it may be some time before I can take a closer look. @npezza93 or @mrpasquini do you have any thoughts?

[Gokul-Edcast]

Thanks @mnovelo for the response, This is happening when we switch replica database multiple times.

def switch_to_tenant(tenant_name, replica_db = nil)
     Apartment::Tenant.switch!(tenant_name)
     if tenant_name.present? && replica_db.present? && RequestStore.read(:read_replica_enabled)
          ActiveRecord::Base.connects_to(database: {reading: replica_db.to_sym })
     end
 end

This is the method we used to switch the primary as well as replica database ActiveRecord::Base.connects_to(database: {reading: replica_db.to_sym })

This line is causing the issue.

[mnovelo]

Interesting. I'm not as familiar with the MySQL implementation, but I know for Postgres it's more optimal to do the tenant switch after the db switch because the tenant switch issues a SET search_path= on the current connection, so it's needless work for us to switch tenants on the current connection if we're switching databases on the next line. Have you tried moving the Apartment::Tenant.switch!(tenant_name) to be after your db switch?

[sunil-sharma]

Can you share your Sample Postgres code here?

[mnovelo]

@sunil-sharma what do you mean?