MySQL Bugs: #108834: MySQL Online DDL add AUTO

Bug #108834	MySQL Online DDL add AUTO_INCREMENT
Submitted:	20 Oct 2022 11:05	Modified:	4 Nov 2022 14:14
Reporter:	HULONG CUI	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	all version	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment

Description:
Case one:
i have  two server master and replica,using employees database.
I use online ddl command to add auto increment columnto employees table.
when  update master employees rows, replica have error not found key.
to check employees table emp_no column, same emp_no but auto increment column value not match.

Case two:
one mysql server ，same employee table to add auto increment column.
same emp_no auto increment column value is not match.

How to repeat:
sql:
use employees;
alter table employees01 add id bigint(11) primary key AUTO_INCREMENT; 
alter table employees02 add id bigint(11) primary key AUTO_INCREMENT; 

select e1.emp_no  
from employees01 e1,employees02 e2 
where e1.emp_no=e2.emp_no and e1.id<>e2.id  limit 1;

##################
mysql> alter table employees01 add id bigint(11) primary key AUTO_INCREMENT;
Query OK, 0 rows affected, 1 warning (2.24 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> alter table employees02 add id bigint(11) primary key AUTO_INCREMENT; 
Query OK, 0 rows affected, 1 warning (2.19 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> select * from employees01 where emp_no=404619 ; select * from employees02 where emp_no=404619;
+--------+------------+------------+-----------+--------+------------+-------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | id    |
+--------+------------+------------+-----------+--------+------------+-------+
| 404619 | 1957-12-02 | Shigehiro  | Walston   | M      | 1992-06-08 | 32603 |
+--------+------------+------------+-----------+--------+------------+-------+
1 row in set (0.01 sec)

+--------+------------+------------+-----------+--------+------------+------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | id   |
+--------+------------+------------+-----------+--------+------------+------+
| 404619 | 1957-12-02 | Shigehiro  | Walston   | M      | 1992-06-08 | 6619 |
+--------+------------+------------+-----------+--------+------------+------+
1 row in set (0.00 sec)

Suggested fix:
I think this is a bug. auto increment fields should be generated in a hidden primary key sort and should not be upgraded randomly。

This is a fatal problem online ddl in high availability mode

Hi,

As for the "Case2" - this is not a bug. There is nothing in SQL standard that forces the order of unordered table nor there is anything in the SQL standard forcing order in auto increment field so adding auto increment field to two tables does not guarantee same order.

With regards to replication, I will verify this as a bug as I did not find this case in documentation as part of limitation of the AUTO INCREMENT and replication but I believe this will just be better documented, no change in behavior will most probably happen.

Thanks for the report

master [localhost:22031] {msandbox} (employees) > create table emp01 like `employees`;
Query OK, 0 rows affected (0.01 sec)

master [localhost:22031] {msandbox} (employees) > alter table emp01 drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

master [localhost:22031] {msandbox} (employees) > insert into emp01 select * from employees;
Query OK, 300024 rows affected (3.80 sec)
Records: 300024  Duplicates: 0  Warnings: 0

master [localhost:22031] {msandbox} (employees) > alter table emp01 add id bigint(11) primary key AUTO_INCREMENT;
Query OK, 0 rows affected, 1 warning (1.42 sec)
Records: 0  Duplicates: 0  Warnings: 1

master [localhost:22031] {msandbox} (employees) > select * from emp01 where emp_no > 404618 order by emp_no limit 5;
+--------+------------+------------+-----------+--------+------------+--------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | id     |
+--------+------------+------------+-----------+--------+------------+--------+
| 404619 | 1957-12-02 | Shigehiro  | Walston   | M      | 1992-06-08 | 204316 |
| 404620 | 1958-07-11 | Patricio   | Munos     | F      | 1995-12-25 | 204318 |
| 404621 | 1962-12-14 | Billur     | Tempesti  | F      | 1993-02-20 | 204320 |
| 404622 | 1958-11-06 | Lubomir    | Mersereau | M      | 1990-09-19 | 204322 |
| 404623 | 1960-04-17 | Honesty    | Samarati  | M      | 1994-09-25 | 204324 |
+--------+------------+------------+-----------+--------+------------+--------+
5 rows in set (0.13 sec)

And now on Slave:
slave1 [localhost:22032] {msandbox} (employees) > select * from emp01 where emp_no > 404618 order by emp_no limit 5;
+--------+------------+------------+-----------+--------+------------+--------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | id     |
+--------+------------+------------+-----------+--------+------------+--------+
| 404619 | 1957-12-02 | Shigehiro  | Walston   | M      | 1992-06-08 | 204295 |
| 404620 | 1958-07-11 | Patricio   | Munos     | F      | 1995-12-25 | 204296 |
| 404621 | 1962-12-14 | Billur     | Tempesti  | F      | 1993-02-20 | 204297 |
| 404622 | 1958-11-06 | Lubomir    | Mersereau | M      | 1990-09-19 | 204298 |
| 404623 | 1960-04-17 | Honesty    | Samarati  | M      | 1994-09-25 | 204299 |
+--------+------------+------------+-----------+--------+------------+--------+
5 rows in set (0.13 sec)

slave1 [localhost:22032] {msandbox} (employees) >

Posted by developer:
 
This problem has always existed in replication, so setting back status to verified.

Indeed, ALTER TABLE ADD COLUMN ... AUTO_INCREMENT is unsafe for replication. The workaround is to first add an unindexed BIGINT column, then update every row of the table so that the value in the new column is unique, then add a unique index and add the auto_increment attribute on the column.

I think there are three things to do:
1. Document the problem and the workaround.
2. Short-term solution: File a feature request to disallow the unsafe statement.
3. Long-term solution: File a feature request to make it possible to replicate this statement.

I'll file the feature requests in separate bugs, and keep the current bug as a documentation bug.

Posted by developer:
 
Filed the following to track the enhancement requests:

BUG#34763852: Disallow ALTER TABLE ... ADD COLUMN ... AUTO_INCREMENT with binary log enabled
BUG#34763870: Replicate ALTER TABLE ... ADD COLUMN ... AUTO_INCREMENT deterministically

Long documented at https://dev.mysql.com/doc/refman/8.0/en/replication-features-auto-increment.html -- not a bug.