Sequel ConnectionWriteableValidator extension

connection_writeable_validator.rb

# frozen-string-literal: true

# The connection_writeable_validator extension modifies a Database to validate
# that newly created connections are connected to writeable InnoDB servers.
#
# Based on advise given by Jeremy Evans
# https://groups.google.com/g/sequel-talk/c/JFuxfDuoAZ4/m/hTvgj71uBgAJ
#
# During an Aurora fail over, new connections may resolve to the old primary
# instance, which converts to a ready-only node.
# Checking the `innodb_read_only` global variable allows Sequel::Databases
# to reject the connection if it connects to a read-only instance.
# If rejected, the connection is closed and `connect` is called again on the
# adapter for up to `connect_timeout` seconds.
#
# https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.BestPractices.html
#
#
module Sequel
  module ConnectionWriteableValidator
    def connect(server)
      # The 2 minute default is absurdly high, but it mirrors the default in the Mysql2 gem.
      # The expectation is to set a reasonable value in the connection options.
      timeout = Float(opts[:connect_timeout] || 120)
      timer = Sequel.start_timer

      while Sequel.elapsed_seconds_since(timer) < timeout
        conn = super(server)
        result = conn.query("SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';")
        innodb_read_only = (result.first || {})[:Value] != 'OFF'

        if innodb_read_only
          log_each(:warn, "#{connection_info(conn)}Connected to read-only server. Disconnecting")
          disconnect_connection(conn)
          next
        end

        return conn
      end

      raise DatabaseConnectionError, 'Unable to create a read/write connection'
    end
  end

  Database.register_extension(:connection_writeable_validator, ConnectionWriteableValidator)
end