indexing - MySQL reuse missed primary key int values when maxed out

Question

One of our tables unexpectedly ran out of Ids. A quick look at the table I can see not all Ids have been used. I assume this where we may have deleted data for reasons.

I've been reading that the nature of mysql's auto_inc field means it always goes +1 for the next Id.

Is there a way to tell it to use unused Ids?

I've been reading here : https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html But I'm not a super DB dev, and I don't really get if this will do what I'm hoping for.

Our first plan is to change the Id to be Unsigned() and gain an extra couple billion rows. But this is a quick fix to a problem that we shouldnt be near yet. The table is in constant use, in production, but our system does have a "retry" concept. So if this table is out of action for a bit, that is fine for us.

Any pointers or ideas would be greatly appreciated. Cheers

question from:https://stackoverflow.com/questions/65938855/mysql-reuse-missed-primary-key-int-values-when-maxed-out

Answer 1

To answer your question:

No, there is no way to tell MySQL's auto-increment to generate values that "fill in the gaps" in the table.

Even if you try to alter the table to reset the next increment value, it will immediately adjust itself up to the current MAX(id)+1. It will not generate an auto-increment value less than the max auto-increment currently in the table.

You can do an INSERT to override the auto-increment and specify a value yourself. But it's up to you to find the values that are unused.

That introduces race conditions. Even if you check for an unused value, some other client could grab it the instant after you read that, before you use it in an INSERT.

To avoid the race condition, you'd have to lock the whole table, to prevent another client from inserting. Then your session could do a query to find a missing value, then insert, then unlock the table.

There are several good reasons for the limitation of auto-increment not filling in gaps.

• It simplifies the internal implementation. It only needs to keep track of one value per table. Simpler implementation usually means less chance for bugs.

• There is no table-lock needed to search for the next value. Allocating the next value is implemented by a brief mutex lock. This allows many concurrent clients to insert freely, without waiting.

• There might have been a good reason for the gap.

This scenario is a bit complicated, but it illustrates that such scenarios could exist:

Suppose the id 12345 in a users table corresponds to a user who was banned for sending harassing emails to other users. Their account was prohibited and banned, and the row in the users table for their account was deleted. Later, if you find that gap and assume it was never used, so you allocate that id 12345 to the next new user. They now unfortunately are associated with the id of that banned user.

Then one of the original abusive emails is discovered by someone who checks their email infrequently. The recipient complains about user 12345. But that id now belongs to the new innocent user, and they don't deserve to be accused as sending harassing emails.