I am using Sequel on an application that is composed of about half a dozen sinatra services. These services use Sequel::Model.db to connect to postgres via some shared logic that is installed as a gem 'sequel_connection'. That connection logic is pretty simple. Here it is.
module Persistence module Sequel require 'sequel'
module Connection class << self def _database @_database ||= ::Sequel.connect(connection_string) end
def database retries = 0 begin retries += 1 _database rescue ::Sequel::DatabaseDisconnectError retry if retries > 10 end end
def connection_string ["postgres://"].tap do |s| s << config.username if config.username s << ":#{config.password}" if config.password s << "@#{config.host}" if config.host s << ":#{config.port}" if config.port s << "/#{config.database}" if config.database end.join("") end
def config Config.for(ENV['RACK_ENV']) end end
require 'ostruct' require 'yaml'
class Config < OpenStruct class << self attr_accessor :config_file
def for(env) new(config[env]) end
def config YAML.load_file("#{config_file}") end end end end end end
The services set the connections in an init script that get required in the sinatra class as such:
I am experiencing trouble with the connections dropping and not being re-establised. The air brake error is this:
Sequel::DatabaseDisconnectError: PG::Error: SSL error: sslv3 alert bad record mac
I assume there is some important thread safety stuff here that I am glossing over due to my inexperience with this kind of setup. What's the best way to deal with this? Is sharing the connection the wrong approach? Should I nix the gem and have this logic in each service? Would wrapping it in a Thread.new block work? Or is there something else I'm missing like disconnecting/reconnecting properly?
On Saturday, October 20, 2012 8:28:06 AM UTC-7, David Ott wrote:
> I am experiencing trouble with the connections dropping and not being > re-establised. The air brake error is this:
> Sequel::DatabaseDisconnectError: PG::Error: SSL error: sslv3 alert bad > record mac
> I assume there is some important thread safety stuff here that I am > glossing over due to my inexperience with this kind of setup. What's the > best way to deal with this? Is sharing the connection the wrong approach? > Should I nix the gem and have this logic in each service? Would wrapping it > in a Thread.new block work? Or is there something else I'm missing like > disconnecting/reconnecting properly?
Sequel is thread-safe, so it's unlikely to be thread safety. If I had to guess, you are using a forking webserver, loading your application code before forking, and not calling Database#disconnect before forking, resulting in multiple processes sharing the connections. But that's really only a guess based on limited information.
A couple of notes based on the code you posted:
1) Also note that there is no point in building a URL in your code, just pass an options hash to Sequel.connect.
2) Your attempts to retry are misguided. A DatabaseDisconnectError does not mean a problem with the Database object, but an issue with a single connection in the Database's connection pool, which Sequel handles by removing the connection from the pool. Creating a new Database object is absolutely the wrong approach.
Thanks for your comments. The retries were me grasping at straws. I figured it wasn't helping.
I think your assumption is correct. These services are using Passenger. Where is the best place to disconnect? I have an init.rb file that sets the connection and that gets required in the sinatra file. Can i call disconnect at the end of that init file?
On Saturday, October 20, 2012 12:52:20 PM UTC-4, Jeremy Evans wrote:
> On Saturday, October 20, 2012 8:28:06 AM UTC-7, David Ott wrote:
>> I am experiencing trouble with the connections dropping and not being >> re-establised. The air brake error is this:
>> Sequel::DatabaseDisconnectError: PG::Error: SSL error: sslv3 alert bad >> record mac
>> I assume there is some important thread safety stuff here that I am >> glossing over due to my inexperience with this kind of setup. What's the >> best way to deal with this? Is sharing the connection the wrong approach? >> Should I nix the gem and have this logic in each service? Would wrapping it >> in a Thread.new block work? Or is there something else I'm missing like >> disconnecting/reconnecting properly?
> Sequel is thread-safe, so it's unlikely to be thread safety. If I had to > guess, you are using a forking webserver, loading your application code > before forking, and not calling Database#disconnect before forking, > resulting in multiple processes sharing the connections. But that's really > only a guess based on limited information.
> A couple of notes based on the code you posted:
> 1) Also note that there is no point in building a URL in your code, just > pass an options hash to Sequel.connect.
> 2) Your attempts to retry are misguided. A DatabaseDisconnectError does > not mean a problem with the Database object, but an issue with a single > connection in the Database's connection pool, which Sequel handles by > removing the connection from the pool. Creating a new Database object is > absolutely the wrong approach.
On Saturday, October 20, 2012 11:07:59 AM UTC-7, David Ott wrote:
> Jeremy,
> Thanks for your comments. The retries were me grasping at straws. I > figured it wasn't helping.
> I think your assumption is correct. These services are using Passenger. > Where is the best place to disconnect? I have an init.rb file that sets the > connection and that gets required in the sinatra file. Can i call > disconnect at the end of that init file?
IIRC, Passenger, unlike Unicorn, lacks a before_fork hook, so disconnecting near the end of the application code loading is the best place.
On Sunday, October 21, 2012 6:22:30 AM UTC+8, Jeremy Evans wrote:
> On Saturday, October 20, 2012 11:07:59 AM UTC-7, David Ott wrote:
>> Jeremy,
>> Thanks for your comments. The retries were me grasping at straws. I >> figured it wasn't helping.
>> I think your assumption is correct. These services are using Passenger. >> Where is the best place to disconnect? I have an init.rb file that sets the >> connection and that gets required in the sinatra file. Can i call >> disconnect at the end of that init file?
> IIRC, Passenger, unlike Unicorn, lacks a before_fork hook, so > disconnecting near the end of the application code loading is the best > place.
On Sunday, December 2, 2012 11:01:35 AM UTC-8, Daniel Tsai wrote: > I have the same problem and happened to google this thread. Is my modifed > unicorn config ok?
> before_fork do |server, worker| > # Disconnect all database connection from Sequel > DB.disconnect > sleep 1 > end
> after_fork do |server, worker| > # Reconnect database by Sequel > DB = Sequel.connect(ENV['DATABASE_URL'])
You don't need to manually call Sequel.connect after fork, so you should remove this. I'm not sure about Redis/Sidekiq, but the same may be true for them, you should probably talk to those authors.
I really appreciate your quick reply and I have been a big fan of Sequel. I just want to make sure I understand correctly that I only need to call DB.disconnect in before_fork block and do not do anything in after_fork block. Will DB get the connection automatically in this case?
before_fork do |server, worker| # Disconnect all database connection from Sequel DB.disconnect sleep 1 end
after_fork do |server, worker| # Do nothing here and DB will connect automatically end
>> before_fork do |server, worker| >> # Disconnect all database connection from Sequel >> DB.disconnect >> sleep 1 >> end
>> after_fork do |server, worker| >> # Reconnect database by Sequel >> DB = Sequel.connect(ENV['DATABASE_URL'])
> You don't need to manually call Sequel.connect after fork, so you should > remove this. I'm not sure about Redis/Sidekiq, but the same may be true > for them, you should probably talk to those authors.
On Sunday, December 2, 2012 1:46:48 PM UTC-8, Daniel Tsai wrote: > I really appreciate your quick reply and I have been a big fan of Sequel. > I just want to make sure I understand correctly that I only need to call > DB.disconnect in before_fork block and do not do anything in after_fork > block. Will DB get the connection automatically in this case?
> before_fork do |server, worker| > # Disconnect all database connection from Sequel > DB.disconnect > sleep 1 > end
> after_fork do |server, worker| > # Do nothing here and DB will connect automatically > end
Correct. The only part of Sequel that cannot be shared in a multiprocess application is the underlying database socket connections. DB.disconnect clears the connections from the Database object's connection pool, and doing that before forking ensures that each forked child will have its own connections that are not shared (connections will be reestablished on an as-needed basis in each child).
