MySQL Bugs: #85756: replication break due to "When @@SESSION.GTID

Bug #85756	replication break due to "When @@SESSION.GTID_NEXT is set to a GTID, ....."
Submitted:	1 Apr 2017 10:45	Modified:	7 Apr 2017 3:55
Reporter:	slwang wang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6.20	OS:	CentOS
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	myisam, replication

Description:
Replication breaks and slave reports error message " When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is '039a0c0c-9cee-11e6-922c-525400a9bf0e:35960484'"

The master and slave's options are as follows:
master_info_repository: FILE
relay_log_info_repository: FILE

binlog_format: MIXED
binlog_row_image：FULL

binlog and relay log did not rotate.

when the error occurs, binlog contents is as follows:

#170331 16:01:19 server id 168404623  end_log_pos 522822498 CRC32 0x91721714    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '039a0c0c-9cee-11e6-922c-525400a9bf0e:35960484'/*!*/;
# at 522822498
#170331 16:01:19 server id 168404623  end_log_pos 522822571 CRC32 0x6925008a    Query   thread_id=4164522       exec_time=0     error_code=0
SET TIMESTAMP=1490947279/*!*/;
BEGIN
/*!*/;
# at 522822571
#170331 16:01:19 server id 168404623  end_log_pos 522822630 CRC32 0x33fe6d10    Table_map: `bb_s1`.`log_item` mapped to number 7958
# at 522822630
#170331 16:01:19 server id 168404623  end_log_pos 522822695 CRC32 0xa16a341c    Write_rows: table id 7958 flags: STMT_END_F

BINLOG '
zwzeWBOPpgkKOwAAAOajKR8AABYfAAAAAAEABWJiX3MxAAhsb2dfaXRlbQAIAwMDAQMDAwMAABBt
/jM=
zwzeWB6PpgkKQQAAACekKR8AABYfAAAAAAEAAgAI/wDPDN5Ykg4AAMsAAAAK3DoBAAMAAABJAgAA
AQAAABw0aqE=
'/*!*/;
### INSERT INTO `bb_s1`.`log_item`
### SET
###   @1=1490947279 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3730 /* INT meta=0 nullable=0 is_null=0 */
###   @3=203 /* INT meta=0 nullable=0 is_null=0 */
###   @4=10 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @5=80604 /* INT meta=0 nullable=0 is_null=0 */
###   @6=3 /* INT meta=0 nullable=0 is_null=0 */
###   @7=585 /* INT meta=0 nullable=0 is_null=0 */
###   @8=1 /* INT meta=0 nullable=0 is_null=0 */
# at 522822695
#170331 16:01:19 server id 168404623  end_log_pos 522822754 CRC32 0xb6cdc6bf    Table_map: `bb_s1`.`log_item` mapped to number 7958
# at 522822754
#170331 16:01:19 server id 168404623  end_log_pos 522822819 CRC32 0x0470107c    Write_rows: table id 7958 flags: STMT_END_F

BINLOG '
zwzeWBOPpgkKOwAAAGKkKR8AABYfAAAAAAEABWJiX3MxAAhsb2dfaXRlbQAIAwMDAQMDAwMAAL/G
zbY=
zwzeWB6PpgkKQQAAAKOkKR8AABYfAAAAAAEAAgAI/wDPDN5Ykg4AAMsAAAAKeDoBAAIAAABKAgAA
AQAAAHwQcAQ=
'/*!*/;
### INSERT INTO `bb_s1`.`log_item`
### SET
###   @1=1490947279 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3730 /* INT meta=0 nullable=0 is_null=0 */
###   @3=203 /* INT meta=0 nullable=0 is_null=0 */
###   @4=10 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @5=80504 /* INT meta=0 nullable=0 is_null=0 */
###   @6=2 /* INT meta=0 nullable=0 is_null=0 */
###   @7=586 /* INT meta=0 nullable=0 is_null=0 */
###   @8=1 /* INT meta=0 nullable=0 is_null=0 */
# at 522822819
#170331 16:01:19 server id 168404623  end_log_pos 522822893 CRC32 0x0c5ae75c    Query   thread_id=4164522       exec_time=0     error_code=0
SET TIMESTAMP=1490947279/*!*/;
COMMIT
/*!*/;
# at 522822893

"show create table" is as follows: 
log_item | CREATE TABLE `log_item` (
  `a` int(10) unsigned NOT NULL COMMENT '????',
  `b` int(10) unsigned NOT NULL COMMENT '??ID',
  `c` int(10) unsigned NOT NULL COMMENT '??ID',
  `d` tinyint(3) unsigned NOT NULL COMMENT '??',
  `e` int(10) unsigned NOT NULL COMMENT '??Key',
  `f` int(10) unsigned NOT NULL COMMENT '???',
  `g` int(11) NOT NULL COMMENT '????',
  `h` int(11) NOT NULL COMMENT '????',
  KEY `a` (`a`)
) ENGINE=MyISAM

How to repeat:
I dont know how to general two pairs table_map_event and write_row_event in a transaction in myisam table. I can not repeat it.

Suggested fix:
depending

