How is the MySQL auto_increment step size determined

Question

I have a table of web pages, with the primary key as hash of the URL, and an auto_increment ID column, which is also a unique key.

What I'm a bit confused by is why successive inserts don't increment the ID field by 1. When I first created the table and did a single insert, the first id was 1. The second insert produced and id of 5 and the third was 8.

I have a trigger on the table which, on insert, computes the hash of the URL of the webpage. Not sure if that's relevant or not.

It's not a problem to have gaps, but I would like to understand why successive inserts don't generate IDs with a step size of 1.

thanks!

can you post the code of the trigger? – Ivan Buttinoni Nov 24 '11 at 22:16 — Ivan Buttinoni, Nov 24 '11 at 22:16

score 8 · Accepted Answer · answered Nov 24 '11 at 22:10

8

Several suggestions of why this may be happening:

See auto_increment_increment. This controls the incrementation each time a new value is requested during INSERT.

Also if you use InnoDB tables in MySQL 5.1, they optimized auto-inc allocation so that it locks the table for a shorter duration. This is good for concurrency, but it can also "lose" auto-inc values if the INSERT of a row conflicts with another constraint such as a secondary UNIQUE column or a foreign key. In those cases, the auto-inc value allocated is not pushed back into the queue, because we assume another concurrent thread may have already allocated the next auto-inc value.

Of course rollbacks also occur, in which case an auto-inc value may be allocated but discarded.

answered Nov 24 '11 at 22:10

Bill Karwin

538,548
86
673
828

Thanks Bill. I played around with it a bit more and it looks like it's the duplicate entries that cause the gaps (by which I mean attempting to do an insert with a duplicate primary key). When doing recrawling of webpages (to update dynamic content), this is probably going to leave quite a few gaps. I suppose that isn't a huge problem so long as I don't run out of integer values! :) – Vijay Boyapati Nov 24 '11 at 22:24
2

I did some consulting earlier this year for a site that ran out of values in their 32-bit integer primary key due to this issue. They had a secondary UNIQUE column that was causing inserts to fail 1000 times for every 1 success. So their auto-inc values were increasing by hundreds, and by greater amounts the more rows they had (more chance for duplicates). We had to ALTER TABLE to change their primary keys to BIGINT (along with the foreign key columns in 30+ dependent tables). – Bill Karwin Nov 24 '11 at 22:35
1

`SHOW SESSION VARIABLES;` to see the current value of auto_increment_increment – Brian Low Mar 31 '17 at 14:13

score 3 · Answer 2 · answered Nov 24 '11 at 22:10

It could be related to transactions that end up getting rolled back. For example,

Insert google.com id=5
Insert mysql.com id=6
Insert stackoverflow.com id = 7
rollback insert google.com
rollback insert mysql.com

Then stackoverflow.com is inserted with id=7 and 5 and 6 are left blank.

score 1 · Answer 3 · edited Nov 24 '11 at 22:15

The auto increment is always with steps of 1, but once a row gets removed, the ID is not freed up.

The next auto increment ID is stored in the information schema database in your MySQL environment. It is always +1, and when a row gets removed, the ID in between will be missing. So if the first is 1, and the second (according to you) is 5, then 2,3,4 have been removed in the process.

Run this query to find out, and replace the last 2 values in the where ;)

SELECT AUTO_INCREMENT
from `information_schema`.`TABLES`
WHERE   TABLE_NAME = '<<YOUR TABLE NAME HERE>>' AND
      TABLE_SCHEMA = '<< YOUR DATABASE NAME HERE >>'

score 1 · Answer 4 · answered Nov 25 '11 at 09:34

1

mysql.cnf / ini can set the change:

auto-increment-increment = 2
auto-increment-offset = 1

In some configuration (for example, for master/master replication), a_i can skip numbers using these variables.

answered Nov 25 '11 at 09:34

Moshe L

1,797
14
19

score 0 · Answer 5 · answered Aug 29 '20 at 10:48

SET GLOBAL auto_increment_offset=1;

SET GLOBAL auto_increment_increment=5;

auto_increment_offset: interval between successive column values auto_increment_offset: determines the starting point for the AUTO_INCREMENT column value. The default value is 1.

more info here

How is the MySQL auto_increment step size determined

5 Answers5

Linked