heartcombo
ActiveRecord::ReadOnlyError when writing to user record on sign in
Moving a new Rails 8.0.1 app from development to production. When testing the authentication for the first time in production, Devise cannot write to the user table when signing in a user:
Information for cause: ActiveRecord::ReadOnlyError (Write query attempted while in readonly mode: UPDATE "users" SET "sign_in_count" = $1, "current_sign_in_at" = $2, "last_sign_in_at" = $3, "current_sign_in_ip" = $4, "last_sign_in_ip" = $5, "updated_at" = $6 WHERE "users"."id" = $7)
The problem occurs in the session controller on this line:
sign_in(resource_name, resource)
To test, I can create and save a user record in the rails console. Until debugging this, I never knew of all the various options concerning primary and replica databases. I have only a primary Postgresql database (and others for cache, queue, and cable) and have added this to the application.rb file:
config.active_record.writing_role = :primary
config.active_record.reading_role = :primary
config.after_initialize do
ActiveRecord::Base.connected_to(role: :writing) do
ActiveRecord::Base.connection.execute("SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE")
ActiveRecord::Base.connection.execute("SET default_transaction_read_only = OFF")
end
end
And this is the updated database.yml section for the production database:
production: &production
primary: &primary_production
<<: *default
database: app_production
username: <%= ENV["POSTGRES_USER"] %>
password: <%= ENV["POSTGRES_PASSWORD"] %>
replica: false
primary: true
read_only: false
variables:
statement_timeout: 5000
lock_timeout: 5000
idle_in_transaction_session_timeout: 5000
default_transaction_read_only: 'off'
Still no joy though and the user record continues to be blocked from writing in Devise. What do I need to do in production to allow it to be written to? Thanks!
Check Your Database Connection in Production
Run the following in the Rails production console (rails console -e production):
ActiveRecord::Base.connection.execute("SHOW transaction_read_only;")
If it returns on, your database is still in read-only mode, meaning your settings haven't taken effect.
To manually set it to read/write, you can run:
ActiveRecord::Base.connection.execute("SET default_transaction_read_only = OFF;")
If this works, your database is in read-only mode when the Rails app starts, and you need to fix that at the PostgreSQL level.
The above solution works if you have reads and writes in the same datacenter. However, if you load balance reads across geographic zones then updates will still fail.
