Will you loose data?

I can understand why you might be worried when you see unexplained gaps in the primary key values — when I first saw it happen in one of my database tables, the first thing I was wondering was if I was loosing data, and I even spent a few hours investigating the behavior.

Missing auto increment values are not necessarily an indication that a query failed to insert data — it could just be that the data already existed in the table. That is technically still a failure, but it is an expected failure.

People have tried to avoid it by using on duplicate key update id=id, but this will not prevent the auto_increment value from incrementing when using InnoDB.

One way to prevent it from happening is to first perform a select query to see if the row already exists. But that would open up a small time gap for someone to insert the data between the select and the insert statement; so it would not completely eliminate the problem, and sometimes it might actually lead to even worse problems.

Will you run out of keys?

Another thing someone might worry about is whether they will run out of primary key numbers. With an INT you got more than 2 billion values (2,147,483,647).

Of course, if you are creating something like a HTTP request logger, you would theoretically run out of keys faster. But the problem here is not the key — the problem is that you are using the wrong tool for the job.

A request log is rarely cleared, and the auto increment value is almost never reset. This means you will run out of values at some point. Only entries older than a year are typically deleted.

A way around this would be to use BIGINT; this would at least delay the issue years into the future.

With BIGINT you would not even run out if every single human on the planet hit your site a million times — within a single second!

It will very likely crash the internet, but you would still have more than nine quintillion left before you would finally run out.

The maximum size of an unsigned BIGINT is 9,223,372,036,854,775,807; if every single human hit a button a million times concurrently, you would only be down just about 7,000,000,000,000,000 (seven quadrillion)

What happens when you run out?

This may depend on the database, but it is actually very easy to test with a smaller datatype. So, let us create a test table using TINYINT.

Of course, with INT there is also a real possibility you might run out of numbers. But it is still unlikely it will actually happen. In fact, if you have just a single VARCHAR column in the same table, and every single row included a field with 255 characters, it would already mean your table had grown to 68 Gigabytes; so as you can see, it is unlikely but not impossible.

When numbers are skipped, you would of course not take up that much space before running out of keys. This is why you may want to do something about it before it actually happens. A TINYINT has just 127 available unsigned keys. You can create a table to see what happens:

create table `test_table` (`id` tinyint not null auto_increment, `whatever` varchar(255) not null, primary key (`id`), unique key `whatever` (`whatever`)) engine=InnoDB auto_increment=127;

Note the auto_increment=127 part; this tells MySQL that the auto increment should begin from this number. If we then try to execute a couple of insert queries:

insert into test_table (whatever) values ('Doing it');
insert into test_table (whatever) values ('Not doing it');

It will result in an error like:

Duplicate entry '127' for key 'test_table.PRIMARY'

And the table will simply be blocked from having more rows added until we change the datatype.

alter table test_table modify id int;

Or, if you think you will need it, use BIGINT:

alter table test_table modify id bigint;

Tell us what you think: