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: | |
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
[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