RDS ERROR 1062 && online DDL

最新推荐文章于 2024-11-01 12:20:35 发布

weixin_30328063

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量199

点赞数

文章标签：数据库操作系统嵌入式

原文链接：http://www.cnblogs.com/Kid-Zhou/p/8108716.html

版权

一、RDS ERROR 1062

1.背景：

数据库：RDS-MySQL5.6；业务表trade，该业务表的数据量是百万级别的，表字段已有70个左右，索引已有十几个，有唯一性索引unique_idx(uniq1,uniq2)

2.现象：

给trade表加字段（因为mysql5.6的版本已支持online-DDL，所以白天执行加字段的sql，之前测试下来对业务影响不大）sql如下：

1 ALTER TABLE `trade`
2 ADD COLUMN `col1` BIGINT(20) DEFAULT NULL,
3 ADD COLUMN `col2` BIGINT(20) DEFAULT NULL,
4 ADD COLUMN `col3` VARCHAR(20) DEFAULT NULL;

但是执行过程中，多个库都出现报错，报错大致如下：

错误代码： 1062
Duplicate entry '69950493617-27155840530' for key 'unique_idx'

3.分析：

1>报错显示是唯一性索引出现重复数据，于是首先去trade表中查询是否出现重复数据，sql如下：

SELECT DISTINCT(uniq1),COUNT(uniq2) cnt FROM trade GROUP BY uniq1 HAVING cnt >1 ORDER BY cnt DESC;

没有查询到重复数据（显然是不会有重复数据的，因为建表之初就是存在唯一性索引的，不可能有重复数据呀）

2>查询到如下网址： http://blog.csdn.net/mchdba/article/details/76695325

以上网址中解释如下：这是RDS实例的bug，是由于该表的DML很多导致磁盘碎片化严重，同时该表是业务表，实时数据操作也很多，所以导致线上执行alter table异常报错。同时网址中也提到了rds官方提供的技术支持，可以使用Percona Toolkit三方工具。

3>限制性：由于我们的rds是基于聚石塔，没有操作系统的权限，另外考虑到在ecs上使用第三方工具操作rds，可能会有网络影响。

4>考虑到mysql5.6新增了 online DDL特性（以前新增字段的话，MySQL5.5会锁表，后升级到MySQL5.6），于是分析一下online DDL的原理

二、online DDL

1.MySQL5.1之前的版本实现DDL操作简单原理：

1>对表table1执行DDL时，会先给表table1建一张临时表table1_tmp,该临时表的表结构是（DDL操作后）新定义的表结构

2>对table1进行锁表操作，然后将table1中的数据copy到table1_tmp

3>对table1 进行rename为table2，同时对table1_tmp进行rename为table1，然后删除掉table2

老版本的操作带来的最大问题是，表的数据量越大，copy表数据的时间越长，导致锁表时间越长，对客户的影响就越大。（互联网行业一般是维持7*24小时的不间断服务，但是执行这些会锁表的操作会选择业务低峰期，但这样人为花费的精力就蛮大的）

2.MySQL5.1（with innodb plugin）和MySQL5.5及以上的版本

这些版本中出现了一个新特性 Fast Index Creation （快速创建索引），该特性能加快alter table table1 add/drop key idx的消耗时间，这个主要是节省在copy table这个步骤上，创建二级索引时对table1加上一个s锁，创建过程不需要重建table1_tmp，删除innodb二级索引只需要更新内部视图，并标记这个索引的空间可用，去掉数据库元数据上该索引的定义。这个特性也是需要锁表操作的，但是加快了修改索引的速度

（注意：主键索引的修改仍然需要copy table，是因为innodb IOT（索引组织表）特性）

3.MySQL5.6 online DDL三个阶段

参考如下网址： http://www.linuxidc.com/Linux/2016-08/134763.htm

三、解决RDS加字段可能会带来的1062 Duplicate error

采用如下sql：

1 ALTER TABLE `trade`
2 ADD COLUMN `col1` BIGINT(20) DEFAULT NULL,
3 ADD COLUMN `col2` BIGINT(20) DEFAULT NULL,
4 ADD COLUMN `col3` VARCHAR(20) DEFAULT NULL,
5 lock=shared;

#lock=shared的话，会对原表进行锁表操作，所以需要在业务低峰期执行以上sql。另外也可以使用copy算法来设置，达到同样的效果。

1 ALTER TABLE `trade`
2 ADD COLUMN `col1` BIGINT(20) DEFAULT NULL,
3 ADD COLUMN `col2` BIGINT(20) DEFAULT NULL,
4 ADD COLUMN `col3` VARCHAR(20) DEFAULT NULL,
5 ALGORITHM=COPY;