MySQL Auto Increment--REPLACE INTO导致的自增列主键冲突问题
测试环境描述
MySQL版本： 5.7.26-29-log Percona Server (GPL)
MySQL架构： 主从复制
MySQL参数：
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
+-----------------------------+-------+
准备测试数据
## 删除测试表
DROP TABLE IF EXISTS test1001.tb1001;
## 创建测试表
CREATE TABLE test1001.tb1001(
id INT PRIMARY key auto_increment,
c1 INT NOT NULL,
dt datetime DEFAULT now(),
UNIQUE INDEX idx_c1(c1)
);
## 插入测试数据
INSERT INTO test1001.tb1001(id, c1)
SELECT 1,1
UNION
SELECT 2,2;
## 查看主实例自增ID
SELECT
TABLE_SCHEMA,
TABLE_NAME,
AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'tb1001';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | AUTO_INCREMENT |
+--------------+------------+----------------+
| test1001 | tb1001 | 3 |
+--------------+------------+----------------+
1 row in set (0.00 sec)
## 查看从实例自增ID
SELECT
TABLE_SCHEMA,
TABLE_NAME,
AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'tb1001';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | AUTO_INCREMENT |
+--------------+------------+----------------+
| test1001 | tb1001 | 3 |
+--------------+------------+----------------+
1 row in set (0.00 sec)
测试更新操作
在主实例上使用REPLACE INTO更新数据：
REPLACE INTO test1001.tb1001(c1) VALUES(1);
Query OK, 2 rows affected (0.00 sec)
REPLACE INTO test1001.tb1001(c1) VALUES(2);
Query OK, 2 rows affected (0.00 sec)
主实例自增ID变化为：自增ID从3修改为5
SELECT
TABLE_SCHEMA,
TABLE_NAME,
AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'tb1001';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | AUTO_INCREMENT |
+--------------+------------+----------------+
| test1001 | tb1001 | 5 |
+--------------+------------+----------------+
1 row in set (0.00 sec)
查看从实例自增ID变化：自增ID无变化
SELECT
TABLE_SCHEMA,
TABLE_NAME,
AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'tb1001';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | AUTO_INCREMENT |
+--------------+------------+----------------+
| test1001 | tb1001 | 3 |
+--------------+------------+----------------+
1 row in set (0.00 sec)
直接在从节点上执行插入操作：报主键冲突
INSERT INTO test1001.tb1001(c1)VALUES(4);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
问题原因分析
当REPALCE INTO在主实例执行时，由于唯一索引冲突，REPLACE INTO操作被转换为DELETE+INSERT操作，因此REPALCE INTO插入1条记录最终影响2条记录(2 rows affected)：
REPLACE INTO test1001.tb1001(c1) VALUES(1);
Query OK, 2 rows affected (0.00 sec)
REPLACE INTO test1001.tb1001(c1) VALUES(2);
Query OK, 2 rows affected (0.00 sec)
但在BINLOG中，REPALCE INTO操作生成UPDATE Event：
### UPDATE `test1001`.`tb1001`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=0 is_null=0 */
### @3='2021-12-23 14:56:43' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=0 is_null=0 */
### @3='2021-12-23 14:57:44' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### UPDATE `test1001`.`tb1001`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### @3='2021-12-23 14:56:43' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### @3='2021-12-23 14:57:45' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
MySQL在出路记录更新代码如下：
## storage\innobase\handler\ha_innodb.cc
/**
Updates a row given as a parameter to a new value. Note that we are given
whole rows, not just the fields which are updated: this incurs some
overhead for CPU when we check which fields are actually updated.
TODO: currently InnoDB does not prevent the 'Halloween problem':
in a searched update a single row can get updated several times
if its index columns are updated!
@param[in] old_row Old row contents in MySQL format
@param[out] new_row Updated row contents in MySQL format
@return error number or 0 */
int ha_innobase::update_row(const uchar* old_row, uchar* new_row)
{
...省略部分代码...
/* We need to do some special AUTOINC handling for the following case:
INSERT INTO t (c1,c2) VALUES(x,y) ON DUPLICATE KEY UPDATE ...
We need to use the AUTOINC counter that was actually used by
MySQL in the UPDATE statement, which can be different from the
value used in the INSERT statement. */
if (error == DB_SUCCESS
&& table->next_number_field
&& new_row == table->record[0]
&& thd_sql_command(m_user_thd) == SQLCOM_INSERT
&& trx->duplicates) {
ulonglong auto_inc;
ulonglong col_max_value;
auto_inc = table->next_number_field->val_int();
/* We need the upper limit of the col type to check for
whether we update the table autoinc counter or not. */
col_max_value =
table->next_number_field->get_max_int_value();
if (auto_inc <= col_max_value && auto_inc != 0) {
ulonglong offset;
ulonglong increment;
#ifdef WITH_WSREP
/* Applier threads which are processing
ROW events and don't go through server
level autoinc processing, therefore
m_prebuilt autoinc values don't get
properly assigned. Fetch values from
server side. */
if (wsrep_on(current_thd) &&
wsrep_thd_exec_mode(current_thd) == REPL_RECV)
{
wsrep_thd_auto_increment_variables(
current_thd, &offset, &increment);
}
else
{
#endif /* WITH_WSREP */
offset = m_prebuilt->autoinc_offset;
increment = m_prebuilt->autoinc_increment;
#ifdef WITH_WSREP
}
#endif /* WITH_WSREP */
auto_inc = innobase_next_autoinc(
auto_inc, 1, increment, offset, col_max_value);
error = innobase_set_max_autoinc(auto_inc);
}
}
...省略部分代码...
}
MySQL仅会在INSERT操作时才会触发自增列的更新操作，主实例上执行DELETE+INSERT操作触发自增ID发生变化，UPDATE不会触发自增列的更新操作，从实例上执行UPDATE操作不会触发自增ID发生变化，导致主从实例的自增ID值存在差异，当主从实例发生切换时，便可能出现插入操作报自增主键冲突问题。
