Bug #81119 multi source replication slave sql running error 1778
Submitted: 18 Apr 2016 5:45 Modified: 8 Jun 2017 18:21
Reporter: mohamed atef Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.7.12,5.7.13,5.7.14,5.7.15,8,5.7.16,5.717 OS:Windows
Assigned to: CPU Architecture:Any
Tags: multi source replication slave sql running error 1778

[18 Apr 2016 5:45] mohamed atef
Description:
i had multi source replication between 1 master and 6 slaves
after upgrading to version 5.7.12
this the problem
sql running error 1778
Error 'Cannot execute statements with implicit commit inside a transaction when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.' on query. Default database: ''. Query: 'BEGIN'

master gtid variables

How to repeat:
i had multi source replication between 1 master and 6 slaves
after upgrading to version 5.7.12

Suggested fix:
i don`t know
[18 Apr 2016 5:46] mohamed atef
gtid varaiables

Attachment: gtid variables.PNG (image/png, text), 7.47 KiB.

[28 Apr 2016 10:00] MySQL Verification Team
Bug #81152 marked as duplicate of this
[13 Jun 2016 10:56] mohamed atef
mr Umesh Umesh
why till now i didn`t receive any reply on this issue 
is it bug or not 
it is repeated as the same in mysql server 5.7.13
but not found in mysql server 5.7.11
[13 Jun 2016 11:03] mohamed atef
the same issue in mysql server 5.7.13
[30 Jul 2016 11:38] mohamed atef
no one reply till now
also mysql server 5.7.14 added to list the same error
[30 Jul 2016 15:21] mohamed atef
Also found the same error in ver 5.7.14
[7 Sep 2016 8:22] mohamed atef
also mysql server 5.7.15
[16 Sep 2016 20:53] mohamed atef
mysql server 8.00 dmr added to list the same error
[17 Dec 2016 19:06] mohamed atef
5.7.16 and 5.7.17 also had the same error ??????????????????????
[23 Dec 2016 20:24] mohamed atef
mysql server 5.7.12,13,14,15,16,17 ,5.8 relay log
# at 110379
#161221 23:09:51 server id 1005  end_log_pos 22221445 CRC32 0x2eda65e0 	Xid = 783610
COMMIT/*!*/;
# at 110410
#161221 23:09:51 server id 1005  end_log_pos 22221510 CRC32 0xfdb4b5df 	GTID	last_committed=32995	sequence_number=32996
SET @@SESSION.GTID_NEXT= 'd6973f75-6a92-11e6-9ac0-64006a5006b1:100201'/*!*/;
# at 110475
#161221 23:09:51 server id 1005  end_log_pos 22221573 CRC32 0x57ccfd3d 	Query	thread_id=3682	exec_time=37088	error_code=0
SET TIMESTAMP=1482350991/*!*/;
BEGIN
/*!*/;
# at 110538
#161221 23:09:51 server id 1005  end_log_pos 22221653 CRC32 0xa19b3c9c 	Table_map: `abc`.`warehouses_stocks` mapped to number 116
# at 110618
#161221 23:09:51 server id 1005  end_log_pos 22221747 CRC32 0xe11606ec 	Table_map: `abc`.`warehouses_stocks_log_data` mapped to number 117
# at 110712
#161223 22:40:30 server id 1  end_log_pos 110771 CRC32 0xc9cb5aa5 	Rotate to ph4-relay-hqwarehouse.000005  pos: 4
ROLLBACK /* added by mysqlbinlog */ /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

this the end of relay log file roll back added before rotating to next relay log file and the transaction not completed before rotation
so the error appeared 
 but in mysql server 5.7.11 
this end of relay log file 
it complete transaction before rotate to next relay log file 

