RSpec’s transactional db clearing was inadequate for us. We are using FactoryGirl rather than RSpec’s built-in mocks framework, so none of those records were getting cleared out of the database. There’s a simple way to clear out your entire MySQL database before each test, ensuring that each test has a clean slate to start with. Drop this into your spec_helper in the Spec::Runner configuration:

config.before :each do
  (ActiveRecord::Base.connection.tables - %w{schema_migrations}).each do |table_name|
    ActiveRecord::Base.connection.execute "TRUNCATE TABLE #{table_name};"
  end
end

When I first put this in, however, I got the following error on nearly every controller test:

ActiveRecord::StatementInvalid in 'MongoUtil should sum platform play counts correctly with MongoUtil.sum'
Mysql::Error: SAVEPOINT active_record_1 does not exist: ROLLBACK TO SAVEPOINT active_record_1
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/activerecord-2.3.10/lib/active_record/connection_adapters/abstract_adapter.rb:227:in `log'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/activerecord-2.3.10/lib/active_record/connection_adapters/mysql_adapter.rb:324:in `execute'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/activerecord-2.3.10/lib/active_record/connection_adapters/mysql_adapter.rb:366:in `rollback_to_savepoint'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/activerecord-2.3.10/lib/active_record/connection_adapters/abstract/database_statements.rb:167:in `transaction'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/activerecord-2.3.10/lib/active_record/transactions.rb:182:in `transaction'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/activerecord-2.3.10/lib/active_record/transactions.rb:200:in `save!'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/activerecord-2.3.10/lib/active_record/transactions.rb:208:in `rollback_active_record_state!'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/activerecord-2.3.10/lib/active_record/transactions.rb:200:in `save!'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/factory_girl-1.2.4/lib/factory_girl/proxy/create.rb:6:in `result'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/factory_girl-1.2.4/lib/factory_girl/factory.rb:323:in `run'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/factory_girl-1.2.4/lib/factory_girl/factory.rb:267:in `create'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/factory_girl-1.2.4/lib/factory_girl/factory.rb:298:in `send'
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/factory_girl-1.2.4/lib/factory_girl/factory.rb:298:in `default_strategy' 
/Users/andy/.rvm/gems/ruby-1.8.7-p302@heyzap/gems/factory_girl-1.2.4/lib/factory_girl.rb:21:in `Factory'
./spec/models/mongo_util_spec.rb:11:

I had a lot of trouble tracking down exactly what was going on here, since ActiveRecord’s own use of Savepoints is pretty well tested. Eventually I figured out that RSpec’s transaction caching and management was not friendly with making Base.connection.execute calls. Running any dirty SQL totally hosed RSpec’s savepoints, and RSpec just could not deal with that.

Of course, since we’re doing our own db cleaning now, RSpec shouldn’t need to set any save points. So, I just turned them off:

config.use_transactional_fixtures = false

Problem solved.