Thread-friendly shared connection

after.rb

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

before.rb

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

gistfile1.txt

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.

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! :)

In your test helper.rb

In your test helper.rb

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?

@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

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!

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

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

Thank you for this!!

Thank you for this!!

same issue as @vitobotta and @ntreadway so for now I just use http://github.com/zdennis/activerecord-mysql2-retry-ext

same issue as @vitobotta and @ntreadway so for now I just use http://github.com/zdennis/activerecord-mysql2-retry-ext

@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

@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

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.

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.

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

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

cheers @mperham this did the trick for me.

cheers @mperham this did the trick for me.

@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).

@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).

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

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

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'

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'

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.

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

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

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

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

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.

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.

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!

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 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.

@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.

@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?

@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.

@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.

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.

+1

+1