Mysql::Error: SAVEPOINT active_record_1 does not exist: ROLLBACK TO SAVEPOINT active_record_1

Sorry, a bit more information for ths ticket.

It seems to be happening on AR exectute statements calls to TRUNCATE:

e.g.

ActiveRecord::Base.connection.execute("TRUNCATE factsheets")

Can you show more of your logs? Are the savepoints being defined?

Unfortunately yesterday was also the day I chose to clean up my huge log files.

If you can't replicate it in your environment, let me know and I'll restore the code and try to generate a log file for you. If you can replicate it there's probably not much point in my doing it.

Thanks for the quick response.

Regards, Nicholas

A basic test seems to work ok:

Person::Note.transaction do
  Person::Note.transaction :requires_new => true do
    ActiveRecord::Base.connection.execute("TRUNCATE person_notes")
  end
end

Runs:

SQL (0.2ms)  BEGIN
SQL (0.1ms)  SAVEPOINT active_record_1
SQL (3.0ms)  TRUNCATE person_notes
SQL (0.4ms)  RELEASE SAVEPOINT active_record_1
SQL (0.2ms)  COMMIT

Without the transaction?

It appears that TRUNCATE is a DDL statement, http://dev.mysql.com/doc/refman/..., and therefore causes an implicit commit. The docs already warn against using DDL statements inside savepoints.

Person::Note.transaction do
  ActiveRecord::Base.connection.execute("TRUNCATE person_notes")
  Person::Note.transaction :requires_new => true do
  end
end

Can you simply use DELETE FROM instead of TRUNCATE?

Short answer - yes.

This code was appearing in our test code, and was probably added quickly without thought. It's not something we need supported. I wanted to point out this error was occurring though.

Thanks for the quick response.

N.

No worries, let's close this off then.

Is it possible to close this as something like 'Wont's Fix' instead of invalid? This is a real issue.

Found the same issue while running some rspec tests, but...

in activerecord-2.3.1/lib/active_record/transactions.rb:182 there's the following note:

=== Caveats

If you're on MySQL, then do not use DDL operations in nested transactions blocks that are emulated with savepoints. That is, do not execute statements like 'CREATE TABLE' inside such blocks. This is because MySQL automatically releases all savepoints upon executing a DDL operation. When #transaction is finished and tries to release the savepoint it created earlier, a database error will occur because the savepoint has already been automatically released. The following example demonstrates the problem: Model.connection.transaction do # BEGIN

Model.connection.transaction(:requires_new => true) do  # CREATE SAVEPOINT active_record_1
  Model.connection.create_table(...)                    # active_record_1 now automatically released
end                                                     # RELEASE savepoint active_record_1
                                                        # ^^^^ BOOM! database error!

end

(posted this just to help people that will google to here)

I'm not sure how this should best be fixed, so for now I've documented this caveat in docrails: http://github.com/lifo/docrails/...

This bug appears on shoulda tests too. 20% of my tests fail with that strange error. How can I fix it?
Non transactional tests are unacceptable cos them are slow. What should I do?

stanislav, do not use DDL statements in your tests or code if you want to use transactions to rollback fixtures.

This is not a "won't fix" -- it's simply not fixable because most databases do not support transactional DDL statements.

Uh, I was dreaming about "do nothing during migration to rails 2.3.x", Actually, I heave a strong filling that this errors cos shoulda contexts, but thnx anyway. Will digg my code.

Just wanted to add some comments that might be useful for people experiencing this with Rspec + Cucumber
basically... turn off transaction fixtures... its the only way I have found to avoid this error in your tests.

Then use a global before(:each) in the Rspec & Cucumber config files/helpers that uses DatabaseCleaner

Rspec

ruby

config.after(:each) {

DatabaseCleaner.clean

}

Cucumber

ruby

After('@no-transaction') do DatabaseCleaner.clean end

sorry syntax fail - no preview sucks :(

  # rspec
  config.after(:each) {
    DatabaseCleaner.clean
  }

# cucumber After do

DatabaseCleaner.clean

end