mastermatt · April 2, 2021 19:47 · Apr 2, 2021 · Apr 2, 2021
diff --git a/connection_writeable_validator.rb b/connection_writeable_validator.rb
@@ -29,7 +29,7 @@ def connect(server)
         result = conn.query("SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';")
         innodb_read_only = (result.first || {})[:Value] != 'OFF'
 
-        if innodb_read_only || Faker::Boolean.boolean
+        if innodb_read_only
           log_each(:warn, "#{connection_info(conn)}Connected to read-only server. Disconnecting")
           disconnect_connection(conn)
           next

diff --git a/connection_writeable_validator.rb b/connection_writeable_validator.rb
@@ -0,0 +1,46 @@
+# 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 || Faker::Boolean.boolean
+          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