备机唯一键约束失效

   前几日线上运维告知线上客户进行数据同步的时候，备机老是会同步失败，经过查询备机上错误日志发现是出现了duplicate key错误，及试图在备机上插入具有相同唯一键值的两条记录；

   由于备机是试图插入两条相同唯一键值的记录而违法唯一性约束，那么也就意味着在主机上存在着至少两条具有相同唯一键值的记录。因为，只有备机上才会出现上述的现象；

   故而，我们使用索引扫描，查询主机中的数据，发现主机上只有一条记录；但是当我们使用全表扫描时候，却发现存在着两条唯一键相同的数据；这也就解释了为什么会在备机上使用binlog进行数据同步的时候，备机上会发生违反唯一键约束的错误-duplicate key error;

相应错误信息如下：

  2019-12-05T12:04:14.435930+08:00 1293519 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction 'cae9a444-b853-11e9-b0ea-fa163edfac4d:851196058' at master log mysql-bin.212398, end_log_pos 219996953; Could not execute Write_rows_v1 event on table db_name.table_name; Duplicate entry 'xxx.yyy.zzz' for key 'package_lang', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 219996953, Error_code: 1062 

表结构如下：

mysql> show create table db_name.table_name \G

*************************** 1. row ***************************

       Table: googleplay_global

Create Table: CREATE TABLE `table_name` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `colX` varchar(128) NOT NULL COMMENT 'package name',

   …

   `colY` varchar(16) NOT NULL DEFAULT '' COMMENT '语言',

  PRIMARY KEY (`id`),

  UNIQUE KEY `package_lang` (`colX`,`colY`),

  KEY `title` (`title`)

) ENGINE=InnoDB AUTO_INCREMENT=960763562 DEFAULT CHARSET=utf8 COMMENT='…'

1 row in set (0.00 sec)

   从现象上看看，备机之所以会存在着唯一键约束失败的错误，主要原因是由于在主机上存在着两条唯一键相同的记录，（为方便讨论这里我们用rowX和rowY分别代表这两条记录）。当rowX被insert到主机时，由于无论在主机和备机上都不存在着该记录，此时rowX顺利被insert并同步到备机；当rowY被insert到主机时候，此时rowY也被顺利插入并开始同步到备机；此时，备机上也已经存在着记录rowX，此时在备机上在继续插入具有相同唯一键值的记录rowY时候，此时备机检测到其违反唯一键约束，故而备机同步出错；

  为什么会出现这个现象呢？我们直观的反应：是否是用户将唯一性检查关闭了，从而导致该情况的发生；我们知道，MySQL中涉及到unique key的变量为unique_checks，虽然其名曰唯一性约束检查，但实际上在MySQL中会始终进行唯一性约束检查。但InnoDB却存在着一种特殊情形下，InnoDB却不在对所操作的数据进行唯一性约束进行检查；

   我们知道，通常在对一个大的表进行import操作的时候，DBA一般会将unique_checks参数设置为off, 其目的就是为了加速数据import速度，因为在该二级索引上对其进行临时关闭；因为在import的过程中，Innodb可以使用change buffer来对该二级索引进行批量的插入，从而可以减少大量的IO；此时，需要由用户来保证数据的唯一性；官方也给出了给出相关说明：

If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

SET unique_checks=0;... SQL import statements ...

SET unique_checks=1;

For big tables, this saves a lot of disk I/O because InnoDB can use its change buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.

其中，我们需要特别注意最后一句话，be certain that data contains NO duplicate keys.

  用户是否也是因为设置了unique_checks=off，而导致在备机上出现duplicate key error这样的错误呢？

  经过查询binlog，可以看到用户在进行数据import操作之前，进行了set unique_checks=off

的操作，然后又执行了disable keys操作；

SET TIMESTAMP=1576519219/*!*/;

SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/;

SET @@session.sql_mode=524288/*!*/;

/*!40000 ALTER TABLE `tablesName_global` DISABLE KEYS */

   select count(1) from googleplay_global force (package_lang); //使用唯一索引，表中得数据量为：2990029；select count(1) from tableName –走全表扫描得，表中得数据为：2990029

select count(distinct colX,colY) from googleplay_global force index(package_lang) –走唯一索引且去重后，我们得到得表得数据量为：2647195；可以看出：走唯一索引且去重后我们得到2647195条记录，而通过全表是2990029，两者之间差了近30w条记录，而这些就是重复的数据(违反唯一索引特性)；

  从用户的参数配置和官方给出的解释，我们知道：当unique_checks关闭的时候并且在插入数据的时候使用了change buffer，如果此时我们插入的数据中再存在着唯一键重复的记录时，InnoDB是则会插入该具有相同唯一键值的记录；

   innodb有个change buffer，可以缓存二级索引的修改，比如一个插入/删除等，定位到二级索引的叶页面的时候，这个页面不在缓存，那么就把这个插入/删除缓存到change buffer，之后当真正要修改的页面被读入缓存的时候，再合并这个修改。 按照代码设计和逻辑，如果unique checks=0，并且可以被缓存到change buffer，这个插入是不需要进行唯一性检查。

   简而言之，上述可以归结为三点：（1）unique_checks=off；（2）使用change buffer；（3）插入的数据中存在着唯一键相同的记录；如果同时满足了上述三点后，则唯一性约束将会被破坏；

