Bug #103858 | alter table may change primary key value | ||
---|---|---|---|
Submitted: | 31 May 2021 5:12 | Modified: | 1 Jun 2021 3:38 |
Reporter: | Chengyao Jiang | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
Version: | OS: | Any | |
Assigned to: | CPU Architecture: | Any |
[31 May 2021 5:12]
Chengyao Jiang
[31 May 2021 8:17]
MySQL Verification Team
Duplicate of https://bugs.mysql.com/bug.php?id=103784 just changed the test case.
[31 May 2021 12:22]
MySQL Verification Team
Hi Mr. Jiang, Thank you for your bug report. However, we have found out that your bug is a duplicate of the following bug: https://bugs.mysql.com/bug.php?id=103784
[1 Jun 2021 3:38]
Chengyao Jiang
Sorry, I can not add comment for the old post. So I file a new one. I found I give a wrong code for test1. It should be: test1: mysql> show create table x\G *************************** 1. row *************************** Table: x Create Table: CREATE TABLE `x` ( `a` int NOT NULL AUTO_INCREMENT, `b` varchar(1) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.00 sec) mysql> select * from x; +---+------+ | a | b | +---+------+ | 0 | a | | 2 | b | +---+------+ 2 rows in set (0.00 sec) mysql> alter table x modify column b varchar(5); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from x; +---+------+ | a | b | +---+------+ | 0 | a | | 2 | b | +---+------+ 2 rows in set (0.00 sec) As you can see zero value will preserve after altering table when auto_increment option already exists. The MySQL source code really preserve 0 value for auto_increment column. In addtion, for alter table tool, like gh-ost, pt-osc, they will preserve zero value when altering table from non-auto_increment to auto_increment. What they do is to enable NO_AUTO_VALUE_ON_ZERO automatically before inserting old value to table. See: https://github.com/github/gh-ost/issues/722 So I suggest MySQL do the same step as gh-ost and pt-osc do so as to preserve the old value when altering table.