| 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 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.
Description: alter table may change primary key value when changing from non auto_increment column to auto_increment column. How to repeat: test1: mysql> CREATE TABLE x( a int primary key, b varchar(1)); Query OK, 0 rows affected (1.25 sec) mysql> insert into x values(0,'a'),(2,'b') ; Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from x; +---+------+ | a | b | +---+------+ | 0 | a | | 2 | b | +---+------+ 2 rows in set (0.00 sec) mysql> alter table x modify b varchar(5); Query OK, 0 rows affected (0.08 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, MySQL will preserve 0 value to the new table. However, when doing following operation, the 0 value will be changed to 1: test2: mysql> create table y ( a int primary key, b varchar(5)); Query OK, 0 rows affected (0.032 sec) mysql> insert into y values (0,'a'),(2,'b'); Query OK, 2 rows affected (0.004 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from y; +---+------+ | a | b | +---+------+ | 0 | a | | 2 | b | +---+------+ 2 rows in set (0.000 sec) mysql> alter table y modify column a int auto_increment; Query OK, 2 rows affected (0.095 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from y; +---+------+ | a | b | +---+------+ | 1 | a | | 2 | b | +---+------+ 2 rows in set (0.001 sec) test1 alter table preserves 0 values, but test2 not. Suggested fix: The problem lies in function mysql_alter_table (sql/sql_table.cc). /* If we are going to copy contents of one auto_increment column to another auto_increment column it is sensible to preserve zeroes. This condition also covers case when we are don't actually alter auto_increment column. */ if (def->field == from->found_next_number_field) thd->variables.sql_mode |= MODE_NO_AUTO_VALUE_ON_ZERO; Above code only preserve 0 value when altering table from auto_increment column to auto_increment column. It does not cover altering table from non auto_increment column to auto_increment column, like test2. Following is my suggested fix. When new table has auto_increment column, MODE_NO_AUTO_VALUE_ON_ZERO should be enabled by MySQL automatically. --- sql_table2.cc 2021-05-26 15:20:41.974362582 +0800 +++ sql_table.cc 2021-05-26 15:01:18.592772749 +0800 @@ -17925,8 +17925,8 @@ This condition also covers case when we are don't actually alter auto_increment column. */ - if (def->field == from->found_next_number_field) - thd->variables.sql_mode |= MODE_NO_AUTO_VALUE_ON_ZERO; + //if (def->field == from->found_next_number_field) + thd->variables.sql_mode |= MODE_NO_AUTO_VALUE_ON_ZERO; } (copy_end++)->set(*ptr, def->field, false);