Create a gist now

Instantly share code, notes, and snippets.

# In your test_helper.rb
class ActiveRecord::Base
mattr_accessor :shared_connection
@@shared_connection = nil
def self.connection
@@shared_connection || retrieve_connection
end
end
# Forces all threads to share the same connection. This works on
# Capybara because it starts the web server in a thread.
ActiveRecord::Base.shared_connection = ActiveRecord::Base.connection
@mcmire
mcmire commented Jul 11, 2010

Looks interesting... what's the use case if you don't mind me asking?

@josevalim
Owner

http://twitter.com/josevalim/status/18195382848

More related comments on my twitter account. :)

@mcmire
mcmire commented Jul 11, 2010

I came from that, actually :) I guess I'm wanting to know... a) Are you using this patch with Selenium in particular? b) Currently we use the Culerity driver in Capybara for JS tests... is that what you mean instead of Celerity and if so, would this patch work with that somehow? (You have to send all requests to a completely separate Rails process so transactional fixtures so far has been a no-go.)

@josevalim
Owner

When you are using Capybara, you probably noticed that it starts the server on its own. That is done by starting your app server in a new thread. Since ActiveRecord has a connection pool thread-based, the server and the test suite are using different connections and transactions are not shared. This makes impossible for you to use transactional fixtures. This patch allows the connection to be shared, allowing you to use transactional fixtures once again.

It's working fine here. You just need to ensure that:

  1. Each tests is being wrapped in a transaction (usually not true for cucumber);
  2. You still need a solution like a database cleaner when your test suite starts, to ensure all tables are empty;
@mcmire
mcmire commented Jul 12, 2010

Gotcha. Yeah, when I moved our testing setup over to Capybara, just to keep with how we'd been doing things, I overrode the Culerity driver so that it hits a copy of the app running in a separate Rails process (http://localhost:someport). That's why I'd mentioned that.

Anyways, I'll definitely try out your fix. Always happy to make the testing process less painful :)

@carlosantoniodasilva

Small benchmark done running with and without AR shared connection solution, with Culerity/Celerity:

Using transactional_fixtures = false

Finished in 223.613601 seconds
550 examples, 0 failures, 4 pending

Finished in 240.086725 seconds
550 examples, 0 failures, 4 pending

Using transactional_fixtures = true and AR shared connection solution

Finished in 173.050436 seconds
550 examples, 0 failures, 4 pending

Finished in 169.368463 seconds
550 examples, 0 failures, 4 pending

@mcmire
mcmire commented Jul 12, 2010

Oh, I remember why I patched Capybara like I did. Every time you run Capybara+Culerity, doesn't Capybara run the server every time?

@josevalim
Owner

Nops. It does it just once, at least here.

@mperham
mperham commented Aug 31, 2012

@josevalim Just so you know, this code has a race condition between the rspec thread and the server thread both accessing the shared connection at the same time. You can wrap the connection (using the connection_pool gem) to fix this:

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

This fails in Postgres. Here's what I get when I call FactoryGirl to create my user:

