五块的博客
Home
归档
留言板
avatar
文章
5
Home
归档
留言板
目录
  1. 1. 问题复现
    1. 1.1. REPLACE操作导致AUTO_INCREMENT值不一致
    2. 1.2. 从库升主后插入数据报错
  2. 2. 原因分析
  3. 3. replace操作在binlog(row格式)中的行为分析
  4. 4. 尝试修复：
    1. 4.1. 可能的修复方法一：
    2. 4.2. 可能的修复方法二：
      1. 4.2.1. mysql8.0实验
  5. 5. 小结
  6. 6. 附：
REPLACE操作导致主从库AUTO_INCREMENT不一致的分析

在某些情况下，replace操作将导致主从库auto_increment值不一致，如果此时发生切换，将可能导致数据无法插入的问题。

问题复现

REPLACE操作导致AUTO_INCREMENT值不一致

测试版本：5.7.23

建表语句：

sql
1
2
3
4
5
6
7
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL DEFAULT '0',
`data` varchar(100) not null DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY uniq_name(name)
) ENGINE=InnoDB;

初始插入一些记录：

sql
1
2
3
4
5
6
insert into t1(name, data) values
('user1', 'data1'), 
('user2', 'data2'), 
('user3', 'data3'),
('user4', 'data4'),
('user5', 'data5');

查看主从库auto_increment：

可以看见此时主从库的auto_increment一致。

使用replace into语句替换部分数据：

sql
1
2
3
4
replace into t1(name, data) values
('user2', 'new data2'), 
('user3', 'new data3'), 
('user4', 'new data4');

再次查看主从库表中数据与auto_increment值：

IMAGE
发现此时主备库auto_increment不一致。

从库升主后插入数据报错

如果这时发生主从切换，即在原从库尝试插入数据：

sql
1
2
3
4
insert into t1(name, data) values('user6', 'data6');
insert into t1(name, data) values('user7', 'data7');
insert into t1(name, data) values('user8', 'data8');
insert into t1(name, data) values('user9', 'data9');

IMAGE

原备库的auto_increment值为6，由于之前的replace操作，表中数据已经存在主键id为6, 7, 8的记录，导致前三条语句插入失败，报错重复主键。

原因分析

尝试分析导致主备库auto_increment不一致的原因：
查看主库记录的binlog(row格式)，发现上述replace操作在binlog中记录的是update操作：

sql
1
2
3
4
5
6
7
8
9
10
11
...
### UPDATE `test`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='user2' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
###   @3='data2' /* VARSTRING(100) meta=100 nullable=0 is_null=0 */
### SET
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='user2' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
###   @3='new data2' /* VARSTRING(100) meta=100 nullable=0 is_null=0 */
...

从库应用以上日志时，只更新了自增列的值（大于auto_increment），而auto_increment值并未增加，如果此时发生切换，向原从库insert数据，就会报错。

replace操作在binlog(row格式)中的行为分析

在官方文档中replace的语义有这样一句话：”It either inserts, or deletes and inserts.”，
然而通过上面的实验可看出replace操作在row格式下的binlog中记录的并不是delete+insert操作，而是直接update。
那么是不是所有情况都是记录的update操作呢，可以实验观察：

