Create a gist now

Instantly share code, notes, and snippets.

Embed
Thread-friendly shared connection
class ActiveRecord::Base
mattr_accessor :shared_connection
@@shared_connection = nil
def self.connection
@@shared_connection || ConnectionPool::Wrapper.new(:size => 1) { retrieve_connection }
end
end
ActiveRecord::Base.shared_connection = ActiveRecord::Base.connection
class ActiveRecord::Base
mattr_accessor :shared_connection
@@shared_connection = nil
def self.connection
@@shared_connection || retrieve_connection
end
end
ActiveRecord::Base.shared_connection = ActiveRecord::Base.connection
Capybara uses two threads to run client and server. If you just cargo cult
the DatabaseCleaner code that is floating around, you can run into
https://github.com/brianmario/mysql2/issues/99 because the threads will
occasionally use the same connection at the same time.
The fix is to use a single connection but protect access to it by having each
thread "check out" the connection when they are using it. This is trivial to
do with the `connection_pool` gem. Add it to your Gemfile and use the "after"
code above.
@vitobotta

This comment has been minimized.

Show comment
Hide comment
@vitobotta

vitobotta Jul 11, 2012

Hi, could you kindly clarify where exactly I should put this code? Thanks! :)

Hi, could you kindly clarify where exactly I should put this code? Thanks! :)

@mperham

This comment has been minimized.

Show comment
Hide comment
@mperham

mperham Jul 12, 2012

In your test helper.rb

Owner

mperham commented Jul 12, 2012

In your test helper.rb

@vitobotta

This comment has been minimized.

Show comment
Hide comment
@vitobotta

vitobotta Jul 12, 2012