对于change buffer的作用这里简单提下：当我们对二级索引进行操作的时候，InnoDB为了减少对于二级索引的IO操作；当我们对二级索引进行操作的时候，如果该页面不在缓冲池（buffer pool）的时候，首先会将该二级索引插入到change buffer中，当该页面由于其他操作被加载到缓冲池(buffer pool)的时候，将change buffer中的页面和缓冲池中的页面进行合并；这样就可以不必每次对于二级索引的操作都进行一次IO操作了，可以减少IO操作；

·         当用户设置了unique_checks为off的时候；InnoDB会将相应的状态更新到当前事务的check_unique_secondary字段中：

  trx->check_unique_secondary =

      !thd_test_options(thd, OPTION_RELAXED_UNIQUE_CHECKS);

·         在二级索引页面定位的过程中会依据是否对二级索引上的唯一性进行检查设置相应的操作类型：

void btr_cur_search_to_nth_level(

dict_index_t *index,

…)  {

…

    case BTR_INSERT:

      btr_op = (latch_mode & BTR_IGNORE_SEC_UNIQUE)

                   ? BTR_INSERT_IGNORE_UNIQUE_OP

                   : BTR_INSERT_OP;

   …

    switch (btr_op) {

      case BTR_INSERT_OP:

      case BTR_INSERT_IGNORE_UNIQUE_OP:

        ut_ad(fetch == Page_fetch::IF_IN_POOL);

        ut_ad(!dict_index_is_spatial(index));

        if (ibuf_insert(IBUF_OP_INSERT, tuple, index, page_id, page_size,

                        cursor->thr)) {//插入到change buffer中，并未进行唯一性检查。

           cursor->flag = BTR_CUR_INSERT_TO_IBUF;

          goto func_exit;

        }

        break;

·         在二级索引页面插入的过程中检查是否对唯一性索引约束进行判断：

dberr_t row_ins_sec_index_entry_low (ulint flags, ulint mode, …) {

  …

  /* Note that we use PAGE_CUR_LE as the search mode, because then

  the function will return in both low_match and up_match of the

  cursor sensible values */

  if (!thr_get_trx(thr)->check_unique_secondary) {

    search_mode |= BTR_IGNORE_SEC_UNIQUE;

  }

 …

if (dict_index_is_spatial(index)) {

…

} else {

if (index->table->is_intrinsic()) {

  …

} else {

  btr_cur_search_to_nth_level(index, 0, entry, PAGE_CUR_LE, search_mode,

                                  &cursor, 0, __FILE__, __LINE__, &mtr);

}

…

//当我们需要插入的页面不在buffer pool的时候，会将该二级索引的页面插入到change buffer

//中，而这也是导致我们唯一性约束失败的必要条件；

if (cursor.flag == BTR_CUR_INSERT_TO_IBUF) { //插入到change buffer中，在完成change buffer的插入后，其并未在二级索引上进行插入操作，直接退出了；若此时在二级索引上进行插入操作的话，则会依据row_ins_scan_sec_index_for_duplicate函数的返回值来进行判定本次二级索引插入是否成功；

    ut_ad(!dict_index_is_spatial(index));

    /* The insert was buffered during the search: we are done */

    goto func_exit;

  }

  …

  if (dict_index_is_unique(index) &&

      (cursor.low_match >= n_unique || cursor.up_match >= n_unique)) {

    mtr_commit(&mtr);

    DEBUG_SYNC_C("row_ins_sec_index_unique");

    if (row_ins_sec_mtr_start_and_check_if_aborted(&mtr, index, check,

                                                   search_mode)) {

      goto func_exit;

    }

    err = row_ins_scan_sec_index_for_duplicate(flags, index, entry, thr, check,

                                               &mtr, offsets_heap);

   mtr_commit(&mtr);

   switch (err) {

     case DB_SUCCESS:

         break;

     case DB_DUPLICATE_KEY:

         if (!index->is_committed()) {

         …

   }

   …

  func_exit:

  if (dict_index_is_spatial(index)) {

    rtr_clean_rtr_info(&rtr_info, true);

  }

  mtr_commit(&mtr);

  DBUG_RETURN(err);

}

   至此，我们给出违反唯一性约束的原因和出现违反该唯一性约束条件；那么我们可以在本地按如下的方式进行重现：

1： session 1，在存在唯一约束test_uniqe的表testtable插入5000条不重复记录，修改unique checks=0，并不断的重复去更新testtable里的test_uniqe约束的字段colume1,此时主机会报错（唯一键约束），备机正常；

2：打开session 2,不断的往testtable1-testtable10 共10张表里不断的插入数据，目的是把testtable这张表数据挤出buffer_pool，放入change_buffer中（此时unique checks=0）

3：此时再回到session1上对testtable进行更新操作（此时unique checks=0）,此时testtable所有的数据不再buffer_pool中，都转到change_buffer中，这时innodb就会绕过唯一性检查进行数据写入，产生现象为唯一性约束被破坏

4：此时备机在回放的时候就会出现违反唯一性约束的报错

下面大家可以使用附件中的SQL进行相关场景的复现：

为了杜绝上述问题的发生，我们可以通过如下操作来消除违反唯一性约束的三个条件：

（1）对操作表中的重复数据进行去重操作；

（2）进行操作的时候不要关闭unique checks；

（3）set global innodb_change_buffering=none；

（4）对唯一性检查选项进行权限控制，如果用户一定要进行关闭唯一性检查选择，则通知用户需明确知道其所作对于数据一致性所带来的影响；