MySQL Auto-increment Column

Fact: MySQL keeps its auto-increment counter in memory for Innodb table, this gets initialised when the database starts. The number that gets assigned when the database gets loaded in the memory is found by for e.g. in the id column by max(id)+1 or 1 if empty. Each time you add a record with a NULL value for the auto-increment column, this value in the counter is assigned and counter is incremented again by 1. More on How AUTO_INCREMENT Handling Works in InnoDB.

Some one in the #rubyonrails found it as a problem. He/she had removed the record with the biggest value, restarted the database and the id gets recycled. As per the log story, record was removed but only to be found again. Their solution was to keep a counter on a table, set it manually, and all the dance that you have to do with rails when you mess with the id attribute.

In fact, if they had done it properly, i.e. using dependency options in associations; removing the biggest record and its id being recycled should not be a problem at all. But you know its never a programmers’ fault. In the end I suggested him/her to set a flag to the record that it is unusable instead of removing it. But hey, will they listen? No, it is cool to mess with id column.

Leave a Comment