Ruby on Rails Multiple Database Issue ActiveRecord::ReadOnlyError: Write query attempted while in readonly mode

user_4002659 0
Viewed 377

I have an application (Ruby on Rails v6) which is configured to establish connection with two databases. Application can read and write to the primary database whereas it can only read from secondary database.

I have setup an application as well: https://github.com/dineshpanda/blog_app

I get the following error while running rails test test/controllers/blogs_controller_test.rb:

BlogsControllerTest#test_should_get_index:
ActiveRecord::ReadOnlyError: Write query attempted while in readonly mode: UPDATE "users" SET "last_login" = $1, "updated_at" = $2 WHERE "users"."id" = $3
    app/controllers/application_controller.rb:8:in `find_user'
    test/controllers/blogs_controller_test.rb:10:in `block in <class:BlogsControllerTest>'

It makes sense that I get the error since I am trying to update users record while in read mode.

Question: Can I only specify writing role for all kind of reading and writing operations. I do not want to support both writing and reading role for primary database.

Looking forward to your answers.

ruby-on-rails-6 ruby-on-rails
2 Answers

Rails is unable to switch back to writing mode for ActiveRecord::Persistence::ClassMethods.instance_methods currently tested on rails 6.0.2 and 6.0.5.1.

This hotfix will help rails to switch back to writing mode for update/create/delete methods called on any active record objects.

lib/rails/postgres_write_handler.rb

ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval do
  def execute_and_clear(sql, name, binds, prepare: false)
    if write_query?(sql)
      ActiveRecord::Base.connected_to(role: :writing) do
        if without_prepared_statement?(binds)
          result = exec_no_cache(sql, name, [])
        elsif !prepare
          result = exec_no_cache(sql, name, binds)
        else
          result = exec_cache(sql, name, binds)
        end
        ret = yield result
        result.clear
        ret
      end
    else
      if without_prepared_statement?(binds)
        result = exec_no_cache(sql, name, [])
      elsif !prepare
        result = exec_no_cache(sql, name, binds)
      else
        result = exec_cache(sql, name, binds)
      end
      ret = yield result
      result.clear
      ret
    end
  end
end

app/models/application_record.rb

require_relative '../../lib/rails/postgres_write_handler.rb'
user_7791770

If I understand correctly, you would like to only allow writes on the primary db, and have all the reads go through the replica? You should be able to get your answer here: https://guides.rubyonrails.org/active_record_multiple_databases.html#activating-automatic-role-switching

Specifically,

For the specified time after the write, the application will read from the primary.

Rails guarantees "read your own write" and will send your GET or HEAD request to the writer if it's within the delay window. By default the delay is set to 2 seconds. You should change this based on your database infrastructure.

As to the issue Vivek mentioned in the other answer, it should have been addressed in 6.1.0. See https://rubyonrails.org/2020/12/9/Rails-6-1-0-release.

Rails 6.1 provides you with the ability to switch connections per-database. In 6.0 if you switched to the reading role then all database connections also switched to the reading role. Now in 6.1 if you set legacy_connection_handling to false in your configuration, Rails will allow you to switch connections for a single database by calling connected_to on the corresponding abstract class.

user_26560850
Related