Hi, thanks for your reply. I am trying to use the code with Cucumber, so I have added it to the env.rb file (I've tried both in the prefork and each_run blocks), and I've added the connection_pool gem to my Gemfile.
When I run features all scenarios fail because of "undefined method increment_open_transactions' for ActiveRecord::Base:Class (NoMethodError)" before a scenario starts, and "undefined methoddecrement_open_transactions' for ActiveRecord::Base:Class (NoMethodError)" after. Any idea?

Hi, thanks for your reply. I am trying to use the code with Cucumber, so I have added it to the env.rb file (I've tried both in the prefork and each_run blocks), and I've added the connection_pool gem to my Gemfile.
When I run features all scenarios fail because of "undefined method increment_open_transactions' for ActiveRecord::Base:Class (NoMethodError)" before a scenario starts, and "undefined methoddecrement_open_transactions' for ActiveRecord::Base:Class (NoMethodError)" after. Any idea?

@ntreadway

This comment has been minimized.

Show comment
Hide comment
@ntreadway

ntreadway Jul 27, 2012

@mperham I am also getting:

  NoMethodError:
        undefined method `decrement_open_transactions' for ActiveRecord::Base:Class

@mperham I am also getting:

  NoMethodError:
        undefined method `decrement_open_transactions' for ActiveRecord::Base:Class
@RamyaAnguraj

This comment has been minimized.

Show comment
Hide comment
@RamyaAnguraj

RamyaAnguraj Aug 19, 2012

Hi ,
Can anyone kindly tell me where to put this after.rb code ? whether to put in test_helper.rb / config/environments/test.rb / env.rb ? Thanks in advance!

Hi ,
Can anyone kindly tell me where to put this after.rb code ? whether to put in test_helper.rb / config/environments/test.rb / env.rb ? Thanks in advance!

@tesserakt

This comment has been minimized.

Show comment
Hide comment
@tesserakt

tesserakt Aug 23, 2012

@mperham, @ntreadway, did you guys figure out the problem?

@mperham, @ntreadway, did you guys figure out the problem?

@andyw8

This comment has been minimized.

Show comment
Hide comment
@andyw8

andyw8 Sep 18, 2012

Thank you for this!!

andyw8 commented Sep 18, 2012

Thank you for this!!

@driv3r

This comment has been minimized.

Show comment
Hide comment
@driv3r

driv3r Sep 27, 2012

@libo

This comment has been minimized.

Show comment
Hide comment
@libo

libo Oct 29, 2012

@vitobotta, @ntreadway, @mperham, @driv3r what about this?

class ActiveRecord::Base
  mattr_accessor :shared_connection
  @@shared_connection = nil

  def self.increment_open_transactions
    connection.increment_open_transactions
  end

  def self.decrement_open_transactions
    connection.decrement_open_transactions
  end

  def self.connection
    @@shared_connection || ConnectionPool::Wrapper.new(:size => 1) { retrieve_connection }
  end
end

libo commented Oct 29, 2012

@vitobotta, @ntreadway, @mperham, @driv3r what about this?

class ActiveRecord::Base
  mattr_accessor :shared_connection
  @@shared_connection = nil

  def self.increment_open_transactions
    connection.increment_open_transactions
  end

  def self.decrement_open_transactions
    connection.decrement_open_transactions
  end

  def self.connection
    @@shared_connection || ConnectionPool::Wrapper.new(:size => 1) { retrieve_connection }
  end
end
@daliusg

This comment has been minimized.

Show comment
Hide comment
@daliusg

daliusg Dec 12, 2012

Encountering the same undefined method increment_open_transactions' for ActiveRecord::Base:Class errors as @vitobotta & @ntreadway. Tried @libo's suggestion, but didn't work. Anybody figure out why this is happening? Using cucumber & DatabaseCleaner set to transaction cleaning strategy.

daliusg commented Dec 12, 2012

Encountering the same undefined method increment_open_transactions' for ActiveRecord::Base:Class errors as @vitobotta & @ntreadway. Tried @libo's suggestion, but didn't work. Anybody figure out why this is happening? Using cucumber & DatabaseCleaner set to transaction cleaning strategy.

@pftg

This comment has been minimized.

Show comment
Hide comment
@pftg

pftg Dec 19, 2012

@vitobotta, @ntreadway, @mperham, @driv3r, @daliusg you should update connection_pool. In master all this stuff works well!

pftg commented Dec 19, 2012

@vitobotta, @ntreadway, @mperham, @driv3r, @daliusg you should update connection_pool. In master all this stuff works well!

@rounders

This comment has been minimized.

Show comment
Hide comment
@rounders

rounders Mar 20, 2013

cheers @mperham this did the trick for me.

cheers @mperham this did the trick for me.

@gtd

This comment has been minimized.

Show comment
Hide comment
@gtd

gtd Aug 15, 2013

@mperham thanks for publishing this and taking the time to sprinkle comments around. Quick solution to a thorny problem (I'm entirely too dependent on transactional fixtures in a huge legacy app).

gtd commented Aug 15, 2013

@mperham thanks for publishing this and taking the time to sprinkle comments around. Quick solution to a thorny problem (I'm entirely too dependent on transactional fixtures in a huge legacy app).

@ncr

This comment has been minimized.

Show comment
Hide comment
@ncr

ncr Nov 7, 2013

Finally, my integration tests running reliably :) Thanks Mike.

ncr commented Nov 7, 2013

Finally, my integration tests running reliably :) Thanks Mike.

@siva3395

This comment has been minimized.

Show comment
Hide comment
@siva3395

siva3395 Dec 5, 2013

I am facing below issue is i replace with after..

/home/ubuntu/redzone/spec/spec_helper.rb:53:in `connection': uninitialized constant ActiveRecord::Base::ConnectionPool (NameError)
    from /home/ubuntu/redzone/spec/spec_helper.rb:56:in `block in <top (required)>'
    from /home/ubuntu/redzone/vendor/bundle/ruby/1.9.1/gems/spork-1.0.0rc4/lib/spork.rb:24:in `prefork'
    from /home/ubuntu/redzone/spec/spec_helper.rb:30:in `<top (required)>'
    from /home/ubuntu/redzone/spec/controllers/advertiser_campaign_ads_controller_spec.rb:1:in `require'

siva3395 commented Dec 5, 2013

I am facing below issue is i replace with after..

/home/ubuntu/redzone/spec/spec_helper.rb:53:in `connection': uninitialized constant ActiveRecord::Base::ConnectionPool (NameError)
    from /home/ubuntu/redzone/spec/spec_helper.rb:56:in `block in <top (required)>'
    from /home/ubuntu/redzone/vendor/bundle/ruby/1.9.1/gems/spork-1.0.0rc4/lib/spork.rb:24:in `prefork'
    from /home/ubuntu/redzone/spec/spec_helper.rb:30:in `<top (required)>'
    from /home/ubuntu/redzone/spec/controllers/advertiser_campaign_ads_controller_spec.rb:1:in `require'
@dholdren

This comment has been minimized.

Show comment
Hide comment
@dholdren

dholdren Mar 27, 2014

This worked great for me with cucumber and capybara 1.x
I just had to add it to my features/support/env.rb file

However, with capybara 2.x I get the connection errors.

This worked great for me with cucumber and capybara 1.x
I just had to add it to my features/support/env.rb file

However, with capybara 2.x I get the connection errors.

@chamnap

This comment has been minimized.

Show comment
Hide comment
@chamnap

chamnap Apr 9, 2014

@mperham, it works great for me. Thanks alot. :)

chamnap commented Apr 9, 2014

@mperham, it works great for me. Thanks alot. :)

@chuckd

This comment has been minimized.

Show comment
Hide comment
@chuckd

chuckd Apr 17, 2014

@siva3395 have you added the connection_pool gem to your Gemfile?

chuckd commented Apr 17, 2014

@siva3395 have you added the connection_pool gem to your Gemfile?

@stve

This comment has been minimized.

Show comment
Hide comment
@stve

stve Oct 3, 2014

Has anyone taken this approach on an app with multiple database connections? I kept getting

ActiveRecord::StatementInvalid:
       Mysql2::Error: Table 'my_application.users' doesn't exist:: SHOW FULL FIELDS FROM `users`

It was only when I commented this out that my tests passed again.

