How to reset auto increment field in a ActiveRecord migration?

Question

In my migration I have:

def up
   MyModel.destroy_all
   MyModel.create!({:id=>1,:name=>'foo'})
   MyModel.create!({:id=>2,:name=>'fooBar'})
   MyModel.create!({:id=>3,:name=>'fooNull'})
end

because I need to override data that was already on my_models table

But Even though I'm specifying the id on MySQL it continues the numbering from the position it already was.

I need to rest the counter on the auto increment for id to have only this 3 new records with that id values trough Active Record migration on my Ruby on Rails application.

Answer 1

You have 2 separate issues. One is that you are trying to specify the id with mass assignment, rails won't allow you to do that. See Overriding id on create in ActiveRecord for a way to do that.

The other issue is that the auto-increment isn't resetting. Each DBMS has a unique way of setting an increment counter, and rails doesn't give you a generic way to access them, although it is implemented for some of them (not MySQL), see Rails way to reset seed on id field

So you'll need to run the MySQL specific SQL for this, which is something like:

ALTER TABLE my_models AUTO_INCREMENT = 1;

This should reset to the number after the largest id in your table (1 if there aren't any)