MySQL Bugs: #103784: column value seems to be wrong when modifying column to auto

Bug #103784	column value seems to be wrong when modifying column to auto_increment
Submitted:	24 May 2021 4:42	Modified:	27 May 2021 7:30
Reporter:	Chengyao Jiang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
After changing column to auto_increment, the column value will set to 1 from 0.

How to repeat:
mysql> create table x ( a bigint primary key );
Query OK, 0 rows affected (0.50 sec)

mysql> insert into x values (-1),(0),(2);
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from x;
+----+
| a  |
+----+
| -1 |
|  0 |
|  2 |
+----+
3 rows in set (0.00 sec)

mysql> ALTER TABLE x MODIFY COLUMN a bigint auto_increment;
Query OK, 3 rows affected (2.16 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from x;
+----+
| a  |
+----+
| -1 |
|  1 |
|  2 |
+----+
3 rows in set (0.00 sec)

NO_AUTO_VALUE_ON_ZERO can control the auto_increment behavior.
So when doing alter table, NO_AUTO_VALUE_ON_ZERO should be set automatically.

mysql> set sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.04 sec)

mysql> create table x ( a bigint primary key );
Query OK, 0 rows affected (0.47 sec)

mysql> insert into x values (-1),(0),(2);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from x;
+----+
| a  |
+----+
| -1 |
|  0 |
|  2 |
+----+
3 rows in set (0.00 sec)

mysql> ALTER TABLE x MODIFY COLUMN a bigint auto_increment;
Query OK, 3 rows affected (2.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from x;
+----+
| a  |
+----+
| -1 |
|  0 |
|  2 |
+----+
3 rows in set (0.00 sec)

Because MySQL can create auto_increment value by NULL or 0, I strongly suggest that when doing alter table, NO_AUTO_VALUE_ON_ZERO should be set internally by MySQL, not by session/global variables.

Hi Mr. Jiang,

Thank you for your bug report.

However, it is not a bug. What you are reporting is described in our Reference Manual:

https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

Regarding the sql_mode settings, MySQL always tries to do what it has been asked to do by the user, whether it is DBA or a developer. This is why it could not be accepted even as a feature request. That change would have broken many existing applications, without any good reason.

Hi, guys

ALTER TABLE x MODIFY COLUMN a bigint auto_increment;

What I mean is that under any circumstances, above DDL  should only change data structure, not the value in the row.

If there is a 0 value in the table x, it should not be change to 1 after dong DDL.

For sql_mode, it only affects how to insert auto_increment value behavior.

Hence, ALTER TABLE ... MODIFY COLUMN ... auto_increment need to enable NO_AUTO_VALUE_ON_ZERO by MySQL internally, not DBA or any developer.

Later, I will show my fix.

Hi,

After I review the source code , MySQL did handle the auto_increment column problem when dong alter table. 

Please see the comment and source code in 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;

But the code only covers 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 the example I show before.

Hence, it is really a bug. Please fix it asap.

Followings are my fix. When new table has auto_increment column, sql_mode should enable  MODE_NO_AUTO_VALUE_ON_ZERO 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);

Now it works fine for me:

mysql> create table x ( a bigint primary key );
Query OK, 0 rows affected (0.50 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into x values (-1),(0),(2);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from x;
+----+
| a  |
+----+
| -1 |
|  0 |
|  2 |
+----+
3 rows in set (0.00 sec)

mysql> ALTER TABLE x MODIFY COLUMN a bigint auto_increment;
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from x;
+----+
| a  |
+----+
| -1 |
|  0 |
|  2 |
+----+
3 rows in set (0.01 sec)

Hi Mr. Jiang,

Sorry, but MySQL has, in many cases, change the value of the tuple. If you change signed to unsigned, you can not have negative values. Same for many other conversion. The same logic applies for auto-inc, but that is why we allow that users configure MySQL, with sql_mode and other settings.

Not a bug.

Hi,

Maybe I did not explain the problem clearly.

It is not related to the variables sql_mode at all.

The problem is about ALTER TABLE operation will change primary key value from 0 to  1.

And from the source code， MySQL has already handle this problem. Please see the source code 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;

From the source code, we can see that MySQL will preserve zero value. But it miss the scenario when changing from 0 column value to 0 auto_increment column value.

In our production environment, we encounter this problem after dong alter table, which has caused disaster for the business. Because the primary key changed from 0 to 1.

From my point of view, my fix will not change anything on current application running on MySQL, it only change the result for alter table from wrong to right.

In addtion, the document innodb-auto-increment-handling.html does not write anything related to my report. 

I suggest one or more members of MySQL team can review this problem. 

It is really a big problem if anyone does not realize the operation of alter table can change primary key value.

wrong category