The binlog before and after is as follows, it looks like fine:
#170331 16:01:19 server id 168404623  end_log_pos 522821862 CRC32 0x3613e94b    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '039a0c0c-9cee-11e6-922c-525400a9bf0e:35960482'/*!*/;
# at 13644922
#170331 16:01:19 server id 168404623  end_log_pos 522821935 CRC32 0x4a77f07e    Query   thread_id=4164522       exec_time=0     error_code=0
SET TIMESTAMP=1490947279/*!*/;
BEGIN
/*!*/;
# at 13644995
#170331 16:01:19 server id 168404623  end_log_pos 522821994 CRC32 0x41fd2caf    Table_map: `bb_s1`.`log_item` mapped to number 7958
# at 13645054
#170331 16:01:19 server id 168404623  end_log_pos 522822089 CRC32 0x23fa4b5f    Write_rows: table id 7958 flags: STMT_END_F

BINLOG '
zwzeWBOPpgkKOwAAAGqhKR8AABYfAAAAAAEABWJiX3MxAAhsb2dfaXRlbQAIAwMDAQMDAwMAAK8s
/UE=
zwzeWB6PpgkKXwAAAMmhKR8AABYfAAAAAAEAAgAI/wDPDN5Ykg4AAMwAAAAK3DoBAAMAAAD2HQAA
BQAAAADPDN5Ykg4AAMwAAAAKeDoBAAIAAAD7HQAABQAAAF9L+iM=
'/*!*/;
### INSERT INTO `bb_s1`.`log_item`
### SET
###   @1=1490947279 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3730 /* INT meta=0 nullable=0 is_null=0 */
###   @3=204 /* INT meta=0 nullable=0 is_null=0 */
###   @4=10 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @5=80604 /* INT meta=0 nullable=0 is_null=0 */
###   @6=3 /* INT meta=0 nullable=0 is_null=0 */
###   @7=7670 /* INT meta=0 nullable=0 is_null=0 */
###   @8=5 /* INT meta=0 nullable=0 is_null=0 */
### INSERT INTO `bb_s1`.`log_item`
### SET
###   @1=1490947279 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3730 /* INT meta=0 nullable=0 is_null=0 */
###   @3=204 /* INT meta=0 nullable=0 is_null=0 */
###   @4=10 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @5=80504 /* INT meta=0 nullable=0 is_null=0 */
###   @6=2 /* INT meta=0 nullable=0 is_null=0 */
###   @7=7675 /* INT meta=0 nullable=0 is_null=0 */
###   @8=5 /* INT meta=0 nullable=0 is_null=0 */
# at 13645149
#170331 16:01:19 server id 168404623  end_log_pos 522822163 CRC32 0x5a9b1d5b    Query   thread_id=4164522       exec_time=0     error_code=0
SET TIMESTAMP=1490947279/*!*/;
COMMIT
/*!*/;
# at 13645223
#170331 16:01:19 server id 168404623  end_log_pos 522822211 CRC32 0xaebc09b5    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '039a0c0c-9cee-11e6-922c-525400a9bf0e:35960483'/*!*/;
# at 13645271
#170331 16:01:19 server id 168404623  end_log_pos 522822292 CRC32 0xabb5e843    Query   thread_id=4177632       exec_time=0     error_code=0
SET TIMESTAMP=1490947279/*!*/;
BEGIN
/*!*/;
# at 13645352
#170331 16:01:19 server id 168404623  end_log_pos 522822419 CRC32 0xd95e747c    Query   thread_id=4177632       exec_time=0     error_code=0
SET TIMESTAMP=1490947279/*!*/;
UPDATE `item` SET g=587 WHERE b=3730 AND c=203
/*!*/;
# at 13645479
#170331 16:01:19 server id 168404623  end_log_pos 522822450 CRC32 0xc6d0a99b    Xid = 71750275
COMMIT/*!*/;
# at 13645510

#170331 16:01:19 server id 168404623  end_log_pos 522822941 CRC32 0x6df828b6    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '039a0c0c-9cee-11e6-922c-525400a9bf0e:35960485'/*!*/;
# at 13646001
#170331 16:01:19 server id 168404623  end_log_pos 522823022 CRC32 0xe1fcabec    Query   thread_id=4177632       exec_time=0     error_code=0
SET TIMESTAMP=1490947279/*!*/;
BEGIN
/*!*/;
# at 13646082
#170331 16:01:19 server id 168404623  end_log_pos 522823152 CRC32 0x0cbb6ba5    Query   thread_id=4177632       exec_time=0     error_code=0
SET TIMESTAMP=1490947279/*!*/;
UPDATE `lock` Set `d`=0 WHERE b=3730 AND `d`=1
/*!*/;
# at 13646212
#170331 16:01:19 server id 168404623  end_log_pos 522823183 CRC32 0x8f332003    Xid = 71750278
COMMIT/*!*/;
# at 13646243

Hi,

MyISAM is not transactional engine and commit/rollback does not work with it.

If you read: https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-restrictions.html

you can find that:
Updates involving nontransactional storage engines.  When using GTIDs, updates to tables using nontransactional storage engines such as MyISAM cannot be made in the same statement or transaction as updates to tables using transactional storage engines such as InnoDB. 

I suggest you move that table to a transactional engine (InnoDB for e.g.) if you want to use GTID

best regards
Bogdan