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:
None 
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 6:51] Fungo Wang
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
[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.