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?

score 5 · Accepted Answer · edited May 23 '17 at 11:50

5

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.

edited May 23 '17 at 11:50

Community

1
1

answered Mar 25 '16 at 16:39

Matouš Borák

15,606
1
42
53

`DatabaseCleaner.clean_with(:truncation, pre_count: true, reset_ids: true)` config to use truncation, no?? – Matrix Mar 25 '16 at 16:41
Yes, but `clean_with` cleans the db immediately where it is called, i.e. once before all tests in your case. But the AUTO_INCREMENTs are not reset **after each test**. – Matouš Borák Mar 25 '16 at 16:49
ok... I had don't understand that. So what's better, call `clean_with(:truncation...)` in teardown method, or change strategy to :truncation ? What's the difference? – Matrix Mar 25 '16 at 16:56
Practically it would probably be about the same, but the standard way is to set the strategy. I.e. remove the `clean_with` line and just set the strategy to `:truncation`. And that should be it. – Matouš Borák Mar 25 '16 at 17:00

score 0 · Answer 2 · answered Apr 01 '22 at 00:41

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.

Also you need to have `gem 'database_cleaner-active_record'` in your gem file, not `gem 'database_cleaner'` — Sam Adams, Apr 01 '22 at 01:10

DatabaseCleaner don't reset autoincrement index in rails test unit

2 Answers2