Bug #99680 MTS encounter 1032 error
Submitted: 25 May 2020 6:52 Modified: 26 May 2020 1:50
Reporter: tu ming Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:8.0.20 OS:CentOS
Assigned to: MySQL Verification Team CPU Architecture:Any

[25 May 2020 6:52] tu ming 
Description:
I create two innodb tables test_a and test_b which test_a has no primary key. Then I update the test_a and test_b in the same trasaction parallelly. I got the 1032 in the slave error message. The sql thread became NO at last.

2020-05-25T03:37:06.724353Z 25 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 5 failed executing transaction '477d8de8-9e34-11ea-bb85-fa163ede251c:2152' at master log mysql-bin.000004, end_log_pos 2292568; Could not execute Update_rows event on table mtu.test_a; Can't find record in 'test_a', Error_code: 1032; Can't find record in 'test_a', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 2292568, Error_code: MY-001032

Seems MySQL 5.7 will hold the lock and report 1025 error(warning).

How to repeat:
1. create two tables:
CREATE DATABASE mtu;
use mtu;
CREATE TABLE `test_a` (
  `test_ID` decimal(12,0) NOT NULL,
  `test_REPO` decimal(6,0) NOT NULL DEFAULT '0',
  `test_FOLD` decimal(6,0) NOT NULL DEFAULT '0',
  `test_FLAG` decimal(1,0) NOT NULL DEFAULT '0',
  `test_CREATED` datetime DEFAULT NULL,
  `test_MODIFIED` datetime DEFAULT NULL,
  `test_CURRENT` decimal(6,0) NOT NULL DEFAULT '0',
  `test_LOCKED` decimal(1,0) NOT NULL DEFAULT '0',
  `test_NODE` decimal(6,0) NOT NULL DEFAULT '0',
  `test_STATUS` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_USER` decimal(6,0) DEFAULT NULL,
  `test_NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_AUTHOR` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_TOPIC` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_RELATED` decimal(1,0) DEFAULT NULL,
  `test_KEEP` decimal(1,0) DEFAULT NULL,
  `test_ATTACH` decimal(1,0) DEFAULT NULL,
  `test_AUTHORID` decimal(6,0) DEFAULT NULL,
  `test_siteID` decimal(6,0) DEFAULT NULL,
  `test_type` decimal(2,0) DEFAULT NULL,
  `test_status` decimal(2,0) DEFAULT '0',
  `test_pubTime` datetime DEFAULT NULL,
  `test_order` decimal(25,10) DEFAULT '0.0000000000',
  `test_priority` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_position` decimal(2,0) DEFAULT '0',
  `test_title` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_attr` decimal(3,0) DEFAULT '63',
  `test_hasTitlePic` decimal(3,0) DEFAULT '0',
  `c_1` decimal(38,0) DEFAULT NULL,
  `c_2` decimal(38,0) DEFAULT NULL,
  `c_3` decimal(38,0) DEFAULT NULL,
  `c_4` decimal(38,0) DEFAULT NULL,
  `c_5` decimal(38,0) DEFAULT NULL,
  `c_6` decimal(38,0) DEFAULT NULL,
  `c_7` decimal(38,0) DEFAULT NULL,
  `c_8` decimal(38,0) DEFAULT NULL,
  `c_9` decimal(38,0) DEFAULT NULL,
  `c_10` decimal(38,0) DEFAULT NULL,
  `solr_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  KEY `IDX01_index` (`c_1`) USING BTREE,
  KEY `IDX02_index` (`c_1`,`test_FLAG`) USING BTREE,
  KEY `IDX03_index` (`test_ID`) USING BTREE,
  KEY `IDX04_index` (`test_MODIFIED`) USING BTREE,
  KEY `IDX05_index` (`test_order`) USING BTREE,
  KEY `IDX06_index` (`test_status`) USING BTREE,
  KEY `IDX07_index` (`test_type`) USING BTREE,
  KEY `IDX08_index` (`test_position`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE `test_b` (
  `test_ID` decimal(12,0) NOT NULL,
  `test_REPO` decimal(6,0) NOT NULL DEFAULT '0',
  `test_FOLD` decimal(6,0) NOT NULL DEFAULT '0',
  `test_FLAG` decimal(1,0) NOT NULL DEFAULT '0',
  `test_CREATED` datetime DEFAULT NULL,
  `test_MODIFIED` datetime DEFAULT NULL,
  `test_CURRENT` decimal(6,0) NOT NULL DEFAULT '0',
  `test_LOCKED` decimal(1,0) NOT NULL DEFAULT '0',
  `test_NODE` decimal(6,0) NOT NULL DEFAULT '0',
  `test_STATUS` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_USER` decimal(6,0) DEFAULT NULL,
  `test_NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_AUTHOR` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_TOPIC` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `test_RELATED` decimal(1,0) DEFAULT NULL,
  `test_KEEP` decimal(1,0) DEFAULT NULL,
  `test_ATTACH` decimal(1,0) DEFAULT NULL,
  `test_AUTHORID` decimal(6,0) DEFAULT NULL,
  `test_siteID` decimal(6,0) DEFAULT NULL,
  `test_orgID` decimal(6,0) DEFAULT NULL,
  `test_originalID` decimal(12,0) DEFAULT NULL,
  `test_type` decimal(2,0) DEFAULT NULL,
  `test_status` decimal(2,0) DEFAULT '0',
  `test_channel` decimal(3,0) DEFAULT '1',
  `test_pubTime` datetime DEFAULT NULL,
  `test_realPubTime` datetime DEFAULT NULL,
  `test_order` decimal(25,10) DEFAULT '0.0000000000',
  `test_priority` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_title` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_subTitle` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_leadTitle` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_shortTitle` longtext,
  `test_keyword` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_tag` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_mark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_editor` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_liability` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_collaborator` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_abstract` longtext CHARACTER SET utf8 COLLATE utf8_general_ci,
  `test_url` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_urlPad` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `test_trace` longblob,
  `test_wordCount` decimal(10,0) DEFAULT NULL,
  `test_extFieldGroup` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_extFieldGroupID` decimal(10,0) DEFAULT '0',
  `test_sourceType` decimal(1,0) DEFAULT '0',
  `test_source` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_sourceUrl` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_sourceID` decimal(6,0) DEFAULT NULL,
  `test_region` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_regionID` decimal(10,0) DEFAULT NULL,
  `test_trade` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_tradeID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_template` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_templateID` decimal(10,0) DEFAULT '0',
  `test_templatePad` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_templatePadID` decimal(10,0) DEFAULT NULL,
  `test_column` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_columnID` decimal(8,0) DEFAULT NULL,
  `test_columnRel` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_columnRelID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_columnAll` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_picBig` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_picMiddle` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_picSmall` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_attr` decimal(3,0) DEFAULT '63',
  `test_copyright` decimal(1,0) DEFAULT NULL,
  `test_isExclusive` decimal(1,0) DEFAULT '0',
  `test_isSensitive` decimal(1,0) DEFAULT '0',
  `test_isBigPic` decimal(1,0) DEFAULT '0',
  `test_discussClosed` decimal(1,0) DEFAULT '0',
  `test_onceTop` decimal(1,0) DEFAULT '0',
  `test_position` decimal(2,0) DEFAULT '0',
  `test_linkID` decimal(12,0) DEFAULT '0',
  `test_linkName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_multimediaLink` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_lastPublish` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_lastPublishID` decimal(6,0) DEFAULT NULL,
  `test_countDiscuss` decimal(12,0) DEFAULT '0',
  `test_countClick` decimal(12,0) DEFAULT '0',
  `test_countClickInitial` decimal(12,0) DEFAULT '0',
  `test_countPraise` decimal(12,0) DEFAULT '0',
  `test_countShare` decimal(12,0) DEFAULT '0',
  `test_countShareClick` decimal(12,0) DEFAULT '0',
  `test_countClick0` decimal(12,0) DEFAULT '0',
  `test_countClick1` decimal(12,0) DEFAULT '0',
  `test_countClick2` decimal(12,0) DEFAULT '0',
  `test_countShare0` decimal(12,0) DEFAULT '0',
  `test_countShare1` decimal(12,0) DEFAULT '0',
  `test_countShare2` decimal(12,0) DEFAULT '0',
  `test_countDiscuss0` decimal(12,0) DEFAULT '0',
  `test_countDiscuss1` decimal(12,0) DEFAULT '0',
  `test_countDiscuss2` decimal(12,0) DEFAULT '0',
  `test_corpID` decimal(12,0) DEFAULT NULL,
  `test_corpTypeID` decimal(10,0) DEFAULT NULL,
  `test_corpTradeID` decimal(10,0) DEFAULT NULL,
  `test_corpStock` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_corpStockCode` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_corpPark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_longitude` decimal(17,14) DEFAULT '0.00000000000000',
  `test_latitude` decimal(17,14) DEFAULT '0.00000000000000',
  `test_location` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_hasTitlePic` decimal(3,0) DEFAULT '0',
  `test_xyAccountID` decimal(8,0) DEFAULT NULL,
  `test_xyAccount` varchar(512) DEFAULT NULL,
  `test_rssID` decimal(12,0) DEFAULT NULL,
  `test_rss` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_rssType` decimal(1,0) DEFAULT '0',
  `test_picType` decimal(10,0) DEFAULT NULL,
  `test_listType` decimal(2,0) DEFAULT NULL,
  `test_picFullScreen` decimal(10,0) DEFAULT NULL,
  `test_titleDisplay` decimal(2,0) DEFAULT NULL,
  `test_pubTimeDisplay` decimal(2,0) DEFAULT NULL,
  `test_relChannel` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_relChannelID` decimal(8,0) DEFAULT NULL,
  `test_shareClosed` decimal(1,0) DEFAULT '0',
  `test_newseditID` decimal(12,0) DEFAULT NULL,
  `test_newseditLibID` decimal(12,0) DEFAULT NULL,
  `test_newseditStatus` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_editorPic` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_editorPicID` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_editorVideo` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_editorVideoID` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_reporter` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_reporterID` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_reporterPic` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_reporterPicID` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_reporterVideo` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_reporterVideoID` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_isExposed` decimal(1,0) DEFAULT '0',
  `test_countExpose` decimal(12,0) DEFAULT '0',
  `test_countClickFake` decimal(12,0) DEFAULT '0',
  `test_tagCascadeID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_tagID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_isShowReporterCard` decimal(1,0) DEFAULT '0',
  `test_isReward` decimal(1,0) DEFAULT '0',
  `test_journalist` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_journalistID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_countReward` decimal(12,0) DEFAULT '0',
  `test_planner` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_checker` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_linkType` decimal(2,0) DEFAULT NULL,
  `test_emojiID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_advClosed` decimal(1,0) DEFAULT '0',
  `test_isRevokeTimer` decimal(1,0) DEFAULT '0',
  `test_revokeTime` datetime DEFAULT NULL,
  `test_style` decimal(2,0) DEFAULT NULL,
  `test_countDiscuss3` decimal(12,0) DEFAULT '0',
  `test_isNewMobile` decimal(1,0) DEFAULT '0',
  `test_cameraman` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_drawer` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_overallPlanning` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_correspondent` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_intern` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_director` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_textArticleType` decimal(4,0) DEFAULT NULL,
  `test_textEditorType` decimal(4,0) DEFAULT NULL,
  `test_videoType` decimal(4,0) DEFAULT NULL,
  `test_photoType` decimal(4,0) DEFAULT NULL,
  `test_audioType` decimal(4,0) DEFAULT NULL,
  `test_specifyType` decimal(4,0) DEFAULT '0',
  `test_textArticleTypeValue` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_textEditorTypeValue` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_videoTypeValue` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_photoTypeValue` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_audioTypeValue` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_specifyTypeValue` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'aaaaa',
  `test_sharePicUrl` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_shareTitle` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_shareContent` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_customShare` decimal(1,0) DEFAULT NULL,
  `test_listTitle` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `test_praiseType` decimal(1,0) DEFAULT '0',
  `test_editorID` varchar(255) DEFAULT NULL,
  `test_countPraise0` decimal(10,0) DEFAULT '0',
  `test_countPraise1` decimal(12,0) DEFAULT '0',
  `test_countPraise2` decimal(12,0) DEFAULT '0',
  `test_countPraise3` decimal(12,0) DEFAULT '0',
  PRIMARY KEY (`test_ID`) USING BTREE,
  KEY `IDX01_index` (`test_AUTHORID`) USING BTREE,
  KEY `IDX02_index` (`test_columnID`) USING BTREE,
  KEY `IDX03_index` (`test_pubTime`) USING BTREE,
  KEY `IDX04_index` (`test_MODIFIED`) USING BTREE,
  KEY `IDX05_index` (`test_FLAG`) USING BTREE,
  KEY `IDX06_index` (`test_siteID`,`test_status`,`test_FLAG`,`test_type`) USING BTREE,
  KEY `IDX07_index` (`test_siteID`,`test_status`,`test_FLAG`) USING BTREE,
  KEY `IDX08_index` (`test_FLAG`,`test_originalID`,`test_type`,`test_status`) USING BTREE,
  KEY `IDX09_index` (`test_pubTime`,`test_status`,`test_FLAG`) USING BTREE,
  KEY `IDX10_index` (`test_pubTime`,`test_status`,`test_siteID`,`test_sourceType`) USING BTREE,
  KEY `IDX11_index` (`test_pubTime`,`test_orgID`,`test_AUTHORID`,`test_status`,`test_siteID`,`test_sourceType`) USING BTREE,
  KEY `IDX12_index` (`test_pubTime`,`test_status`,`test_siteID`,`test_AUTHORID`,`test_orgID`) USING BTREE,
  KEY `IDX13_index` (`test_pubTime`,`test_orgID`,`test_AUTHORID`,`test_status`,`test_siteID`,`test_sourceType`,`test_source`) USING BTREE,
  KEY `IDX14_index` (`test_pubTime`,`test_AUTHORID`,`test_status`,`test_siteID`,`test_sourceType`) USING BTREE,
  KEY `IDX15_index` (`test_pubTime`,`test_status`,`test_siteID`,`test_AUTHORID`) USING BTREE,
  KEY `IDX17_index` (`test_linkID`,`test_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

2. insert 1000 records for each table, which test_ID is 1-1000
3. update two tables parally

cat upd1.sh
#!/bin/bash  

for((i=1;i<100;i++));  
do
    ./bin/mysql -uroot -p123456 -S ./master/mysql.sock -e "begin; update mtu.test_b set test_status=10 where test_ID=${i}; update mtu.test_a set test_status=10 where test_ID=${i}; commit;"
done

cat upd2.sh
#!/bin/bash  

for((i=100;i<200;i++));  
do
    ./bin/mysql -uroot -p123456 -S ./master/mysql.sock -e "begin; update mtu.test_b set test_status=10 where test_ID=${i}; update mtu.test_a set test_status=10 where test_ID=${i}; commit;"
done

... more updX.sh

execute scripts as below:
sh upd1.sh&
sh upd2.sh&
sh upd3.sh&
sh upd4.sh&
sh upd5.sh&
sh upd6.sh&
sh upd7.sh&
sh upd8.sh&
sh upd9.sh&
sh upd10.sh&

4. check the mysqld error message of slave
[25 May 2020 7:59] tu ming 
no need test_b, also reproduced when i update test_a parallelly.
[26 May 2020 1:50] MySQL Verification Team 
Hi,

This is not a bug. Tables that do not have PK can't be replicated with MTS.

Since 8.0.20 there is even REQUIRE_TABLE_PRIMARY_KEY_CHECK variable that you can set to enforce this.

all best
Bogdan