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

[7 Aug 2019 6:42] lieyia --
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')
[7 Aug 2019 6:43] lieyia --
my.cnf,mysql.err,stack

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

[7 Aug 2019 6:46] lieyia --
and the table has 200+ million records
[9 Aug 2019 2:10] lieyia --
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;
[9 Aug 2019 2:31] lieyia --
info of no lock on buf_dump_thread

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

[17 Oct 2019 10:16] Umesh Shastry
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