stve commented Oct 3, 2014

Has anyone taken this approach on an app with multiple database connections? I kept getting

ActiveRecord::StatementInvalid:
       Mysql2::Error: Table 'my_application.users' doesn't exist:: SHOW FULL FIELDS FROM `users`

It was only when I commented this out that my tests passed again.

@mrsimo

This comment has been minimized.

Show comment
Hide comment
@mrsimo

mrsimo Mar 4, 2015

We've been running this flawlessly for a long time and since updating the connection_pool gem to the 2.1.2 version our tests have started failing.

We connect to two different databases, so we have a slightly altered version:

class ActiveRecord::Base
  mattr_accessor :shared_connections
  self.shared_connections = {}

  def self.connection
    shared_connections[connection_config[:database]] ||= begin
      ConnectionPool::Wrapper.new(size: 1) { retrieve_connection }
    end
  end
end

I wonder if you can think of something new in the gem that would cause trouble? Thanks!

mrsimo commented Mar 4, 2015

We've been running this flawlessly for a long time and since updating the connection_pool gem to the 2.1.2 version our tests have started failing.

We connect to two different databases, so we have a slightly altered version:

class ActiveRecord::Base
  mattr_accessor :shared_connections
  self.shared_connections = {}

  def self.connection
    shared_connections[connection_config[:database]] ||= begin
      ConnectionPool::Wrapper.new(size: 1) { retrieve_connection }
    end
  end
end

I wonder if you can think of something new in the gem that would cause trouble? Thanks!

@jwg2s

This comment has been minimized.

Show comment
Hide comment
@jwg2s

jwg2s Mar 12, 2015

@mrsimo this was really helpful. We also connect to multiple databases. Hopefully people google capybara shared database connection with multiple databases and find this like I did.

jwg2s commented Mar 12, 2015

@mrsimo this was really helpful. We also connect to multiple databases. Hopefully people google capybara shared database connection with multiple databases and find this like I did.

@pratik60

This comment has been minimized.

Show comment
Hide comment
@pratik60

pratik60 Mar 25, 2015

@mperham -: Hey, we used the earlier code which resulted in the following.

PG::UnableToSend: another command is already in progress

Then we found your code, but initially it couldn't find a few fields within the thread, but after running a few times our thread seems stuck now as a result. Its so flaky and hard to get what's happening.

Any one has any idea, what I can do to fix it? Faced similar issues?

@mperham -: Hey, we used the earlier code which resulted in the following.

PG::UnableToSend: another command is already in progress

Then we found your code, but initially it couldn't find a few fields within the thread, but after running a few times our thread seems stuck now as a result. Its so flaky and hard to get what's happening.

Any one has any idea, what I can do to fix it? Faced similar issues?

@oneamtu

This comment has been minimized.

Show comment
Hide comment
@oneamtu

oneamtu Sep 10, 2015

@pratik60
Solves
PG::UnableToSend: another command is already in progress
and
undefined method 'fields'
and other flaky race errors.

class ActiveRecord::Base
  mattr_accessor :shared_connection
  @@shared_connection = nil

  def self.connection
    @@shared_connection || ConnectionPool::Wrapper.new(:size => 1) { retrieve_connection }
  end
end

ActiveRecord::Base.shared_connection = ActiveRecord::Base.connection

# hack a mutex in the query execution so that we don't
# get competing queries that can timeout and not get cleaned up
module MutexLockedQuerying
  @@semaphore = Mutex.new

  def async_exec(*)
    @@semaphore.synchronize { super }
  end
end

PG::Connection.prepend(MutexLockedQuerying)

Check out https://gist.github.com/josevalim/470808 for more history on the issue.

oneamtu commented Sep 10, 2015

@pratik60
Solves
PG::UnableToSend: another command is already in progress
and
undefined method 'fields'
and other flaky race errors.

class ActiveRecord::Base
  mattr_accessor :shared_connection
  @@shared_connection = nil

  def self.connection
    @@shared_connection || ConnectionPool::Wrapper.new(:size => 1) { retrieve_connection }
  end
end

ActiveRecord::Base.shared_connection = ActiveRecord::Base.connection

# hack a mutex in the query execution so that we don't
# get competing queries that can timeout and not get cleaned up
module MutexLockedQuerying
  @@semaphore = Mutex.new

  def async_exec(*)
    @@semaphore.synchronize { super }
  end
end

PG::Connection.prepend(MutexLockedQuerying)

Check out https://gist.github.com/josevalim/470808 for more history on the issue.

@hoffmanc

This comment has been minimized.

Show comment
Hide comment
@hoffmanc

hoffmanc Sep 30, 2015

I would say avoiding shared state is far from cargo culting. It actually avoids a bunch of hard-to-debug problems quite nicely.

I would say avoiding shared state is far from cargo culting. It actually avoids a bunch of hard-to-debug problems quite nicely.

@chevinbrown

This comment has been minimized.

Show comment
Hide comment
@chevinbrown

chevinbrown Mar 4, 2016

+1

+1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment