Bug #92241 alter partitioned table add auto_increment diff result depending on algorithm
Submitted: 30 Aug 2018 6:29 Modified: 14 Feb 2019 19:28
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.6, 5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any

[30 Aug 2018 6:29] Shane Bester
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();
[14 Feb 2019 19:28] Jon Stephens
Documented fix as follows in the MySQL 5.6.44, 5.7.26, and 8.0.16 changelogs:

    An AUTO_INCREMENT key added to a partitioned table by an ALTER
    TABLE statement using ALGORITHM=INPLACE restarted on each
    partition.

Closed.