故障案例--在线ddl的一个bug

来源：互联网发布：网络教研平台编辑：程序博客网时间：2024/06/06 17:10

现象

mysql> ALTER TABLE mabang_order.`Db_Order4` ADD `trackNumber1` VARCHAR(45) DEFAULT NULL COMMENT '内部单号' AFTER `trackNumber`;
ERROR 1062 (23000): Duplicate entry '8026379' for key 'PRIMARY'
处理方法：

实际上不存在什么主键冲突，忽略它再来一发可能就好了

mysql> ALTER TABLE mabang_order.`Db_Order4` ADD `trackNumber1` VARCHAR(45) DEFAULT NULL COMMENT '内部单号' AFTER `trackNumber`;
Query OK, 0 rows affected (3 min 26.51 sec)
Records: 0 Duplicates: 0 Warnings: 0

故障原因

在线ddl的一个bug，在5.6.28以后的版本中已经修复

https://bugs.mysql.com/bug.php?id=77572

5.6在线添加列时虽然属于in-place YES，但也还是会有copy table的操作，但可以并发执行dml。在这个情况下允许dml操作，ddl结束之前会用online log记录这段时间的增量修改，导致了这个主键冲突的bug

处理方案

1 多试几次，不一定复现

2 pt工具修改

3 显示指定ddl模式为copy，即退化成ddl期间阻塞写操作，而不阻塞读操作

Any ALTER TABLE operation run with theALGORITHM=COPY clause prevents concurrent DML
operations. Concurrent queries are still allowed. That is, a table-copying operation always includes at
least the concurrency restrictions of LOCK=SHARED (allow queries but not DML). You can further restrict
concurrency for such operations by specifying LOCK=EXCLUSIVE (prevent DML and queries).

0 0