| Bug #77354 | multi drop temp table statements in UDF cause replication fails using 5.6 GTID | ||
|---|---|---|---|
| Submitted: | 15 Jun 2015 6:51 | Modified: | 20 Nov 2015 16:11 |
| Reporter: | Fungo Wang (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S1 (Critical) |
| Version: | 5.6.25, 5.6.26, 5.7.8 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | GTID, replication, temporary table | ||
[15 Jun 2015 7:02]
MySQL Verification Team
Hello Fungo Wang, Thank you for the report and test case. Observed this with 5.6.26 build. Thanks, Umesh
[15 Jun 2015 7:08]
MySQL Verification Team
test results
Attachment: 77354_5_6_26.results (application/octet-stream, text), 20.57 KiB.
[15 Jun 2015 7:21]
MySQL Verification Team
- 5.7.8 also affected
[15 Jun 2015 7:22]
MySQL Verification Team
test results
Attachment: 77354_5_7_8.results (application/octet-stream, text), 28.44 KiB.
[20 Nov 2015 16:11]
David Moss
Thanks for your feedback. This has been fixed in upcoming versions and the following was noted in the 5.6.29 and 5.7.11 changelog: When using --gtid-mode=on , --enforce-gtid-consistency , and --binlog-format=row, if a user defined function with multiple DROP TEMPORARY TABLE statements was executed on a master, the resulting binary log caused an error on slaves. The fix ensures that stored functions and triggers are also considered multi-statement transactions, and that when --enforce-gtid-consistency is enabled, functions with CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE statements generate an ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION error.
Description: When using GTID for replication in 5.6, if create a user defined function, which include multi drop temporary table statements, and we call this function on master, then the slave SQL thread will stop with error message like this: Last_Errno: 1837 Last_Error: Error '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 'cfef3544-1327-11e5-8a6f-2c44fd7a5210:2'.' on query. Default database: 'test'. Query: 'DROP TEMPORARY TABLE IF EXISTS `test`.`test_func_2` /* generated by server */' when inspect the details of binary log events on master(using *show binlog events*), the problems can be seen clearly as below. One GTID followed by multi DDL queries, when slave SQL thread execute this sql sequence, the only explicit GTID is assigned to the first drop DDL, and leaves the other queries without GTID, then leads to error. | master-bin.000001 | 580 | Gtid | 1 | 628 | SET @@SESSION.GTID_NEXT= 'cfef3544-1327-11e5-8a6f-2c44fd7a5210:2' | | master-bin.000001 | 628 | Query | 1 | 779 | DROP TEMPORARY TABLE IF EXISTS `test`.`test_func_1` /* generated by server */ | | master-bin.000001 | 779 | Query | 1 | 930 | DROP TEMPORARY TABLE IF EXISTS `test`.`test_func_2` /* generated by server */ | | master-bin.000001 | 930 | Query | 1 | 1081 | DROP TEMPORARY TABLE IF EXISTS `test`.`test_func_3` /* generated by server */ | | master-bin.000001 | 1081 | Query | 1 | 1232 | DROP TEMPORARY TABLE IF EXISTS `test`.`test_func_4` /* generated by server */ The affected versions I have tested are 5.6.16 and 5.6.25(the latest). How to repeat: //test case as bellow: --source include/not_windows.inc --source include/have_gtid.inc --source include/have_binlog_format_row.inc --disable_warnings --source include/master-slave.inc --enable_warnings connection master; ## create a UDF which drop multi temp tables delimiter //; CREATE FUNCTION `test_func` () RETURNS varchar(30) CHARSET utf8 BEGIN DROP TEMPORARY TABLE IF EXISTS test_func_1; DROP TEMPORARY TABLE IF EXISTS test_func_2; DROP TEMPORARY TABLE IF EXISTS test_func_3; DROP TEMPORARY TABLE IF EXISTS test_func_4; RETURN "hello"; END// delimiter ;// ## call the UDF SELECT test_func(); ## clean the UDF DROP FUNCTION test_func; ## make slave sync with master sync_slave_with_master; ## slave SQL thread stoped --source include/rpl_end.inc And you should also have below options in you case opt or cnf file: --gtid-mode=on --enforce-gtid-consistency --log-slave-updates --log-bin --binlog-format=row