sql
1
2
3
4
5
6
7
8
9
10
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) NOT NULL DEFAULT '0',
  `c` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_a` (`a`),
  UNIQUE KEY `uniq_b` (`b`),
  UNIQUE KEY `uniq_c` (`c`)
) ENGINE=InnoDB

通过先insert数据，再执行replace操作，并观察不同的冲突情况及row格式下的binlog中记录的行为，整理如下表，其中*表示与哪一个键冲突，同一颜色跨越多行的表示replace操作和多行记录都有冲突（实验重现与原始数据）：
IMAGE

可以看见replace操作发生冲突时在binlog中记录的行为并不一致，既有(delete+)update的情况，也有delete+insert的情况。其中记录为update操作的在图中用红色字体标注，可以发现它们都有一个共同点就是在一行内仅有唯一键c冲突。

同时，不同的表结构还未测试：例如含有联合唯一键的表结构、仅存在主键的表结构、仅含有主键和其它非唯一键的表结构、含有主键+唯一键+非唯一键的表结构等等。

那么mysql是如何判断replace操作在binlog中记录的类型呢：
带着问题翻看源码，可以看见在sql_insert.cc和log_event.cc中有相关代码：

cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
sql_insert.cc:
...
/* Check if there is more uniq keys after field */

static int last_uniq_key(TABLE *table,uint keynr)
{
  /*
    When an underlying storage engine informs that the unique key
    conflicts are not reported in the ascending order by setting
    the HA_DUPLICATE_KEY_NOT_IN_ORDER flag, we cannot rely on this
    information to determine the last key conflict.
   
    The information about the last key conflict will be used to
    do a replace of the new row on the conflicting row, rather
    than doing a delete (of old row) + insert (of new row).
   
    Hence check for this flag and disable replacing the last row
    by returning 0 always. Returning 0 will result in doing
    a delete + insert always.
  */
  if (table->file->ha_table_flags() & HA_DUPLICATE_KEY_NOT_IN_ORDER){
    return 0;
  }
  while (++keynr < table->s->keys){
    if (table->key_info[keynr].flags & HA_NOSAME){
        return 0;
    }
  }
  return 1;
}
...

	/*
	  The manual defines the REPLACE semantics that it is either
	  an INSERT or DELETE(s) + INSERT; FOREIGN KEY checks in
	  InnoDB do not function in the defined way if we allow MySQL
	  to convert the latter operation internally to an UPDATE.
          We also should not perform this conversion if we have 
          timestamp field with ON UPDATE which is different from DEFAULT.
          Another case when conversion should not be performed is when
          we have ON DELETE trigger on table so user may notice that
          we cheat here. Note that it is ok to do such conversion for
          tables which have ON UPDATE but have no ON DELETE triggers,
          we just should not expose this fact to users by invoking
          ON UPDATE triggers.
	*/
	if (last_uniq_key(table,key_nr) &&
	    !table->file->referenced_by_foreign_key() &&
            (!table->triggers || !table->triggers->has_delete_triggers()))
        {
          if ((error=table->file->ha_update_row(table->record[1],
					        table->record[0])) &&
              error != HA_ERR_RECORD_IS_THE_SAME)
            goto err;
          if (error != HA_ERR_RECORD_IS_THE_SAME)
            info->stats.deleted++;
          else
            error= 0;
          thd->record_first_successful_insert_id_in_cur_stmt(table->file->insert_id_for_cur_row);
          /*
            Since we pretend that we have done insert we should call
            its after triggers.
          */
          goto after_trg_n_copied_inc;
        }
        else
        {
...
        }
...

在log_event.cc中也有上述相近代码，但在调试过程中发现并未调用，所以以上述代码为准初步分析：

可以看见mysql选择将replace操作转换为update操作的条件为：
当发生冲突的键是最后一个唯一键 且 没有外键引用 且 没有触发器

由于我们线上不使用外键和触发器，所以暂不关心后两个条件。

这也验证了上述实验的数据：由于t1表中name是最后一个唯一键，t2表中c是最后一个唯一键，所以当最后一个唯一键冲突时，在binlog中记录的是update操作，其它情况记录的则是delete+insert操作。

尝试修复：

腾讯数据库技术公众号在2018年12月的一篇文章中提到了相同问题，给出了如下修复方案：
IMAGE
这里主要在内核侧对第2种修复方法进行了实现，尝试对源码进行修改：
sql_insert.cc中的last_uniq_key()函数只在上述一处被调用，且无外部文件调用。
至于为什么程序会判断last_uniq_key()只能在log_event.cc中找到如下一段相关的注释：

Code
1
2
3
4
5
6
7
8
9
I (Matz) am not sure of the reason for the last_uniq_key()
check as, but I'm guessing that it's something along the
following lines.

Suppose that we got the duplicate key to be a key that is not
the last unique key for the table and we perform an update:
then there might be another key for which the unique check will
fail, so we're better off just deleting the row and inserting
the correct row.

last_uniq_key()函数是mysql在repacle操作遇到冲突时采取 delete+insert 或是 update 行为的关键，下面是该函数的历史变更记录：
sql_insert.cc中和last_uniq_key()相关的历史变更记录摘要如下，内容较多预警，已经尽量省略，更详细的的记录可以点此：

在2000年mysql的第一次提交记录中就引入了last_uniq_key()函数，此时的条件判断相当简单，只有last_uniq_key()一个条件：

cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
commit f4c589ff6c653d1d2a09c26e46ead3c8a15655d8
Author: bk@work.mysql.com <>
Date:   Mon Jul 31 21:29:14 2000 +0200
...
+static int last_uniq_key(TABLE *table,uint keynr)
+{
+  while (++keynr < table->keys)
+    if (table->key_info[keynr].flags & HA_NOSAME)
+      return 0;
+  return 1;
+}
...
+      if (last_uniq_key(table,key_nr))
+      {
+	if ((error=table->file->update_row(table->record[1],table->record[0])))
+	  goto err;
+	info->deleted++;
+	break;					/* Update logfile and count */
+      }
...

repalce在内部实现的update行为会导致有外键引用的表出现问题，这里又加入了一个例外条件：table->file->referenced_by_foreign_key()

cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
commit d9790a406c69a46eb197cea725c1e7c7e480ac41
Author: heikki@hundin.mysql.fi <>
Date:   Mon Feb 9 23:57:29 2004 +0200
    Many files:
      Do not let REPLACE to perform internally an UPDATE if the table is referenced by a FOREIGN KEY: the manual says that REPLACE must resolve a duplicate key error semantically with DELETE(s) + INSERT, and not by an UPDATE; the internal update caused foreign key checks and cascaded operations to behave in a semantically wrong way
...
-      if (last_uniq_key(table,key_nr))
+      /*
+	The manual defines the REPLACE semantics that it is either an INSERT or
+	DELETE(s) + INSERT; FOREIGN KEY checks do not function in the defined
+	way if we allow MySQL to convert the latter operation internally to an
+	UPDATE.
+      */
+
+      if (last_uniq_key(table,key_nr)
+	  && !table->file->referenced_by_foreign_key())
       {
 	if ((error=table->file->update_row(table->record[1],table->record[0])))
 	  goto err;
...

后续又不断加入了多个条件，例如：table->timestamp_default_now == table->timestamp_on_update_now、 (!table->triggers || !table->triggers->has_delete_triggers())) 等等：

cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
commit f6bff2e6c662d4778f86a919b6f7b731d2553f44
Author: dlenev@jabberwock.localdomain <>
Date:   Fri Apr 2 10:12:53 2004 +0400

    WL#1266 "Separate auto-set logic from TIMESTAMP type."
    
    Final version of patch.
    
    Adds support for specifying of DEFAULT NOW() and/or ON UPDATE NOW()
    clauses for TIMESTAMP field definition.
    Current implementation allows only one such field per table and
    uses several unireg types for storing info about this properties of
    field. It should be replaced with better implementation when new
    .frm format is introduced.
...
 	  an INSERT or DELETE(s) + INSERT; FOREIGN KEY checks in
 	  InnoDB do not function in the defined way if we allow MySQL
 	  to convert the latter operation internally to an UPDATE.
+          We also should not perform this conversion if we have 
+          timestamp field with ON UPDATE which is different from DEFAULT.
 	*/
 	if (last_uniq_key(table,key_nr) &&
-	    !table->file->referenced_by_foreign_key())
+	    !table->file->referenced_by_foreign_key() &&
+            table->timestamp_default_now == table->timestamp_on_update_now)
         {
           if ((error=table->file->update_row(table->record[1],
 					     table->record[0])))
...

cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
commit 2511990c978137fe0bc81657e160a6d537bc957f
Author: dlenev@brandersnatch.localdomain <>
Date:   Fri Oct 1 18:54:06 2004 +0400

    Support for TIMESTAMP columns holding NULL values. Unlike all other
    column types TIMESTAMP is NOT NULL by default, so in order to have
    TIMESTAMP column holding NULL valaues you have to specify NULL as
    one of its attributes (this needed for backward compatibility).
    
    Main changes:
    Replaced TABLE::timestamp_default_now/on_update_now members with
    TABLE::timestamp_auto_set_type flag which is used everywhere
    for determining if we should auto-set value of TIMESTAMP field
    during this operation or not. We are also use Field_timestamp::set_time()
    instead of handler::update_timestamp() in handlers.
...
 	if (last_uniq_key(table,key_nr) &&
 	    !table->file->referenced_by_foreign_key() &&
-            table->timestamp_default_now == table->timestamp_on_update_now)
+            (table->timestamp_field_type == TIMESTAMP_NO_AUTO_SET ||
+             table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH))
         {
           if ((error=table->file->update_row(table->record[1],
 					     table->record[0])))
cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
commit 007a20591892beb3734ed4d29fdfe28b750f435a
Author: dlenev@brandersnatch.localdomain <>
Date:   Tue May 24 22:19:33 2005 +0400

    Fix for bugs:
     #5860 "Multi-table UPDATE does not activate update triggers"
     #6812 "Triggers are not activated for INSERT ... SELECT"
     #8755 "Trigger is not activated by LOAD DATA".
    This patch also implements proper handling of triggers for special forms
    of insert like REPLACE or INSERT ... ON DUPLICATE KEY UPDATE.
    Also now we don't call after trigger in case when we have failed to
    inserted/update or delete row. Trigger failure should stop statement
    execution.
    
    I have not properly tested handling of errors which happen inside of
    triggers in this patch, since it is simplier to do this once we will be
    able to access tables from triggers.
...
             (table->timestamp_field_type == TIMESTAMP_NO_AUTO_SET ||
              table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH))
         {
+          if (table->triggers &&
+              table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+                                                TRG_ACTION_BEFORE, TRUE))
+            goto before_trg_err;
           if ((error=table->file->update_row(table->record[1],
 					     table->record[0])))
             goto err;
           info->deleted++;
cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
commit 59d20e26d57e9fc33cf44ada4ce511f507e3d623
Author: dlenev@mysql.com <>
Date:   Fri Jun 16 20:21:25 2006 +0400

    Fix for bug#13479 "REPLACE activates UPDATE trigger, not the DELETE and
    INSERT triggers".
    
...
 	*/
 	if (last_uniq_key(table,key_nr) &&
 	    !table->file->referenced_by_foreign_key() &&
             (table->timestamp_field_type == TIMESTAMP_NO_AUTO_SET ||
-             table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH))
+             table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH) &&
+            (!table->triggers || !table->triggers->has_delete_triggers()))
         {
-          if (table->triggers &&
-              table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
-                                                TRG_ACTION_BEFORE, TRUE))
-            goto before_trg_err;
...
cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
commit 6eee956d7538410f5fd5ee0f8216b2d89937950a
Author: V Narayanan <v.narayanan@sun.com>
Date:   Thu Dec 3 16:48:02 2009 +0530

    WL#4454 change sql_insert.cc::last_uniq_key to match keys in any order
    
    Introduce a flag that will enable the REPLACE
    command to work correctly with an underlying
    storage engine that does not report unique key
    conflicts in the ascending order.
 
 static int last_uniq_key(TABLE *table,uint keynr)
 {
+  /*
+    When an underlying storage engine informs that the unique key
+    conflicts are not reported in the ascending order by setting
+    the HA_DUPLICATE_KEY_NOT_IN_ORDER flag, we cannot rely on this
+    information to determine the last key conflict.
+   
+    The information about the last key conflict will be used to
+    do a replace of the new row on the conflicting row, rather
+    than doing a delete (of old row) + insert (of new row).
+   
+    Hence check for this flag and disable replacing the last row
+    by returning 0 always. Returning 0 will result in doing
+    a delete + insert always.
+  */
+  if (table->file->ha_table_flags() & HA_DUPLICATE_KEY_NOT_IN_ORDER)
+    return 0;
+
   while (++keynr < table->s->keys)
     if (table->key_info[keynr].flags & HA_NOSAME)
       return 0;
...
cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
commit 0d466406d7e33e6c497e91f2865ce3d32cae916f
Author: Martin Hansson <martin.hansson@oracle.com>
Date:   Tue Jan 31 16:16:16 2012 +0100

    WL#5874: CURRENT_TIMESTAMP as DEFAULT for DATETIME columns.
...
 	if (last_uniq_key(table,key_nr) &&
 	    !table->file->referenced_by_foreign_key() &&
-            (table->timestamp_field_type == TIMESTAMP_NO_AUTO_SET ||
-             table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH) &&
             (!table->triggers || !table->triggers->has_delete_triggers()))
         {
           if ((error=table->file->ha_update_row(table->record[1],
...

从sql_insert.cc中关于last_uniq_key()的相关变更记录中可以看到在2000年的最初版本中即出现了如果last_uniq_key()则update的逻辑，但是作者并未说明这样做的意图，前文所述log_event.cc中的那段相关注释(“I (Matz) am not sure of the reason for the last_uniq_key() check as…”)第一次出现是在2005年的一次commit:

cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
commit ad126d90e019f223470e73e1b2b528f9007c4532
Author: lars@mysql.com <>
Date:   Thu Dec 22 06:39:02 2005 +0100

    WL#1012: All changes as one single changeset.
    This includes both code and test cases.
...
+
+       REPLACE is defined as either INSERT or DELETE + INSERT.  If
+       possible, we can replace it with an UPDATE, but that will not
+       work on InnoDB if FOREIGN KEY checks are necessary.
+
+       I (Matz) am not sure of the reason for the last_uniq_key()
+       check as, but I'm guessing that it's something along the
+       following lines.
+
+       Suppose that we got the duplicate key to be a key that is not
+       the last unique key for the table and we perform an update:
+       then there might be another key for which the unique check will
+       fail, so we're better off just deleting the row and inserting
+       the correct row.
+     */
+    if (last_uniq_key(table, keynum) &&
+        !table->file->referenced_by_foreign_key())
+    {
+      error=table->file->ha_update_row(table->record[1],
+                                       table->record[0]);
+      return error;
+    }
...

可能的修复方法一：

了解了last_uniq_key()相关的历史变更，尝试将该逻辑判断去掉，统一replace的内部实现逻辑为(delete+)insert，还能避免诸多关于AUTO_INCREMENT、UPDATE CURRENT_TIMESTAMP、TRIGGER等相关的判断甚至是bug（从提交历史可以看出来）。去掉该处逻辑是否有其它影响还有待讨论，可能的修复方法如下：

cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-       if (last_uniq_key(table,key_nr) &&
-           !table->file->referenced_by_foreign_key() &&
-            (!table->triggers || !table->triggers->has_delete_triggers()))
-        {
-          if ((error=table->file->ha_update_row(table->record[1],
-                                               table->record[0])) &&
-              error != HA_ERR_RECORD_IS_THE_SAME)
-            goto err;
-          if (error != HA_ERR_RECORD_IS_THE_SAME)
-            info->stats.deleted++;
-          else
-            error= 0;
-          thd->record_first_successful_insert_id_in_cur_stmt(table->file->insert_id_for_cur_row);
-          /*
-            Since we pretend that we have done insert we should call
-            its after triggers.
-          */
-          goto after_trg_n_copied_inc;
-        }
-        else

如此统一replace操作的内部实现逻辑，通过了含有replace into语句的测试用例（1 skipped），更详细的测试还依赖于完善的测试环境。：

./mtr auto_increment bug39022 bulk_replace debug_sync2 explain flush flush_read_lock func_group grant2 grant_explain_non_select heap heap_btree heap_hash innodb_mysql_lock2 insert insert_notembedded insert_select insert_update join loaddata lock_multi lock_sync merge myisam opt_hints packet parser partition partition_charset partition_explicit_prune partition_locking replace trigger trigger_wl6030 type_timestamp union view

IMAGE

可能的修复方法二：

在mysql8.0版本中不仅将AUTO_INCREMENT值做了持久化，且在做更新操作时，如果表上的自增列被更新为比auto_increment更大的值，auto_increment值也将被更新。
实验验证如下：

sql
1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t1(a) values(1), (2), (3);
select * from t1;
id a
1 1
2 2
3 3
show create table t1\G

IMAGE

sql
1
2
3
4
5
6
7
update t1 set id = 6 where id =1;
select * from t1;
id a
2 2
3 3
6 1
show create table t1\G

mysql8.0.13表现如下：
IMAGE

而mysql5.7.23不会因为上诉UPDATE操作更新auto_increment值：
IMAGE

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
insert into t1(a) values(4);
insert into t1(a) values(5);
insert into t1(a) values(6);
mysql8.0.13表现如下：
select * from t1;
id a
2 2
3 3
6 1
7 4
8 5
9 6
mysql5.7.23表现如下：
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
select * from t1;
id a
2 2
3 3
4 4
5 5
6 1

可以看见两者之间的差异，在myql8.0中UPDATE操作如果将自增列的值更新为比auto_increment大，auto_increment的值也会发生更新。

在文中开头提到的主从复制场景中，如果replace操作当最后一个唯一键索引冲突的情况下在row格式的binlog中记录为update操作，从库在应用日志时也会更新auto_increment值。如此在发生切换时就不会发生原从库的自增列的最大值大于当前auto_increment值而导致数据无法插入的情况了。

mysql8.0实验

使用mysql8.0.13重现文中一开头提到的实验：
row格式的binlog中记录的是UPDATE操作：

Code
1
2
3
4
5
6
7
8
9
10
11
...
### UPDATE `test`.`t1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='user3' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3='data3' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
### SET
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2='user3' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3='new data3' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
...