�[1m�[35m (0.1ms)�[0m BEGIN
PG::Error: connection is closed: SELECT a.attname, format_type(a.atttypid, a.atttypmod),
pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = '"users"'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

�[1m�[36m (0.2ms)�[0m �[1mROLLBACK�[0m
�[1m�[35m (0.1ms)�[0m BEGIN
PG::Error: connection is closed: SELECT a.attname, format_type(a.atttypid, a.atttypmod),
pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = '"users"'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

�[1m�[36m (0.1ms)�[0m �[1mROLLBACK�[0m
�[1m�[35m (0.1ms)�[0m BEGIN
PG::Error: connection is closed: SELECT a.attname, format_type(a.atttypid, a.atttypmod),
pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = '"users"'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

�[1m�[36m (0.1ms)�[0m �[1mROLLBACK�[0m

That's no good. Using (or trying to use, and failing for 5 days straight now) Guard/Spork/Rspec/FactoryGirlPhantomJS/Poltergeist.

@HuckyDucky

The solution in Postgres and Spork is to move the call:

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

to the Spork.each_run block.

@Malet
Malet commented Jul 25, 2013

If you need to use more than one connection (if some of your models use different databases) at a time you can use this:

class ActiveRecord::Base
  mattr_accessor :shared_connection
  @@shared_connection = {}

  def self.connection
    @@shared_connection[self.connection_config[:database]] ||= retrieve_connection
  end
end
@carlosantoniodasilva

Hey @mperham, we've been using this shared connection trick for some time in different projects here. Some time ago we started to get some errors regarding threads and postgresql in one project (using poltergeist), and since then I started using the connection_pool gem. It seemed to have solved the problem at first, however we're now back to having similar errors with multiple threads access.

Question is: are you aware of any known issues with that connection pool approach?

I'm already thinking about getting back to good old database cleaner and sacrifice speed for reliability.

@abgoldstein

Hey, @carlosantoniodasilva.

We've been bumping into the same issue. The most useful information I've found so far is a blog post by @fcheung. Using the ConnectionPool for the shared connection seems to have solved my issue so far, but @fcheung points out that it doesn't entirely save you and it looks like you're running into the same issue. That blog post basically suggests adding a call to ActiveRecord::Base.clear_active_connections!, but then I'm not sure that we're saving anything by trying to share this DB connection anymore. It seems like you might as well revert to truncating via database cleaner.

Here's our situation to hopefully try to track this down a little better. In a Rails 4 project using MySQL, I'm trying to run an acceptance test with rspec/capybara/poltergeist. I intermittently get errors like this:

Mysql2::Error: This connection is in use by: #<Thread:0x0000000091fcf8 sleep>:
SELECT  `users`.* FROM `users`  WHERE `users`.`status` = 1 AND `users`.`id` = 65  ORDER BY `users`.`id` ASC LIMIT 1

I've mostly been getting this error since introducing a feature with steps that create a user with FactoryGirl, and then interact with a page whose content is largely AJAXed into existence. One of those interactions ultimately sends a PATCH back to the server, and then loads that same AJAXed into existence content again. The error seems to happen at non-deterministic points. For now, I think we're going to use the ConnectionPool solution from above, but it'd be great to hear more thoughts about it.

@abgoldstein

So here's our best pass on this problem so far. We were able to track down the AJAX POST that we were doing that was causing the race condition. A coworker, @97jaz, shared this snippet from an earlier project in spec_helper.rb:

RSpec.configure do |config|
  require 'timeout'
  config.after(:each, js: true) do
    Timeout.timeout(Capybara.default_wait_time) do
      until (i = page.evaluate_script("$.active")).zero?
        Rails.logger.info "example [#{example.description}] has #{i} outstanding XHR(s)"
        sleep 0.1
      end
    end
  end
end

Basically, we will only encounter this thread issue in a Poltergeist test, so when we're running our steps we wait for all AJAX requests to finish. This seems to have cleared up our issue.

@rmontgomery429

@abgoldstein Thank you for sharing! This approach worked for us as we were running into the exact same issue with Capybara and AJAX requests. They would rear their ugly head as an intermittent failure. 👍

@killthekitten

When I use this hack, I get tons of spec failures with messages like that:

PG::DuplicatePstatement: ERROR:  prepared statement "a3731" already exists

Postgres 9.3.2, Rails 4, Capybara-webkit, Rspec

@bradrobertson

@killthekitten We're also getting this error on our Rails4 branch

@ka8725
ka8725 commented Jul 2, 2014

@bradrobertson have you solved the problem? I also have strange errors using this approach with Rails 4. For example:

  1. PG::UnableToSend: another command is already in progress
  2. undefined method `fields' for nil:NilClass
  3. PG::UnableToSend: socket not open

And so on...

This is my config:

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

  def self.connection
    @@shared_connection || retrieve_connection
  end
end

RSpec.configure do |config|
  config.before(:suite) do
    DatabaseCleaner.strategy = :transaction
    DatabaseCleaner.clean_with(:truncation)
  end

  config.around(:each, js: true) do |example|
    ActiveRecord::Base.shared_connection = ActiveRecord::Base.connection
    example.run
    ActiveRecord::Base.shared_connection = nil
  end

  config.around(:each) do |example|
    DatabaseCleaner.cleaning do
      example.run
    end
  end
end

Any ideas?

@cavneb
cavneb commented Jul 16, 2014

@ka8725 I am also having the same bug you are having. Did you get any answers?

@gustavowt

@ka8725 and @cavneb i am solved this issue using an wait_for_ajax helper. This issue occurred when capybara-webkit fires an ajax request and in the spec next line you tried access some database record.

on my suite i have following behavior

bar = bars(:main)
user = users(:main)

fill_in "user", with: user.name
expect(page).to have_field, "bar", with: bar.name

in this case im listening javascript change event on user field, who fires an ajax request to fill bar with something. In this case the spec will broke because this issue.

with wait_for_ajax

module WaitForAjax
  def wait_for_ajax
    Timeout.timeout(Capybara.default_wait_time) do
      loop until finished_all_ajax_requests?
    end
  end

  def finished_all_ajax_requests?
    page.evaluate_script('jQuery.active').zero?
  end
end

RSpec.configure do |config|
  config.include WaitForAjax, type: :feature
end

i just change my spec to something like that

bar = bars(:main)
user = users(:main)

fill_in "user", with: user.name
wait_fo_ajax
expect(page).to have_field, "bar", with: bar.name

and my specs becomes to be green

@MrJaba
MrJaba commented Jul 31, 2014

Amazing this is still an issue, but is there any chance someone could post a complete setup example? Tearing my hair out here. User Rails 4.1.4, Postgres 9.3, Capybara, Poltergeist and running into all the same issues as @ka8725:

PG::UnableToSend: another command is already in progress
undefined method `fields' for nil:NilClass
PG::UnableToSend: socket not open

@rochers
rochers commented Aug 7, 2014

+1

@mikecmpbll

Right then. The ConnectionPool solution wasn't working for me, I still kept getting ActiveRecord::StatementInvalid: Mysql2::Error: This connection is in use by: #<Thread:0x007ff431810830 sleep> errors. After hours and hours of digging around in AR trying to figure out how this was happening when the ConnectionPool gem is supposed to be locking it down to one access at a time, I decided to take a different tack, I opened up the Mysql2 gem and hacked a mutex around the query execution method.

class Mysql2::Client
  @@semaphore = Mutex.new

  def query_with_lock(*args)
    @@semaphore.synchronize { query_without_lock(*args) }
  end
  alias_method :query_without_lock, :query
  alias_method :query, :query_with_lock
end

The query method is actually implemented in C so I've wrapped the mutex around it with alias method chain. Lo and behold this actually works for me, so I'm happy. I've too much of a headache right now to work out whether this is potentially an awful thing to do, but the test suite runs green now at least. You still need to hack the same connection, otherwise your Capybara stuff won't have access to your transaction but you can do that simply:

class ActiveRecord::Base
  @@shared_connection = retrieve_connection

  def self.connection
    @@shared_connection
  end
end
@ka8725
ka8725 commented Aug 16, 2014

Hi guys (@cavneb and @gustavowt), I've solved the issue with the following config:

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

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

RSpec.configure do |config|
  config.before(:suite) do
    DatabaseCleaner.strategy = :transaction
    DatabaseCleaner.clean_with(:transaction)
  end

  # If an example has one of the following options: :js, :driver
  # the connection to the databas e will be shared to Capybara thread.
  # Option :clean_db_strategy allows to set any of three strategies available in
  # DatabaseCleaner: :transaction, :truncation, :deletion. The default and the fastest
  # value is :transaction.
  config.around(:each) do |example|
    if example.metadata[:clean_db_strategy]
      DatabaseCleaner.strategy = example.metadata[:clean_db_strategy]
    end

    DatabaseCleaner.cleaning do
      if example.metadata[:js] || example.metadata[:driver]
        ActiveRecord::Base.shared_connection = ActiveRecord::Base.connection
        example.run
        ActiveRecord::Base.shared_connection = nil
      else
        example.run
      end
    end

    if example.metadata[:clean_db_strategy]
      DatabaseCleaner.strategy = :transaction
    end
  end

end

For the config you have to install connection_pool gem for your test environment. With the config I also have ability to start any db strategy as you see. But now I have one more problem: the tests fail sometimes saying that there is no connection to DB and sometimes with a circular dependency in ActiveRecord associations... Now I don't know what to do: may be this is because of race conditions from Poltergeist's parallel requests to the server or because of some other reason which I can't solve by myself. Is there are anyone who could help?

@divineforest

@ka8725 try to set

config.eager_load = true

in config/environments/test.rb

@ka8725
ka8725 commented Sep 10, 2014

@divineforest, good advise. But this solution won't work for us too, because as I remember we had an issue with this option using active_admin... I've returned to truncation strategy and it works as expected. This config is 100% works

@divineforest

@ka8725 @gustavowt @MrJaba @mikecmpbll @rochers @cavneb @killthekitten

Here is robust and still fast solution for these problems

PG::UnableToSend: another command is already in progress
undefined method `fields' for nil:NilClass
PG::UnableToSend: socket not open

It uses shared connection, connection pool and waits for the ajax requests to finish at the end of each js: true spec.

# spec/support/capybara.rb
def wait_for_ajax
  return unless respond_to?(:evaluate_script)
  wait_until { finished_all_ajax_requests? }
end

def finished_all_ajax_requests?
  evaluate_script("!window.jQuery") || evaluate_script("jQuery.active").zero?
end

def wait_until(max_execution_time_in_seconds = Capybara.default_wait_time)
  Timeout.timeout(max_execution_time_in_seconds) do
    loop do
      if yield
        return true
      else
        sleep(0.05)
        next
      end
    end
  end
end

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

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

RSpec.configure do |config|
  config.before :all do
    # Forces all threads to share the same connection. This works on
    # Capybara because it starts the web server in a thread.
    ActiveRecord::Base.shared_connection = ActiveRecord::Base.connection
  end

  config.before :each, js: true do
    # Need to wait for active connections because of shared_connection hack
    # Fixes errors like
    # PG::UnableToSend: another command is already in progress
    # undefined method `fields' for nil:NilClass
    # PG::UnableToSend: socket not open
    wait_for_ajax
  end
end

thanks @abgoldstein for the idea

I suggest also to add this to your config/environments/test.rb:

config.eager_load = true

as it helps to avoid wrong exceptions about circular dependency.

@aprescott

The approach given by @mikecmpbll of using Mutex in https://gist.github.com/josevalim/470808#comment-1280898 worked very well.

Without the change, 40 test runs saw 12 failures. With the change, there were 0 failures.

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

raise "adapter was expected to be mysql2" unless ActiveRecord::Base.connection.adapter_name.downcase == "mysql2"

module MutexLockedQuerying
  @@semaphore = Mutex.new

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

Mysql2::Client.prepend(MutexLockedQuerying)

I tweaked it to use prepend instead of aliasing methods, so I can simply call super.

@ardavis
ardavis commented Mar 12, 2015

@divineforest I think that fix worked for the root cause mentioned, but introduced another error for me.

We are using Rails 4, Cucumber with a transactional strategy, we can't change this right now unfortunately. (The really strange part is that on Rails 3.2 this shared connection thing works perfectly.. upgraded to Rails 4.0.11 (soon to 4.1, then 4.2...) and am having issues.

My team has a single model that stores it's data in a separate database. How can I use this shared_connection approach for multiple databases? The error I'm getting now is:

PG::UndefinedTable: ERROR:  relation "user_groups" does not exist
LINE 1: SELECT  "user_groups".* FROM "user_groups"  WHERE "u...
                                     ^
: SELECT  "user_groups".* FROM "user_groups"  WHERE "user_groups"."name" = 'All Groups'  ORDER BY "user_groups"."id" ASC LIMIT 1 (ActiveRecord::StatementInvalid)

Here's my UserGroup and AdminService models:

class UserGroup < AdminService
  self.table_name = 'lcs_user_groups'
  ..
end

class AdminService <  ActiveRecord::Base
  self.abstract_class = true
  establish_connection SERVICES_CONFIG['admin']
end

Here's the services.yml file that SERVICES_CONFIG gets it's data from:

admin: &admin_defaults
  adapter: postgresql
  encoding: unicode
  pool: 5
  host: localhost
  port: 5432
  username: my_username
  password: <%= begin IO.read("$HOME/.db") rescue "" end %>

test_defaults: &test_defaults
  admin:
    <<: *admin_defaults
    database: admin_service_test
    domain: test_domain

test: &test
  <<: *test_defaults

Someone mentioned this issue I'm having in a Railscast comment a long time ago found here:

http://railscasts.com/episodes/257-request-specs-and-capybara?view=comments#comment_157994

The answer in the reply was to do:

class ActiveRecord::Base
  mattr_accessor :shared_connection
  @@shared_connection = {}

  def self.connection
    @@shared_connection[self.connection_config[:database]] ||= retrieve_connection
  end
end

But it only ended up in the following error:

undefined method `[]' for #<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter:0x0000001f827700> (NoMethodError)

Thanks for your time everyone!

@mrsimo
mrsimo commented Mar 18, 2015

We had some problems with the newest versions of connection_pool (from version 2.1.1) and using the semaphore without the connection pool seemed to work. We also connect to more than one database, here's our snippet:

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

  def self.connection
    shared_connections[connection_config[:database]] ||= begin
      retrieve_connection
    end
  end
end

module MutexLockedQuerying
  @@semaphore = Mutex.new

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

Mysql2::Client.prepend(MutexLockedQuerying)
@blueimpb

We had the exact same error with a very plain-vanilla setup using Rails and the "Devise" gem for user session management. Without going to the database in app code at all, we saw lots of:

ActiveRecord::StatementInvalid (Mysql2::Error: This connection is in use by: #<Thread:0x00000004f64740 sleep>: SELECT users.* FROM users WHERE users.id = 13 ORDER BY users.id ASC LIMIT 1): app/controllers/home_controller.rb:12:in `index'

We implemented the fix suggested by mikecmpbll because it seemed very reasonable, essentially forcing Mysql2 to do what the infrastructure above it should already be doing. Problem disappeared. Thanks mikecmpbll!!

@mark-ellul

thank you, muito obrigado, gracias! This has helped me resolve an error that was bugging me!

@Fjan
Fjan commented Jul 28, 2015

We also had some flakiness caused by AJAX requests that arrived after the test had ended. This can actually also lead to heisenbugs without a shared DB connection so instead of waiting for the calls to complete, as in @divineforest's solution, we instead failed the tests and fixed them. This is all that is needed in the test helper to detect tests that need fixing:

class ActionDispatch::IntegrationTest
  def teardown
    # detects both Prototype and jQuery AJAX requests
    active=evaluate_script('window.Ajax ? Ajax.activeRequestCount : (window.jQuery ? jQuery.active : 0)')
    assert_equal 0,active,'Active AJAX request after test end'
  end
end
@mariusandra

I'd been getting PG::UnableToSend another command is already in progress errors when running a few concurrent AJAX requests upon loading a page. One of them would pass, the others would get a 500 with the aforementioned error.

The simple solution for me was to add config.allow_concurrency = false to config/environments/test.rb.

@oneamtu
oneamtu commented Sep 10, 2015

@mikecmpbll's solution with @aprescott's improvement, implemented for postgres. Thanks! 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)
@snoblenet

Hi there,

Currently the following code, in Sidekiq worker, works in development:

url = "http://localhost:3000/downloads/#{download.unique_id}" if Rails.env.development?

However, the following code in the same Sidekiq worker does not work in test:

url = "http://localhost:4000/downloads/#{download.unique_id}" if Rails.env.test?

I get a connection refused error.

Capybara is configured as default_port = 4000 and run_server = true.

Do you think I could fix this with a variant of the code you've posted here?

@grillermo

I gave up, and i'm using truncation in all my tests and the problem es gone.

@kuroda
kuroda commented Mar 4, 2016

The solution using Mutex (thanks to @mikecmpbll) and wait_for_ajax (thanks to @divineforest) works well for me.

Here is another tip from me.

In some occation, the undefined method 'fields' error can occur even if you call wait_for_ajax after triggering an Ajax call.

In fact, we have to wait for all database accesses are completed, not just for ajax.

Suppose that you have following scenario among others:

scenario 'Update account' do
  visit edit_account_path
  fill_in 'account_name', with: 'foo'
  find('#update-account').click # Triggers an Ajax call
  wait_for_ajax
  user.reload
  expect(user.name).to eq('foo')
end

If any database access occurs after this scenario ends, we will get the undefined method 'fields' error on the next scenario.

To prevent this problem, we have to write another expectation that is fulfilled only after all database accesses are completed.

For example, if we know that a text appears on the browser screen at the end of scenario, we can write like this:

scenario 'Update account' do
  visit edit_account_path
  fill_in 'account_name', with: 'foo'
  find('#update-account').click # Triggers an Ajax call
  expect(page).to have_text('The account is updated.')
  user.reload
  expect(user.name).to eq('foo')
end
@jwg2s
jwg2s commented Jul 18, 2016

@kuroda - have you found a more systematic way to fix this issue? We're experiencing the same thing, and rather than run around fixing a bunch of tests, ideally there's a lower level way to address the problem.

@lakim
lakim commented Sep 9, 2016

We should work on a reliable solution for everyone.

The best take I've seen so far is @iangreenleaf's article and gem:
http://technotes.iangreenleaf.com/posts/the-one-true-guide-to-database-transactions-with-capybara.html
https://github.com/iangreenleaf/transactional_capybara

It bundles the shared connection monkey patch and the wait_for_ajax helper.
Version 0.1.0 has just been released with support for Capybara >= 2.6.0.

It doesn't use connection_pool nor a Mutex.
I haven't had the need for it so far, but it's just a PR away.

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