建议使用以下浏览器,以获得最佳体验。 IE 9.0+以上版本 Chrome 31+ 谷歌浏览器 Firefox 30+ 火狐浏览器
设置昵称

在此一键设置昵称,即可参与社区互动!

确定
我再想想
选择版块
RDS for MySQL 主题:252帖子:367

【技术干货】

备机唯一键约束失效

dbin_mu 2020/3/11 7311

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

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

   故而,我们使用索引扫描,查询主机中的数据,发现主机上只有一条记录;但是当我们使用全表扫描时候,却发现存在着两条唯一键相同的数据;这也就解释了为什么会在备机上使用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 '语言',

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

<pre style="margin->  KEY `title` (`title`)<pre style=">) ENGINE=InnoDB AUTO_INCREMENT=960763562 DEFAULT CHARSET=utf8 COMMENT='…'

<pre style="margin->1 row in set (0.00 sec)


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

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

   我们知道,通常在对一个大的表进行import操作的时候,DBA一般会将unique_checks参数设置为off, 其目的就是为了加速数据import速度,因为在该二级索引上对其进行临时关闭;因为在import的过程中,Innodb可以使用change buffer来对该二级索引进行批量的INSERT,从而可以减少大量的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); //使用唯一索引,表中得数据量为:2990029select count(1) from tableName –走全表扫描得,表中得数据为:2990029

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

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

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

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

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

 

·         当用户设置了unique_checksoff的时候;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)) {//INSERTchange buffer中,并未进行唯一性检查。

           cursor->flag = BTR_CUR_INSERT_TO_IBUF;

 

          goto func_exit;

        }

        break;

 

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

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);

}

//当我们需要INSERT的页面不在buffer pool的时候,会将该二级索引的页面INSERTchange buffer

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

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

    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 INSERT 5000条不重复记录,修改unique checks=0,并不断的重复去更新testtable里的test_uniqe约束的字段colume1,此时主机会报错(唯一键约束),备机正常;

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

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

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

 

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

备机唯一键约束失效.png

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

1)对操作表中的重复数据进行去重操作;

2)进行操作的时候不要关闭unique checks

3set global innodb_change_buffering=none

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

  

 

 

 HDC尾巴.png


 

 

 

 

 


回复1

dbin_mu
0 0
2020/3/11 19:33

附件内容

    duplicate_key.txt 2.76 KB,下载次数:2

上划加载中
直达楼层
标签
您还可以添加5个标签
  • 没有搜索到和“关键字”相关的标签
  • 云产品
  • 解决方案
  • 技术领域
  • 通用技术
  • 平台功能
取消

采纳成功

您已采纳当前回复为最佳回复

dbin_mu

发帖: 5粉丝: 1

发消息 + 关注

发表于2020年03月11日 17:40:20 7311 1
直达本楼层的链接
楼主
显示全部楼层
[技术干货] 备机唯一键约束失效

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

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

   故而,我们使用索引扫描,查询主机中的数据,发现主机上只有一条记录;但是当我们使用全表扫描时候,却发现存在着两条唯一键相同的数据;这也就解释了为什么会在备机上使用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 '语言',

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

<pre style="margin->  KEY `title` (`title`)<pre style=">) ENGINE=InnoDB AUTO_INCREMENT=960763562 DEFAULT CHARSET=utf8 COMMENT='…'

<pre style="margin->1 row in set (0.00 sec)


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

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

   我们知道,通常在对一个大的表进行import操作的时候,DBA一般会将unique_checks参数设置为off, 其目的就是为了加速数据import速度,因为在该二级索引上对其进行临时关闭;因为在import的过程中,Innodb可以使用change buffer来对该二级索引进行批量的INSERT,从而可以减少大量的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); //使用唯一索引,表中得数据量为:2990029select count(1) from tableName –走全表扫描得,表中得数据为:2990029

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

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

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

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

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

 

·         当用户设置了unique_checksoff的时候;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)) {//INSERTchange buffer中,并未进行唯一性检查。

           cursor->flag = BTR_CUR_INSERT_TO_IBUF;

 

          goto func_exit;

        }

        break;

 

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

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);

}

//当我们需要INSERT的页面不在buffer pool的时候,会将该二级索引的页面INSERTchange buffer

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

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

    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 INSERT 5000条不重复记录,修改unique checks=0,并不断的重复去更新testtable里的test_uniqe约束的字段colume1,此时主机会报错(唯一键约束),备机正常;

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

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

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

 

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

备机唯一键约束失效.png

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

1)对操作表中的重复数据进行去重操作;

2)进行操作的时候不要关闭unique checks

3set global innodb_change_buffering=none

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

  

 

 

 HDC尾巴.png


 

 

 

 

 


举报
分享

分享文章到朋友圈

分享文章到微博

采纳成功

您已采纳当前回复为最佳回复

dbin_mu

发帖: 5粉丝: 1

发消息 + 关注

发表于2020年03月11日 19:33:53
直达本楼层的链接
沙发
显示全部楼层

附件内容

duplicate_key.txt 2.76 KB,下载次数:2

点赞 评论 引用 举报

游客

富文本
Markdown
您需要登录后才可以回帖 登录 | 立即注册

结贴

您对问题的回复是否满意?
满意度
非常满意 满意 一般 不满意
我要反馈
0/200