ruby-on-railspostgresqlrails-migrationsruby-on-rails-6multiple-databases

Rails 6 Migration giving strange error after using multiple databases


I'm using Two databases in my project using default rails 6 support.

database.yml:-

default: &default
  adapter: postgresql
  migrations_paths: "db/migrate/primary"
  encoding: unicode
  min_messages: warning
  username: ########
  password: ########
  pool: 5
  timeout: 5000
  host_name: localhost

development:
  primary:
    <<: *default
    database: primary_db
  hrm:
    <<: *default
    database: hrm_db
    migrations_paths: "db/migrate/hrm"

Model'l of Secondary database are like:

class HRM::ServiceHistory < ApplicationRecord
  connects_to database: { writing: :hrm, reading: :hrm }

  belongs_to :employee
  belongs_to :designation
.
.

Migration file:-

class ModifyDesigs < ActiveRecord::Migration[6.0]
  def change
    add_column :service_histories, :basic_scale, :integer
    remove_column :employees, :designation_id

    add_column :employees, :current_position_id, :integer

    HRM::ServiceHistory.reset_column_information
    HRM::Employee.reset_column_information
    puts "\n\n=================== #{HRM::Employee.first.name} \n\n"

    HRM::Employee.all.each do |emp|
        emp.service_histories.each do |sh|
            des = sh.designation
            sh.basic_scale = des.basic_scale
            sh.save!
        end
        sh = emp.service_histories.order('appointment_date desc').limit(1).first
        if sh
            emp.current_position = sh
            emp.save!
        end
    end
  end
end

All normal DDL statements are running file, even HRM::Employee.first.name doesn't raise error but further statements raise errors:

== 20200409162158 ModifyDesigs: migrating =====================================
-- add_column(:service_histories, :basic_scale, :integer)    -> 0.0041s
-- add_column(:service_histories, :scale, :integer)    -> 0.0005s
-- add_column(:service_histories, :scale_type, :string, {:limit=>1})    -> 0.0005s
-- remove_column(:employees, :designation_id)    -> 0.0050s
-- add_column(:employees, :current_position_id, :integer)    -> 0.0004s
-- add_index(:employees, :current_position_id)    -> 0.0114s
-- add_foreign_key(:employees, :service_histories, {:column=>:current_position_id})    -> 0.0044s


=================== Employee Name

rails aborted!
ActiveRecord::StatementInvalid: PG::ConnectionBad: connection is closed
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:65:in `exec'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:65:in `block (2 levels) in query'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:64:in `block in query'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:718:in `block (2 levels) in log'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:717:in `block in log'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:708:in `log'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:63:in `query'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/abstract/database_statements.rb:98:in `query_value'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:383:in `release_advisory_lock'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/migration.rb:1384:in `ensure in with_advisory_lock'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/migration.rb:1385:in `with_advisory_lock'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/migration.rb:1229:in `migrate'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/migration.rb:1061:in `up'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/migration.rb:1036:in `migrate'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/tasks/database_tasks.rb:238:in `migrate'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/railties/databases.rake:114:in `block (4 levels) in <top (required)>'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/railties-6.0.2.1/lib/rails/commands/rake/rake_command.rb:23:in `block in perform'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/railties-6.0.2.1/lib/rails/commands/rake/rake_command.rb:20:in `perform'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/railties-6.0.2.1/lib/rails/command.rb:48:in `invoke'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/railties-6.0.2.1/lib/rails/commands.rb:18:in `<top (required)>'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/bootsnap-1.4.5/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:22:in `require'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/bootsnap-1.4.5/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:22:in `block in require_with_bootsnap_lfi'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/bootsnap-1.4.5/lib/bootsnap/load_path_cache/loaded_features_index.rb:92:in `register'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/bootsnap-1.4.5/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:21:in `require_with_bootsnap_lfi'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/bootsnap-1.4.5/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:30:in `require'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/dependencies.rb:325:in `block in require'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/dependencies.rb:291:in `load_dependency'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/dependencies.rb:325:in `require'...


Caused by:
PG::ConnectionBad: connection is closed
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:65:in `exec'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:65:in `block (2 levels) in query'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'...
.
.
Caused by:
StandardError: An error has occurred, this and all later migrations canceled:

PG::ConnectionBad: connection is closed
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:92:in `exec'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:92:in `block (2 levels) in execute'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'...
.
.
Caused by:
ActiveRecord::StatementInvalid: PG::ConnectionBad: connection is closed
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:92:in `exec'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:92:in `block (2 levels) in execute'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'...
.
.
Caused by:
PG::ConnectionBad: connection is closed
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:92:in `exec'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:92:in `block (2 levels) in execute'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activesupport-6.0.2.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'...
.
.
Caused by:
NoMethodError: undefined method `basic_scale=' for #<HRM::ServiceHistory:0x000000000f3b7c98>
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activemodel-6.0.2.1/lib/active_model/attribute_methods.rb:431:in `method_missing'
/var/www/dris/releases/315/db/migrate/hrm/20200409162158_modify_desigs.rb:20:in `block (3 levels) in change'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/relation/delegation.rb:85:in `each'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/relation/delegation.rb:85:in `each'
/var/www/dris/releases/315/db/migrate/hrm/20200409162158_modify_desigs.rb:18:in `block (2 levels) in change'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/relation/delegation.rb:85:in `each'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/relation/delegation.rb:85:in `each'
/var/www/dris/releases/315/db/migrate/hrm/20200409162158_modify_desigs.rb:17:in `block in change'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_handling.rb:269:in `swap_connection_handler'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_handling.rb:176:in `with_handler'
/home/deployer/.rvm/gems/ruby-2.5.1/gems/activerecord-6.0.2.1/lib/active_record/connection_handling.rb:132:in `connected_to'
/var/www/dris/releases/315/db/migrate/hrm/20200409162158_modify_desigs.rb:15:in `change'..
.
.

This is error of just single run. I tried many times, sometimes task hangs on puts HRM::Employee.first.name and sometimes error as shown above.Looks like concurrent migrations were running, but they shouldn't. Although last paragraph of error log describes some mistake in my iterations, but basic_scale= is defined and I've tested in console. Even this migration is running successfully from rails console. I've never faced such issue before using two databases and before migrating to Rails 6.

Edit: I tried splitting migration at the point of DML statements, and migrations run successfully. If I'm resetting column information in a single migration, it shouldn't cause this issue. It's quite strange why splitting migrations resolves the issue.


Solution

  • This seems to happen when invoking a model's own connection (like calling model.save or Model.connection.execute) inside the migration together with calling any ActiveRecord::Migration methods like create_table, etc. It breaks Rails' internal connection handling and causes weird behaviour. I've personally run into connection closed errors, but also it wrote to the wrong schema_information table.

    Rails team is notified of this bug and it should be fixed as for Rails 7.1. If you can't upgrade: splitting up your migration into 2 will solve this issue: one for your modifying the database and the other for calling models (.save etc) (just like mentioned)

    Example:

    # First modify table
    class ModifyTables < ActiveRecord::Migration[7.0]
      def change
        add_column :foo, :bar, :string
        drop_column :foo, :baz
      end
    end
    
    # THEN modify data
    class ModifyData < ActiveRecord::Migration[7.0]
      def up
        Foo.find_each do |foo|
          foo.update!(bar: "baz")
        end
      end
    end