MySQL

做最专业的DBA

RDS 在线DDL诡异报错ERROR 1062 (23000): Duplicate entry

RDS上执行报错如下:

MySQL [ad_billing]> ALTER TABLE ad_spending ADD COLUMN impr bigint(20) NOT NULL DEFAULT 0 COMMENT '曝光数' after  `mall_id`;
ERROR 1062 (23000): Duplicate entry '2017-08-04-3-936158' for key 'scene_date_ad'
MySQL [ad_billing]>


可以确定不会有重复数据,因为scene_date_ad是唯一约束字段,再次验证不是数据的问题,因为没有重复记录

MySQL [ad_billing]> select date,scene_id,ad_id,count(1) from ad_spending group by date,scene_id,ad_id having(count(1)>1)
    -> ;
Empty set (0.43 sec)


google资料,看到如下信息:

When running an online DDL operation, the thread that runs the ALTER TABLE statement applies an “online log” of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the “online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.
“`

这是RDS实例的bug,再结合到这个表之前做过几百万行的delete from table where pkid=xxxxx;的操作,所以导致碎片化比较严重,加上这个表的业务也在实时操作,所以导致线上alter操作异常触发雷区。

然后rds官方技术支持,建议使用pt-online-schema-change去执行alter操作,参考rds如何使用pt组件:https://help.aliyun.com/knowledge_detail/41734.html

阅读更多

扫码向博主提问

去开通我的Chat快问

mchdba

博客专家

非学,无以致疑;非问,无以广识
  • 擅长领域:
  • MySQL Hbas
  • Hbase
  • Tidb
  • cdb
  • 数据拆分
个人分类: - - - MySQL Problems
上一篇ftp上传下载记录
下一篇Python 牵手 MySQL
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