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:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any

[31 May 2021 5:12] Chengyao Jiang
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);
[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.