Why mysql auto_increment skips ids when I `INSERT INTO table () SELECT NULL FROM table`? -

Why mysql auto_increment skips ids when I `INSERT INTO table () SELECT NULL FROM table`? -

i'd understand side-effect of working on.

i wanted create big (2+ million) test table of random integers, ran following:

create table `block_tests` (`id` int(11) default null auto_increment primary key, `num` int(11)) engine=innodb; insert `block_tests` (`num`) values(round(rand() * 1e6)); -- every repeat of line doubles number of rows; insert block_tests (num) select round(rand() * 1e6) block_tests; insert block_tests (num) select round(rand() * 1e6) block_tests; insert block_tests (num) select round(rand() * 1e6) block_tests; -- etc

the table size correctly doubles every iteration. what's unusual ids of rows have been added:

mysql> select * block_tests limit 17; +----+--------+ | id | num | +----+--------+ | 1 | 814789 | | 2 | 84489 | | 3 | 978078 | | 4 | 636924 | | 6 | 250384 | | 7 | 341151 | | 8 | 954604 | | 9 | 749565 | | 13 | 884014 | | 14 | 171375 | | 15 | 204833 | | 16 | 510040 | | 17 | 935701 | | 18 | 148383 | | 19 | 934814 | | 20 | 228923 | | 28 | 340170 | +----+--------+ 17 rows in set (0.00 sec)

for reason, there skips in ids. there's pattern skips:

4 skip 6 - skip 1 9 skip 13 - skip 4 20 skip 28 - skip 8 43 skip 59 - skip 16

what's going on?

maybe answer, side effect of new algorithm called “consecutive“ innodb_autoinc_lock_mode - source

mysql