主从库的数据和auto_increment值一致：
IMAGE
IMAGE

8.0的修复方法详见如下commit，对于本文讨论的问题重点关注row_upd_check_autoinc_counter()函数：
https://github.com/mysql/mysql-server/commit/dcb8792b371601dc5fc4e9f42fb9c479532fc7c2?spm=0.11153940.blogcont60885.9.71f87fb1ijwJTI

小结

通过上面的分析，mysql在一开始通过引入判断last_uniq_key()来采取delete+insert或update的逻辑算是一种优化，update相对于delete+insert操作来说执行更为高效，例如锁开销更少、写入redo log中的数据更少等等。但是从该函数相关的提交历史可以看见这种优化有诸多例外情况，例如存在触发器、外键引用等，这些例外随着时间的推移不断被发现并被加入到mysql源码中。本文又叙述了另一种例外情况，同样的可以考虑将例如表中没有自增列、不是Replication Master等这些例外情况加入其中以避免文中提到的问题，也可以直接统一replace操作的行为，牺牲这种优化，避免诸多可能的bug。而在mysql8.0中update自增列的值比auto_increment值更大时将更新auto_increment，所以就没必要再考虑这种例外情况了，但在目前被广泛使用的mysql5.7中仍然存在上述问题，在使用时还需要留心。

