Occurrence of Sequel::DatabaseDisconnectError at puma worker, even though the db is disconnected in puma's before_fork-hook

I have a hanami 1.3 app, but the issue should be unrelated to hanami. I want to connect to a second db with plain Sequel-gem. Therefore I define the connection in hanami's config/environment.rb:

# config/environment.rb
# ...
DWH = Sequel.connect(ENV['DWH'], :loggers => [Logger.new($stdout)])
#...

In production I have a puma-config like that:

# config/puma.rb
require_relative './environment'
workers 5

threads_count = 1
threads threads_count, threads_count

daemonize true

preload_app!

rackup      DefaultRackup
port        2300
environment 'production'

before_fork do
  DWH.disconnect
end

on_worker_boot do
  Hanami.boot
end

I used the before_fork hook to disconnect the db (http://sequel.jeremyevans.net/rdoc/files/doc/fork_safety_rdoc.html). But after some time I get errors like that:

Sequel::DatabaseDisconnectError: PG::UnableToSend: SSL SYSCALL error: EOF detected
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:166:in `async_exec'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:166:in `block in execute_query'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/database/logging.rb:49:in `log_connection_yield'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:166:in `execute_query'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:153:in `block in execute'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:129:in `check_disconnect_errors'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:153:in `execute'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:515:in `_execute'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:327:in `block (2 levels) in execute'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:537:in `check_database_errors'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:327:in `block in execute'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/database/connecting.rb:301:in `block in synchronize'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/connection_pool/threaded.rb:107:in `hold'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/database/connecting.rb:301:in `synchronize'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:327:in `execute'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/dataset/actions.rb:1135:in `execute'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/postgres.rb:680:in `fetch_rows'
    /home/usr/vendor/bundle/ruby/2.6.0/gems/sequel-4.49.0/lib/sequel/dataset/actions.rb:155:in `each'
  /home/usr/app/lib/repositories/dwh_repository.rb:39:in `to_a'

Solution 1:

I had a conversation with the author of Sequel. It seems, that the puma config and the connection-approach is correct.

It seems, the DB-connections are dropped by another network-part (i.e. tcp-timeout, firewall, ...).

In such a case this is the expected behavior of Sequel:

The application lost connection to the database. When this happens, a DatabaseDisconnectError is raised and Sequel removes the connection from the connection pool. New connection will be created as needed up to the maximum pool size.

The best way to solve the issue, is to fix the reason for the connection-dropping (change setup [DB, server]). A pragmatic solution could be putting DB and application on same server.

If that is not possible, there's a Sequel-extension, which could be a workaround: https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/connection_validator_rb.html

Solution 2:

I had the same problem, to fix it just increase the number or threads in config, I go with preloaded 32 and 0 workers (actualy one but not in cluster mode, that would be enabled if I used workers 1)

In short, you need this

workers 0   
threads 32, 32

My full puma config for custom rack app

# set app root
Dir.chdir File.expand_path("../..", __FILE__)

# load RACK_ENV
require 'dotenv'
Dotenv.load

require 'bundler/setup'
Bundler.require

plugin          :tmp_restart
port            3000
log_requests    false
nakayoshi_fork  true
pidfile         './tmp/puma.pid'
state_path      './tmp/puma.state'

# activate_control_app('tcp://127.0.0.1:9000', no_token: true)

if ENV['RACK_ENV'] == 'production'
  stdout_redirect './log/puma.log', './log/puma_errros.log'
  environment 'production'
  workers     0
  threads     32, 32

  # restart if there is no response on /ok, check every 10 seconds
  Thread.new do
    loop do
      sleep 10

      unless `curl -si -m 5 http://localhost:3000/ok`.include?('200 OK')
        Logger.new('log/app_boot.log').error 'http://localhost:3000/ok fail, restarting'
        `touch tmp/restart.txt`
      end
    end
  end
end