CODEWITHSUNDEEP
ruby-on-railsunit-testingdatabase-cleaner
Matrix
Matrix

Reputation: 3369

DatabaseCleaner don't reset autoincrement index in rails test unit

test/test_helper.rb :

ENV["RAILS_ENV"] ||= "test"
require File.expand_path('../../config/environment', __FILE__)
require 'rails/test_help'
require 'database_cleaner'

DatabaseCleaner.strategy = :transaction
DatabaseCleaner.clean_with(:truncation, pre_count: true, reset_ids: true)

class ActiveSupport::TestCase
  ActiveRecord::Migration.check_pending!


  def setup
    DatabaseCleaner.start
  end


  def teardown
    DatabaseCleaner.clean
    p '-------- DB Cleaned ---------'
  end

end

My test unit file : (test1 and 2 are a duplicate)

require 'test_helper'

class ItemTest < ActiveSupport::TestCase

  test "test1" do
    i = Item.create!

    p ActiveRecord::Base.connection.execute("SELECT auto_increment FROM information_schema.tables WHERE table_schema = 'tmi_game_test' AND table_name = 'items';").first

    assert_equal 1, Item.count
    assert_equal 1, i.id
  end

  test "test2" do
    i = Item.create!

    p ActiveRecord::Base.connection.execute("SELECT auto_increment FROM information_schema.tables WHERE table_schema = 'tmi_game_test' AND table_name = 'items';").first

    assert_equal 1, Item.count
    assert_equal 1, i.id
  end

end

result :

# Running:

[2]
"-------- DB Cleaned ---------"
.[3]
"-------- DB Cleaned ---------"
F

Finished in 0.142886s, 13.9972 runs/s, 27.9944 assertions/s.

  1) Failure:
ItemTest#test_test2 [test/models/item_test.rb:45]:
Expected: 1
  Actual: 2

2 runs, 4 assertions, 1 failures, 0 errors, 0 skips

Why dosn't works? where is my mistake?

Upvotes: 3

Views: 1812

Answers (2)

Sam Adams
Sam Adams

Reputation: 1

This is a rather old post but I happened on it while looking for ways to swap from truncation over to deletion strategy. I was doing this because the truncation strategy was bringing our specs to a crawl. It worked fine with a smallish number of tables, we've grown to 44 tables and now its getting slow.

Ultimately Matouš' earlier comment that you should never rely on an auto_increment ID value in your tests is correct. If I could go back in time and tell the person who changed to truncation strategy not to do it then I would. Alas I cannot time travel and fixing our specs so that ids don't matter at this point in time is a monumental task. I'm just looking to speed up our specs, so I devised the below solution.

To get this to work I've put the below in my spec_helper, this will only work for Mysql:

    require 'database_cleaner/active_record/truncation'
    require 'database_cleaner/active_record/deletion'
    module DatabaseCleaner
      module ActiveRecord
        class Deletion < Truncation
          def delete_table connection, table_name
            connection.execute("DELETE FROM #{connection.quote_table_name(table_name)}")
            connection.execute("ALTER TABLE #{table_name} AUTO_INCREMENT = 1;")
          end
        end
      end
    end

If you want this to work with postgres replace:

    connection.execute("ALTER TABLE #{table_name} AUTO_INCREMENT = 1;")

With

connection.reset_pk_sequence!(table_name)

If you want this to work with sqlite replace with:

    connection.execute("delete from sqlite_sequence where name='#{table_name}';")

I've also created a PR to make this a strategy option: https://github.com/DatabaseCleaner/database_cleaner-active_record/pull/71

But I suspect this will get rejected since this is all bad not good. If you must reset ids do it for individual specs.

Upvotes: 0

Matouš Bor&#225;k
Matouš Bor&#225;k

Reputation: 15944

This is expected behavior. You are using the :transaction strategy to clean up tables. This means that each test is wrapped inside a transaction which is ROLLBACK-ed after the test (during teardown).

You have not stated which database you use, but ROLLBACK does not reset AUTO_INCREMENT values, neither in MySQL (see bug #6714) nor in PostgreSQL (see bug #1139).

In accordance with this SO answer I think that you should never rely on an auto_increment ID value in your tests. I think you should test other attributes instead to assert that you are working with the expected record.

If you really need to reset your AUTO_INCREMENT counters, use the :truncation cleaning strategy instead. I.e. remove the clean_with line and just set the strategy to :truncation. It is much slower than transactions though.

Upvotes: 5

Related Questions