DatabaseCleaner don't reset autoincrement index in rails test unit

Question

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?

Answer 1

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.