| Bug #113614 | The partiton table's AUTOINC back to older after change partitions ddl executed. | ||
|---|---|---|---|
| Submitted: | 11 Jan 2024 8:20 | Modified: | 11 Jan 2024 9:24 |
| Reporter: | zechao zhuang | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | mysql-5.6.51,mysql-5.7.44 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | auto increment, partition table | ||
[11 Jan 2024 8:26]
zechao zhuang
Modify the tags.
[11 Jan 2024 8:31]
zechao zhuang
Add the mtr case result:
==============================================================================
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
#
# Test of Add partition with insert operation
#
CREATE TABLE t0
(a int auto_increment,
b int,
c varchar(64),
PRIMARY KEY (a))
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 2;
INSERT INTO t0 (b,c) VALUES (1,"common"),(2,"common"),(3,"common"),
(4,"common"),(5,"common"), (6,"common"),(7,"common"),(8,"common"),
(9,"common"),(10,"common");
SHOW CREATE TABLE t0;
Table Create Table
t0 CREATE TABLE `t0` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` varchar(64) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (a)
PARTITIONS 2 */
SELECT MAX(a) FROM t0;
MAX(a)
10
INSERT INTO t0 SELECT 0, b + 10, c FROM t0;
ALTER TABLE t0 ADD PARTITION PARTITIONS 1;
SHOW CREATE TABLE t0;
Table Create Table
t0 CREATE TABLE `t0` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` varchar(64) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (a)
PARTITIONS 3 */
SELECT MAX(a) FROM t0;
MAX(a)
20
[ 50%] innodb.partition_change_with_write_operation [ fail ]
Test ended at 2024-01-11 15:27:59
CURRENT_TEST: innodb.partition_change_with_write_operation
mysqltest: At line 40: query 'INSERT INTO t0 (b,c) VALUES (1,"common")' failed: 1062: Duplicate entry '11' for key 'PRIMARY'
safe_process[34689]: Child process: 34690, exit: 1
- the logfile can be found in '/disk1/zechao.zzc/mysql-server/mysql-test/var/log/innodb.partition_change_with_write_operation/partition_change_with_write_operation.log'
[100%] shutdown_report [ pass ]
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 3 seconds executing testcases
Completed: Failed 1/2 tests, 50.00% were successful.
Failing test(s): innodb.partition_change_with_write_operation
The log files in var/log may give you some hint of what went wrong.
If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html
mysql-test-run: *** ERROR: there were failing test cases
[11 Jan 2024 9:24]
MySQL Verification Team
Hello zechao zhuang,
Thank you for the report and feedback.
I can confirm that 5.7.44 is affected but this issue is not seen in 8.0.35.
IMHO - Per Oracle's Lifetime Support policy, as of October 25, 2023, MySQL 5.7 is covered under Oracle Sustaining Support. Thus, Users are encouraged to upgrade to MySQL 8.0. - More details at https://www.mysql.com/support/eol-notice.html
- 5.7 - affected
./mtr --suite=innodb partition_change_with_write_operation --nocheck-testcases
Logging: ./mtr --suite=innodb partition_change_with_write_operation --nocheck-testcases
MySQL Version 5.7.44
Checking supported features...
- SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/export/home/tmp/ushastry/mysql-5.7.44/mysql-test/var'...
Installing system database...
Using parallel: 1
==============================================================================
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
#
# Test of Add partition with insert operation
#
CREATE TABLE t0
(a int auto_increment,
b int,
c varchar(64),
PRIMARY KEY (a))
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 2;
INSERT INTO t0 (b,c) VALUES (1,"common"),(2,"common"),(3,"common"),
(4,"common"),(5,"common"), (6,"common"),(7,"common"),(8,"common"),
(9,"common"),(10,"common");
SHOW CREATE TABLE t0;
Table Create Table
t0 CREATE TABLE `t0` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` varchar(64) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (a)
PARTITIONS 2 */
SELECT MAX(a) FROM t0;
MAX(a)
10
INSERT INTO t0 SELECT 0, b + 10, c FROM t0;
ALTER TABLE t0 ADD PARTITION PARTITIONS 1;
SHOW CREATE TABLE t0;
Table Create Table
t0 CREATE TABLE `t0` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` varchar(64) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (a)
PARTITIONS 3 */
SELECT MAX(a) FROM t0;
MAX(a)
20
[ 50%] innodb.partition_change_with_write_operation [ fail ]
Test ended at 2024-01-11 10:21:27
CURRENT_TEST: innodb.partition_change_with_write_operation
mysqltest: At line 39: query 'INSERT INTO t0 (b,c) VALUES (1,"common")' failed: 1062: Duplicate entry '11' for key 'PRIMARY'
-- 8.0.35 - not affected
./mtr --suite=innodb partition_change_with_write_operation --nocheck-testcases
Logging: ./mtr --suite=innodb partition_change_with_write_operation --nocheck-testcases
MySQL Version 8.0.35
Checking supported features
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.35/mysql-test/var'
Installing system database
Using parallel: 1
==============================================================================
TEST NAME RESULT TIME (ms) COMMENT
------------------------------------------------------------------------------
#
# Test of Add partition with insert operation
#
CREATE TABLE t0
(a int auto_increment,
b int,
c varchar(64),
PRIMARY KEY (a))
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 2;
INSERT INTO t0 (b,c) VALUES (1,"common"),(2,"common"),(3,"common"),
(4,"common"),(5,"common"), (6,"common"),(7,"common"),(8,"common"),
(9,"common"),(10,"common");
SHOW CREATE TABLE t0;
Table Create Table
t0 CREATE TABLE `t0` (
`a` int NOT NULL AUTO_INCREMENT,
`b` int DEFAULT NULL,
`c` varchar(64) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 2 */
SELECT MAX(a) FROM t0;
MAX(a)
10
INSERT INTO t0 SELECT 0, b + 10, c FROM t0;
ALTER TABLE t0 ADD PARTITION PARTITIONS 1;
SHOW CREATE TABLE t0;
Table Create Table
t0 CREATE TABLE `t0` (
`a` int NOT NULL AUTO_INCREMENT,
`b` int DEFAULT NULL,
`c` varchar(64) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 3 */
SELECT MAX(a) FROM t0;
MAX(a)
20
INSERT INTO t0 (b,c) VALUES (1,"common");
SHOW CREATE TABLE t0;
Table Create Table
t0 CREATE TABLE `t0` (
`a` int NOT NULL AUTO_INCREMENT,
`b` int DEFAULT NULL,
`c` varchar(64) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 3 */
SELECT MAX(a) FROM t0;
MAX(a)
21
drop table t0;
[ 50%] innodb.partition_change_with_write_operation [ pass ] 172
[100%] shutdown_report [ pass ]
regards,
Umesh
Description: We found that after executing the DDL to modify the partition on the partition table, like add a new partition, return a 1062（duplicate key）error code in the next insert SQL. After checking the code logic, found that the cause of this problem is that the create_info information of the partition table has been constructed in the prepare phase, and create_info->auto_increment_value has been filled in it. The share lock held in the prepare phase and the lock level is MDL_SHARED_UPGRADABLE. Before the share lock upgraded to MDL_SHARED_NO_WRITE, if there are write requests from other user threads was executed,and modify the autoinc value of this partition table, and the auto_increment_value in create_info was still be older value, that will cause the new add partition in the subsequent alter table phase to use an older autoinc value too, and the autoinc will back to older after the add partition DDL was done. And this problem will be happend at all chage partitons opertions, like ADD/DROP/COALESCE/REORGANIZE PARTITION. How to repeat: Use this mtr case will be repeat this problem. Test name: mysql-server/mysql-test/suite/innodb/t/partition_change_with_write_operation.test Repeat: ./mtr innodb.partition_change_with_write_operation mtr case content: --source include/have_innodb.inc --echo # --echo # Test of Add partition with insert operation --echo # # Create table t0 CREATE TABLE t0 (a int auto_increment, b int, c varchar(64), PRIMARY KEY (a)) ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 2; # Prepare data INSERT INTO t0 (b,c) VALUES (1,"common"),(2,"common"),(3,"common"), (4,"common"),(5,"common"), (6,"common"),(7,"common"),(8,"common"), (9,"common"),(10,"common"); # Check the autoinc, the autoinc will be advanced to 11 SHOW CREATE TABLE t0; SELECT MAX(a) FROM t0; # Create a tmp_conn and send the insert SQL, the autoinc would be advanced # to 21 after this insert was executed done. --connect(tmp_conn,127.0.0.1,root,,test,$SERVER_MYPORT_1) --connection tmp_conn send INSERT INTO t0 SELECT 0, b + 10, c FROM t0; # Add new partition for t0, this DDL will lead the autoinc to be back to 11 --connection default ALTER TABLE t0 ADD PARTITION PARTITIONS 1; # Check the autoinc, the autoinc is 11, but max autoinc field is 20 SHOW CREATE TABLE t0; SELECT MAX(a) FROM t0; # The next insert will be failed at duplicate key 11 INSERT INTO t0 (b,c) VALUES (1,"common"); SHOW CREATE TABLE t0; SELECT MAX(a) FROM t0; drop table t0; Suggested fix: If the partition table have autoinc field, we need reload the autoinc value after upgrade the share lock to MDL_SHARED_UPGRADABLE. The Fix code on mysql-5.7.44 like this: diff --git a/sql/sql_table.cc b/sql/sql_table.cc index db9c1ce..747437a 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -9474,6 +9474,18 @@ bool mysql_alter_table(THD *thd, const char *new_db, const char *new_name, DBUG_RETURN(true); } + /* + If the origin table has autoinc field, we need reload the autoinc after + upgrade the shared lock to MDL_SHARED_NO_WRITE lock. + */ + uint used_fields = create_info->used_fields; + if (!(used_fields & HA_CREATE_USED_AUTO) && table->found_next_number_field) + { + /* Table has an autoincrement, copy value to new table */ + table->file->info(HA_STATUS_AUTO); + create_info->auto_increment_value = table->file->stats.auto_increment_value; + } + char* table_name= const_cast<char*>(alter_ctx.table_name); deprecation_silencer.pop(); if (is_partitioned)