Description:
With inplace algorithm, the auto_increment is starting over for each partition.
With copy algorithm, the auto_increment is not repeating itself. See output of testcase:
mysql> alter table t add column r int unsigned not null auto_increment, add unique key (r,b), algorithm=inplace, lock=shared;
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t;
+---+----+---+
| a | b | r |
+---+----+---+
| a | 0 | 1 |
| b | 1 | 2 |
| c | 2 | 1 |
| d | 3 | 2 |
| e | 4 | 3 |
| f | 5 | 4 |
| g | 25 | 1 |
| h | 35 | 1 |
+---+----+---+
8 rows in set (0.00 sec)
-- versus --
mysql> alter table t add column r int unsigned not null auto_increment, add unique key (r,b), algorithm=copy, lock=shared;
Query OK, 8 rows affected (0.10 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from t;
+---+----+---+
| a | b | r |
+---+----+---+
| a | 0 | 1 |
| b | 1 | 2 |
| c | 2 | 3 |
| d | 3 | 4 |
| e | 4 | 5 |
| f | 5 | 6 |
| g | 25 | 7 |
| h | 35 | 8 |
+---+----+---+
8 rows in set (0.00 sec)
How to repeat:
drop table if exists t;
create table t (a varchar(10) not null,b int,primary key (b)) engine=innodb
partition by range (b)
(partition pa values less than (2),
partition pb values less than (20),
partition pc values less than (30),
partition pd values less than (40));
insert into t values('a',0),('b',1),('c',2),('d',3),('e',4),('f',5),('g',25),('h',35);
alter table t add column r int unsigned not null auto_increment, add unique key (r,b), algorithm=inplace, lock=shared;
select * from t;
select version();
-- versus --
drop table if exists t;
create table t (a varchar(10) not null,b int,primary key (b)) engine=innodb
partition by range (b)
(partition pa values less than (2),
partition pb values less than (20),
partition pc values less than (30),
partition pd values less than (40));
insert into t values('a',0),('b',1),('c',2),('d',3),('e',4),('f',5),('g',25),('h',35);
alter table t add column r int unsigned not null auto_increment, add unique key (r,b), algorithm=copy, lock=shared;
select * from t;
select version();
Description: With inplace algorithm, the auto_increment is starting over for each partition. With copy algorithm, the auto_increment is not repeating itself. See output of testcase: mysql> alter table t add column r int unsigned not null auto_increment, add unique key (r,b), algorithm=inplace, lock=shared; Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t; +---+----+---+ | a | b | r | +---+----+---+ | a | 0 | 1 | | b | 1 | 2 | | c | 2 | 1 | | d | 3 | 2 | | e | 4 | 3 | | f | 5 | 4 | | g | 25 | 1 | | h | 35 | 1 | +---+----+---+ 8 rows in set (0.00 sec) -- versus -- mysql> alter table t add column r int unsigned not null auto_increment, add unique key (r,b), algorithm=copy, lock=shared; Query OK, 8 rows affected (0.10 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> select * from t; +---+----+---+ | a | b | r | +---+----+---+ | a | 0 | 1 | | b | 1 | 2 | | c | 2 | 3 | | d | 3 | 4 | | e | 4 | 5 | | f | 5 | 6 | | g | 25 | 7 | | h | 35 | 8 | +---+----+---+ 8 rows in set (0.00 sec) How to repeat: drop table if exists t; create table t (a varchar(10) not null,b int,primary key (b)) engine=innodb partition by range (b) (partition pa values less than (2), partition pb values less than (20), partition pc values less than (30), partition pd values less than (40)); insert into t values('a',0),('b',1),('c',2),('d',3),('e',4),('f',5),('g',25),('h',35); alter table t add column r int unsigned not null auto_increment, add unique key (r,b), algorithm=inplace, lock=shared; select * from t; select version(); -- versus -- drop table if exists t; create table t (a varchar(10) not null,b int,primary key (b)) engine=innodb partition by range (b) (partition pa values less than (2), partition pb values less than (20), partition pc values less than (30), partition pd values less than (40)); insert into t values('a',0),('b',1),('c',2),('d',3),('e',4),('f',5),('g',25),('h',35); alter table t add column r int unsigned not null auto_increment, add unique key (r,b), algorithm=copy, lock=shared; select * from t; select version();