MySQL Bugs: #96443: M-S, sql playback on slave hang

Bug #96443	M-S, sql playback on slave hang
Submitted:	7 Aug 2019 6:42	Modified:	17 Oct 2019 10:16
Reporter:	lieyia --	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.7.21	OS:	CentOS (CentOS release 6.6 (Final))
Assigned to:		CPU Architecture:	x86

Description:
  I have a strange phenomenon when sql playback on slave. I tried various methods, but still can't solve the problem.
  When i start slave a few minutes later，the sql from master will hang on slave, also theres is no load on master and slave.
 

How to repeat:
It appears in our production environment，and some info：
table：
CREATE TABLE `mbk_redpacket_user_freeride` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(35) NOT NULL ,
  `start_timestamp` bigint(13) unsigned DEFAULT NULL ,
  `end_timestamp` bigint(13) unsigned DEFAULT NULL ',
  `type` int(5) DEFAULT NULL ,
  `is_valid` tinyint(4) DEFAULT NULL ,
  `price` int(11) DEFAULT NULL ',
  `order_id` varchar(64) DEFAULT NULL ,
  `crt_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ,
  `upd_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  `mt_userid` varchar(25) NOT NULL DEFAULT '' ,
  `city_code` varchar(10) NOT NULL DEFAULT 'xx' ,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_userid_starttimestamp` (`user_id`,`start_timestamp`),
  KEY `idx_mt_userid` (`mt_userid`)
) ENGINE=InnoDB AUTO_INCREMENT=239258517 DEFAULT CHARSET=utf8 

sql：
INSERT INTO mbk_redpacket_user_freeride(user_id,order_id,is_valid, type, start_timestamp,end_timestamp,mt_userid,city_code ) VALUES('48722122617578211328765758', 'ORD1564452696330339065758', 1, 200, 1564452705876, 1567094399876, '1941341331', '021')

my.cnf,mysql.err,stack

Attachment: info (application/octet-stream, text), 56.96 KiB.

and the table has 200+ million records

Some more information：
1. the data of slave is backed up from master by xtrabackup, 1.2TB
2. when i start slave after  buf_dump_thread loading all ibuf(the result of "select * from performance_schema.events_stages_current" is empty), buf_dump_thread do not wait lock where sql hang;
3. when sql hang, i kill mysqld and start angain, slave playback normally after several times;

info of no lock on buf_dump_thread

Attachment: info2 (application/octet-stream, text), 43.17 KiB.

Hello lieyia,

Thank you for the report and details.
I tried to reproduce this issue on a moderate box with the provided schema and huge dummy data but on latest GA builds 5.7.27/5.7.28.  MySQL 5.7.21 is nearly 2 year old release and since then many bugs fixed in the replication module, moreover we don't fix bugs in old versions, don't backport bug fixes, so need to check with latest version anyway. So, please, upgrade and inform us if problem still exists.

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Thank you for your interest in MySQL.

regards,
Umesh