0

I have about 100 specs, In a Sinatra project I'm currently developing. For testing I'm currently using rspec + DatabaseCleaner with the :transaction strategy.

Some of those specs require creating new records in the database and thus they increase the auto increment table value in mysql. Is there a way I can run my tests with the transaction strategy, without increasing the auto increment value using DatabaseCleaner or another gem?

Edit: I would like for auto_increment to have the value it had before the tests ran.

Nelo
  • 145
  • 10
  • what value you want in your auto_increment column.... – Zafar Malik Mar 25 '16 at 04:32
  • @ZafarMalik The value it had before the tests ran. – Nelo Mar 25 '16 at 04:49
  • If you are passing your own id in auto_increment column then it will take your own passed value instead of auto_id...so you don't should be worry for it...but make sure that id should not exist in table...if you are trying to say something else then please clear. – Zafar Malik Mar 25 '16 at 04:54
  • @ZafarMalik I'm not pasing an ID to auto_increment. When my tests run some of them insert records into the DB and then they are deleted, however the auto_increment value doesn't roll back. – Nelo Mar 25 '16 at 05:02

2 Answers2

2

Reset the value to some known value after tests. Have a look here...

How to reset AUTO_INCREMENT in MySQL?

Community
  • 1
  • 1
Harry
  • 11,298
  • 1
  • 29
  • 43
1

I am assuming that at the time of test no record will insert from production or outside test.

First get last auto_id from your table by below command-

select max(id) from mytable;

Suppose it was 240125

Now start your test and after completion your test do as per below-

First delete all newly created auto_ids-

delete from mytable where id>240125;

Now set auto_increment from this possition.

alter table mytable AUTO_INCREMENT=240126;
Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • This looks good. Is there a way It could be done only for the tables that had insertions? – Nelo Mar 25 '16 at 13:43
  • as you can set auto_increment value at table level so its on you for which table you want to reset auto_increment....in your case...you can find out tables where you think insertion will be done....you can also automate this process by stored procedure suppose before your test save max auto_id in a temp table...then store same id after your test...and compare where 2nd id > 1st time stored id then delete all records more than 1st time insert ids only tables where 2nd id > 1st id and reset auto_increment id = 1st time saved id + 1.... – Zafar Malik Mar 28 '16 at 04:25