Bug #101309 the partition table add AUTO_INCREMENT column can‘t incremant
Submitted: 26 Oct 3:47 Modified: 27 Oct 21:11
Reporter: Lihan Chen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.7.25-log MySQL Community Server (GPL) OS:CentOS
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: auto_increment, partition table

[26 Oct 3:47] Lihan Chen
Description:
my mysql database Server version is  5.7.25-log MySQL Community Server (GPL).
when I add a partition table's   AUTO_INCREMENT  column then found the column can't auto incremt.

How to repeat:
such as :

table_schema :

SQL>CREATE TABLE `test_add_auto_intable` (
  `t_date` char(8) DEFAULT NULL,
  `o_no` varchar(32) NOT NULL ,
  `c_code` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(t_date)
(
 PARTITION p_20181219 VALUES LESS THAN ('20181219') ENGINE = InnoDB,       
 PARTITION p_20181220 VALUES LESS THAN ('20181220') ENGINE = InnoDB,       
 PARTITION p_20181221 VALUES LESS THAN ('20181221') ENGINE = InnoDB,       
 PARTITION p_20181222 VALUES LESS THAN ('20181222') ENGINE = InnoDB
 ...);     

then I add a parimay key with auto_incermant:

SQL> ALTER TABLE test_add_auto_intable ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD PRIMARY KEY (id,t_date);

then I found table max(id) is not accord with count(*)maximum,such as:
SQL> select min(id),max(id) from test_add_auto_intable;
+---------+---------+
| min(id) | max(id) |
+---------+---------+
|       1 |     814 |
+---------+---------+

SQL> select count(*) from test_add_auto_intable;
+----------+
| count(*) |
+----------+
|    12214 |
+----------+

In the finaly, I also test in the mysql server version 5.7.30 and 5.6.16 ,neither version has this problem !

Suggested fix:
1、upgrade the version 
2、After adding self adding columns, copy and import the data into the new table, and then rename it,such as
SQL> insert into xxx_new(...) values(...)
[26 Oct 3:57] Lihan Chen
The data is like this,id column discontinuous growth:
id      t_date
1	20181129
1	20181201
1	20181203
1	20181204
1	20181205
1	20181206
1	20190107
1	20190429
1	20200502
1	20200503
1	20200504
1	20200505
1	20200506
1	20200507
1	20200508
1	20200509
1	20200510
1	20200511
1	20200512
1	20200513
1	20200514
1	20200515
1	20200516
1	20200517
1	20200518
1	20200519
1	20200520
1	20200521
1	20200522
1	20200523
1	20200524
1	20200525
1	20200526
1	20200527
1	20200528
1	20200529
1	20200530
1	20200531
1	20200601
1	20200602
1	20200603
1	20200604
1	20200605
1	20200606
1	20200607
1	20200608
1	20200609
1	20200610
1	20200611
1	20200612
1	20200613
1	20200614
1	20200615
1	20200616
1	20200617
1	20200618
1	20200619
1	20200620
1	20200621
1	20200622
1	20200623
1	20200624
1	20200625
1	20200626
1	20200627
1	20200628
1	20200629
1	20200630
1	20200701
1	20200702
1	20200703
1	20200704
1	20200705
1	20200706
1	20200707
1	20200708
1	20200709
1	20200710
1	20200711
1	20200712
1	20200713
1	20200714
1	20200715
1	20200716
1	20200717
1	20200718
1	20200719
1	20200720
1	20200721
1	20200722
1	20200723
1	20200724
1	20200725
1	20200726
1	20200727
1	20200728
1	20200729
1	20200730
1	20200731
1	20200801
1	20200802
1	20200803
1	20200804
1	20200805
1	20200806
1	20200807
1	20200808
1	20200809
1	20200810
1	20200811
1	20200812
1	20200813
1	20200814
1	20200815
1	20200816
1	20200817
1	20200818
1	20200819
1	20200820
1	20200821
1	20200822
1	20200823
1	20200824
1	20200825
1	20200826
1	20200827
1	20200828
1	20200829
1	20200830
1	20200831
1	20200901
1	20200902
1	20200903
1	20200904
1	20200905
1	20200906
1	20200907
1	20200908
1	20200909
1	20200910
1	20200911
1	20200912
1	20200913
1	20200914
1	20200915
1	20200916
1	20200917
1	20200918
1	20200919
1	20200920
1	20200922
1	20200923
1	20200924
1	20200925
1	20200926
1	20200927
1	20200928
1	20200929
1	20200930
1	20201001
1	20201002
1	20201003
1	20201004
1	20201005
1	20201006
1	20201007
1	20201008
1	20201009
1	20201010
1	20201011
1	20201012
1	20201013
1	20201014
1	20201015
1	20201016
1	20201017
1	20201018
1	20201019
1	20201020
1	20201021
1	20201022
1	20201023
1	20201024
2	20181129
2	20181203
2	20181204
2	20181205
2	20181206
2	20190107
2	20190429
2	20200502
2	20200503
2	20200504
2	20200505
2	20200506
2	20200507
2	20200508
2	20200509
2	20200510
2	20200511
2	20200512
2	20200513
2	20200514
2	20200515
2	20200516
2	20200517
2	20200518
2	20200519
2	20200520
2	20200521
2	20200522
2	20200523
2	20200524
2	20200525
2	20200526
2	20200527
2	20200528
2	20200529
2	20200530
2	20200531
2	20200601
2	20200602
2	20200603
2	20200604
2	20200605
2	20200606
2	20200607
2	20200608
2	20200609
2	20200610
2	20200611
2	20200612
2	20200613
2	20200614
2	20200615
2	20200616
2	20200617
2	20200618
2	20200619
2	20200620
2	20200621
2	20200622
2	20200623
2	20200624
2	20200625
2	20200626
2	20200627
2	20200628
2	20200629
2	20200630
2	20200701
2	20200702
2	20200703
2	20200704
2	20200705
2	20200706
2	20200707
2	20200708
2	20200709
2	20200710
2	20200711
2	20200712
2	20200713
2	20200714
2	20200715
2	20200716
2	20200717
2	20200718
2	20200719
2	20200720
2	20200721
2	20200722
2	20200723
2	20200724
2	20200725
2	20200726
2	20200727
2	20200728
2	20200729
2	20200730
2	20200731
2	20200801
2	20200802
2	20200803
2	20200804
2	20200805
2	20200806
2	20200807
2	20200808
2	20200809
2	20200810
2	20200811
2	20200812
2	20200813
2	20200814
2	20200815
2	20200816
2	20200817
2	20200818
2	20200819
2	20200820
2	20200821
2	20200822
2	20200823
2	20200824
2	20200825
2	20200826
2	20200827
2	20200828
2	20200829
2	20200830
2	20200831
2	20200901
2	20200902
2	20200903
2	20200904
2	20200905
2	20200906
2	20200907
2	20200908
2	20200909
2	20200910
2	20200911
2	20200912
2	20200913
2	20200914
2	20200915
2	20200916
2	20200917
2	20200918
2	20200919
2	20200920
2	20200922
2	20200923
2	20200924
2	20200925
2	20200926
2	20200927
2	20200928
2	20200929
2	20200930
2	20201001
2	20201002
2	20201003
2	20201004
2	20201005
2	20201006
2	20201007
2	20201008
2	20201009
2	20201010
2	20201011
2	20201012
2	20201013
2	20201014
2	20201015
2	20201016
2	20201017
2	20201018
2	20201019
2	20201020
2	20201021
2	20201022
2	20201023
2	20201024
3	20181129
3	20181203
3	20181204
3	20181205
3	20181206
3	20190107
3	20190429
3	20200502
3	20200503
3	20200504
3	20200505
3	20200506
3	20200507
3	20200508
3	20200509
3	20200510
3	20200511
3	20200512
3	20200513
3	20200514
3	20200515
3	20200516
3	20200517
3	20200518
3	20200519
3	20200520
3	20200521
3	20200522
3	20200523
3	20200524
3	20200525
3	20200526
3	20200527
3	20200528
3	20200529
3	20200530
3	20200531
3	20200601
3	20200602
3	20200603
3	20200604
3	20200605
3	20200606
3	20200607
3	20200608
3	20200609
3	20200610
3	20200611
3	20200612
3	20200613
3	20200614
3	20200615
3	20200616
3	20200617
3	20200618
3	20200619
3	20200620
3	20200621
3	20200622
3	20200623
3	20200624
3	20200625
3	20200626
3	20200627
3	20200628
3	20200629
3	20200630
3	20200701
3	20200702
3	20200703
[27 Oct 21:11] MySQL Verification Team
Hi,

As you said, this is already fixed in newer version. 

Not sure what would you expect us to do about already released version? You have to upgrade.

all best
Bogdan