config.use_schemas = false with MySql causes intermittent incorrect db selection

[michaeljon]

Steps to reproduce

One Tenant database on MySql
Multiple tenant-specific databases on different MySql servers (can all be reproduced on localhost)
Run multiple curl requests to query a tenant-specific table and return a single row. In our case we're simply setting a request HTTP header to match what's in the apartment config and running 4 in parallel (one against each tenant)

config.use_schemas = false causes this to repro consistently, we have considered config.use_schemas = true but need to spread our tenants across many servers

export RAILS_MIN_THREADS=1
export RAILS_MAX_THREADS=5
export WEB_CONCURRENCY=0

The controller code:

  def show
    tenant_bird = TenantBird.find_by name: params[:name]
    sleep_time = rand(250..3000) / 1000.0
    # the sleep here is to sim. long-running queries
    Rails.logger.debug "#{Process.ppid}.#{Process.pid} #{Thread.current['x-tenant-id']} Will sleep for #{sleep_time} seconds"
    ActiveRecord::Base.connection.exec_query("select sleep(#{sleep_time})")
    render json: { slept: sleep_time, bird: tenant_bird, process: { pid: Process.pid, ppid: Process.ppid } }
  end

default: &default
  adapter: mysql2
  encoding: utf8
  timeout: 30_000
  reconnect: false
  pool: 100
  username: root
  password: password
  host: localhost
  database: router

development:
  <<: *default

Expected behavior

Host and database is properly selected and configured for the request.

Actual behavior

Occasional

Apartment::TenantNotFound (Error while connecting to tenant t_103: No connection pool with 'primary' found.):
  
ros-apartment (2.10.0) lib/apartment/adapters/abstract_adapter.rb:268:in `raise_connect_error!'
ros-apartment (2.10.0) lib/apartment/adapters/abstract_adapter.rb:189:in `rescue in connect_to_new'
ros-apartment (2.10.0) lib/apartment/adapters/abstract_adapter.rb:178:in `connect_to_new'
ros-apartment (2.10.0) lib/apartment/adapters/abstract_adapter.rb:76:in `block in switch!'
activesupport (5.2.6) lib/active_support/callbacks.rb:98:in `run_callbacks'
ros-apartment (2.10.0) lib/apartment/adapters/abstract_adapter.rb:75:in `switch!'
ros-apartment (2.10.0) lib/apartment/adapters/abstract_adapter.rb:88:in `switch'

Now and again we'll see issues connecting to the default / Tenant database too

ActiveRecord::ConnectionNotEstablished (No connection pool with 'Tenant' found.):
  ↳ config/initializers/apartment.rb:42
  ↳ /Users/michaeljon/.rvm/gems/ruby-2.6.7@mw/gems/activerecord-5.2.6/lib/active_record/log_subscriber.rb:98
  
  
ActiveRecord::ConnectionNotEstablished (No connection pool with 'Tenant' found.):
config/initializers/apartment.rb:42:in `block in <main>'
Looking for tenant by organization_id 102```

## System configuration

* Database: (Tell us what database and its version you use.) MySql 8.0
* Apartment version: 2.10.0
* Apartment config (in `config/initializers/apartment.rb` or so):

```ruby
# frozen_string_literal: true

require 'apartment/elevators/generic'

Apartment.configure do |config|
  config.excluded_models = %w[Tenant]

  config.tenant_names = lambda {
    names = {}

    tenants = Tenant.all.each do |t|
      names[t.name] ||= {
        adapter: 'mysql2',
        encoding: 'utf8',
        pool: 100,
        timeout: 30_000,
        reconnect: false,
        username: 'root',
        password: 'password',
        host: t.host,
        database: t.db
      }
    end

    names
  }

  config.use_schemas = false
  config.prepend_environment = false
  config.active_record_log = false
end

Rails.application.config.middleware.use Apartment::Elevators::Generic, lambda { |request|
  tenant_id = request.cookies['tenant_id']

  if tenant_id.nil?
    Rails.logger.debug 'Request made without tenant_id'
    return nil
  end

  Rails.logger.debug "Looking for tenant by organization_id #{tenant_id}"
  tenant = Tenant.find_by organization_id: tenant_id

  if tenant.nil?
    Rails.logger.debug "Unable to locate tenant by organization_id #{tenant_id}"
    return nil
  end

  Rails.logger.debug "Found tenant #{tenant_id} named #{tenant.name} at #{tenant.host}.#{tenant.db}"
  tenant.name
}

• Rails (or ActiveRecord) version: 5.2.6

• Ruby version: 2.6.7

[rpbaltazar]

Hi @michaeljon ,

thank you for reporting. we don't actively use the mysql adapter so it's harder for me to identify these issues.
Since you have a very clear failing scenario described, could i ask you to create a repository with that failing scenario and how to reproduce the error in that repo? I could then try to help figuring out where the source of the problem is