mysql踩坑-replace into操作导致Duplicate entry错误异常发现原因排查本地复现总结参考

60 阅读 0 评论 40 点赞

我是靠谱客的博主落寞咖啡豆，最近开发中收集的这篇文章主要介绍mysql踩坑-replace into操作导致Duplicate entry错误异常发现原因排查本地复现总结参考，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

异常发现

近期运营反馈后台数据有无新数据产生，通过分析生产环境日志，发现插入的数据有较多主键已存在的错误，具体报错信息如下：

### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '821159' for key 'PRIMARY'
### The error may exist in xxx.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '821159' for key 'PRIMARY'
; Duplicate entry '821159' for key 'PRIMARY'; nested exception is java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '821159' for key 'PRIMARY'
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:243)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
    at com.sun.proxy.$Proxy106.insert(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:57)
    at com.sun.proxy.$Proxy133.create(Unknown Source)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '821159' for key 'PRIMARY'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)

原因排查

通过 google 搜索，发现使用 replace into 语法会有2中错误场景，

当执行 repleace into 时，如果遇到 unique key 冲突，mysql执行的是delete-insert两步操作，但是binlog只会保存一条update语句，造成主库中 AUTO_INCREAMENT 进行+1操作。从库不执行该操作，造成mysql主从的 AUTO_INCREMENT 不一致，
当mysql主从切换后，从库变为主库，其中的 AUTO_INCREMENT 较为落后，导致新插入的数据主键可能已经存在数据表中，故导致该异常发生。

本地复现

创建表

* MySQL5.7

* ROW模式


* 表结构
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

step1 初始化记录

mater:lc> REPLACE INTO test (col_1,col_2) values('a','a');
Query OK, 1 row affected (0.00 sec) --注意，这里是影响了1条记录

master:lc> REPLACE INTO test (col_1,col_2) values('b','b');
Query OK, 1 row affected (0.00 sec) --注意，这里是影响了1条记录

master:lc> REPLACE INTO test (col_1,col_2) values('c','c');
Query OK, 1 row affected (0.00 sec) --注意，这里是影响了1条记录


master > show create table test

| test  | CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |


slave > show create table test

| test  | CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

step2 构造错误场景

* master

mater:lc> REPLACE INTO test (col_1,col_2) values('c','cc');
Query OK, 2 rows affected (0.00 sec)  --注意，这里是影响了两条记录

mater:lc> show create table test

| test  | CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

master:lc> select * from test
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  1 | a     | a     |
|  2 | b     | b     |
|  4 | c     | cc    |
+----+-------+-------+
3 rows in set (0.00 sec)

* slave

slave:lc> show create table test

| test  | CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

slave:lc> select * from test
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  1 | a     | a     |
|  2 | b     | b     |
|  4 | c     | cc    |
+----+-------+-------+
3 rows in set (0.00 sec)

step3 错误案例产生

* 假设有一天，master 挂了， 由slave 提升为 new mater
原slave:lc> show create table test
| test  | CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
原slave:lc> select * from test
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  1 | a     | a     |
|  2 | b     | b     |
|  4 | c     | cc    |
+----+-------+-------+
3 rows in set (0.00 sec)
===注意==
root:lc> REPLACE INTO test (col_1,col_2) values('d','d');
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

总结

使用 replace into 语法在低版本做主从或binlog复制的情况下，应当避免使用，否则就会出现AUTO_INCREMENT不一致的情况，使用 ON DUPLICATE KEY UPDATE 替代 replace into ，因为这样它的binlog是update，而非 delete insert 操作。

除了在业务侧使用该方法外，可以通过升级mysql至8.0版本解决该问题，mysql在内核测已进行了优化修复。