| Bug #101309 | the partition table add AUTO_INCREMENT column can‘t incremant | ||
|---|---|---|---|
| Submitted: | 26 Oct 2020 3:47 | Modified: | 27 Oct 2020 21:11 |
| Reporter: | Lihan Chen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| 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 2020 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 2020 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
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(...)