附：

一些和replace相关的bug搜集：

Replace into causes master/slave have different auto_increment offset values
https://bugs.mysql.com/bug.php?id=87861

比较久远的bug，2003年被提出，直至2017年才在mysql8.0中被修复，且该bug仍存在于mysql5.7:
Innodb autoincrement stats los on restart
https://bugs.mysql.com/bug.php?id=199
WL#6204: InnoDB persistent max value for autoinc columns
https://dev.mysql.com/worklog/task/?id=6204

提到了update自增列时考虑更新auto_increment，但官方回复因存在并发、死锁相关问题且这样的场景太少，没被修复。
Update auto_increment column to last ID + 1 causes error on next insert
https://bugs.mysql.com/bug.php?id=12434

Docs for REPLACE are not accurate
https://bugs.mysql.com/bug.php?id=54560

Partitions: crash, REPLACE .. on table with PK, DUPLICATE KEY event
https://bugs.mysql.com/bug.php?id=16782

REPLACE or ON DUPLICATE KEY UPDATE in auto_increment breaks binlog
https://bugs.mysql.com/bug.php?id=20188

stored function inserting into two auto_increment breaks statement-based binlog
https://bugs.mysql.com/bug.php?id=19630

REPLACE activates UPDATE trigger, not the DELETE and INSERT triggers
https://bugs.mysql.com/bug.php?id=13479

文章作者: wukuai
文章链接: https://yangwuyuan.com/2019/05/10/REPLACE操作导致主从库AUTO-INCREMENT不一致的分析/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 五块的博客！
评论
简