mysql server 5.7.11 relay log
# at 221103
#161223 20:33:46 server id 1001  end_log_pos 31466925 CRC32 0x37251d0f 	Xid = 964366
COMMIT/*!*/;
# at 221134
#161223  2:49:58 server id 1004  end_log_pos 221193 CRC32 0x3ce4aa44 	Rotate to ph4-relay-hqwarehouse.001058  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[23 Dec 2016 21:50] mohamed atef
slave sql thread didn`t skip the last transaction and try to executing it although the relay log rotated to next file
and show error no 1778
rotation occurred although max_relay_log_size is 1G
and the file didn`t exceeded 1M
[24 Dec 2016 11:42] mohamed atef
multi source replication
[24 Dec 2016 12:58] mohamed atef
it happens when using multi source replication even have only one channel
but when using master to master replication 
or master slave replication 
the rotating event added successfully
but multi source replication mysqlbinlog failed to 
write the rotating event
[26 Dec 2016 10:35] mohamed atef
in today test i had replaced mysqlbinlog v5.7.17 with mysqlbinlog v5.7.11
it work fine in multi source replication for 7 replication channels
mysqlbinlog write the rotating event successfully
and rotate to next relay log with no rollback added
so surely the problem in mysqlbinlog versions that higher than 5.7.11
[28 Jan 2017 21:28] mohamed atef
Replication: When binlog was disabled, the GTID specified
   by GTID_NEXT when committing an empty transaction caused
   by a 'BEGIN' command was being saved into
   GLOBAL@gtid_executed when it should not have been.
   However, when binlog was enabled, the same situation did
   not save it.
   Now, when "@@SESSION.GTID_NEXT == 'UUID:NUMBER'", the
   'BEGIN' command causes an
   'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET'
   error inside an empty or non-empty transaction regardless
   of the binlog setting, since it causes an implicit commit
   like other DDLs. In this case, the GTID specified by
   GTID_NEXT is not saved into GLOBAL@gtid_executed. (Bug
   #22130929)
this from mysql server 5.7.12 change log

but in all version from 5.7.12 and above
mysqlbinlog when writing last event in relay log
to rotate to next relay log
write as empty transaction with begin
"@@SESSION.GTID_NEXT == 'UUID:NUMBER'"
which load the error 
'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET'
although the last event when read it from master binlog it was not DDL
event and written completly in the master binlog
but mysqlbinlog in the slave server write it as empty transaction
so the result 
after fixing the (Bug
   #22130929)
this affect mysqlbinlog when writing the rotating event in relay log 
and result in empty transaction and riase error 
'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET'
hope to reply me
[28 Jan 2017 22:41] mohamed atef
check this

Attachment: mysql setgtidnext.txt (text/plain), 12.79 KiB.

[28 Jan 2017 23:04] mohamed atef
plz anyone reply me
[28 Jan 2017 23:05] mohamed atef
urgent plz
[29 Mar 2017 12:24] João Gramacho
Posted by developer:
 
The issue is happening when the SQL thread is trying to "GTID skip" a partial transaction that is finishing with a Table_map event.

Unfortunately, the way to skip transactions when GTID_MODE=ON is using "GTID skip".

As a workaround, when using MASTER_AUTO_POSITION=1, doing "STOP SLAVE; RESET SLAVE; START SLAVE;" will erase all slave's relay log files (including the one with the partial transaction) and will make the I/O thread to replicate again from the master all not yet applied transactions.
[29 Mar 2017 13:14] João Gramacho
Posted by developer:
 
Just a small correction: the issue is happening when the SQL thread has to GTID skip an incomplete transaction in the relay log. No need to end with a Table_map event.
[11 Apr 2017 10:58] mohamed atef
still the same error in MySQL server 5.7.18
????????????????????????????????????
[8 Jun 2017 18:21] Jon Stephens
DOcumentd fix in the MySQL 5.7.19 and 8.0.2 changelogs, as follows:

    The SQL thread was unable to GTID skip a partial transaction.

Closed.
[3 Feb 2020 8:28] bill w
pls check the Bug #81119 again.
It still come out in mysql 5.7.28.
thx very much!
[3 Feb 2020 8:33] bill w
sorry~
actually in mysql 5.7.26.
[15 Mar 2020 23:19] Otmane K.
Stil hapening in 5.7.29