MySQL Bugs: #87464: GTID restriction on temporary tables may be too heavy-handed

Bug #87464	GTID restriction on temporary tables may be too heavy-handed
Submitted:	18 Aug 2017 2:19	Modified:	23 Aug 2017 6:38
Reporter:	Andrew Garner	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.7, 5.7.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In https://bugs.mysql.com/bug.php?id=77354, the restriction on CREATE/DROP TEMPORARY TABLE was added to generate an error when these operations are run in gtid_mode outside of autocommit.

This is causing some pain in the case of Hibernate, which, in some circumstances, uses temporary tables heavily and in a transactional context.   This causes problem with GTIDs enabled in current MySQL 5.7.

I wonder if this restriction could be relaxed in the MySQL Server.

With binlog_format=ROW, temporary tables are not written to the binary log per the documentation:

https://dev.mysql.com/doc/refman/5.7/en/replication-features-temptables.html

binlog_format=ROW can only be changed outside of a transaction.

binlog_format=ROW can only be changed without active temporary tables.

Given this, it is not clear to me the cases where CREATE / DROP TEMPORARY TABLE would cause a problem with replication in this context.

How to repeat:
Given this patch:

--- a/sql/binlog.cc
+++ b/sql/binlog.cc
@@ -10897,7 +10897,8 @@ bool THD::is_ddl_gtid_compatible()
       GTID even if the transaction is rolled back.
       This includes the execution inside Functions and Triggers.
     */
-    if (in_multi_stmt_transaction_mode() || in_sub_stmt)
+    if ((in_multi_stmt_transaction_mode() || in_sub_stmt)
+        && !is_current_stmt_binlog_format_row())
     {
       bool ret= handle_gtid_consistency_violation(
         this, ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION);

And an instance configured with:
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates
log-bin=mysql-bin

I reran the test  from bug 77354 and I do not see an obvious problem.   That is with this patch I can run the following workload:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)

mysql> create temporary table t1 (id int);
Query OK, 0 rows affected (0.00 sec)

And while using GTID:

mysql> select @@gtid_mode, @@enforce_gtid_consistency, @@log_slave_updates, @@log_bin;
+-------------+----------------------------+---------------------+-----------+
| @@gtid_mode | @@enforce_gtid_consistency | @@log_slave_updates | @@log_bin |
+-------------+----------------------------+---------------------+-----------+
| ON          | ON                         |                   1 |         1 |
+-------------+----------------------------+---------------------+-----------+
1 row in set (0.00 sec)

But I do not see temporary tables being logged to the binary log on either master or slave.

Output from the test case in bug #77354:

$ cat t/bug_77354
--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

$ ./mysql-test-run.pl bug_77354
Logging: ./mysql-test-run.pl  bug_77354
MySQL Version 5.7.18
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '.../mysql-test/var'...
Installing system database...
Using parallel: 1

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
[connection master]
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//
SELECT test_func();
test_func()
hello
DROP FUNCTION test_func;
include/rpl_end.inc
main.bug_77354                           [ pass ]    382
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.382 of 15 seconds executing testcases

Completed: All 1 tests were successful.

This behavior is still broken in MySQL 5.7.19 without the patch for https://bugs.mysql.com/bug.php?id=85258.

My testing here was sloppy and done against Percona Server 5.7.18 which has the patch from bug 85258 applied and so no longer requires the additional guard against temporary tables in a multi-transaction context.

At this point, I suppose this is a feature request that if bug 85258 is fixed then also relax the GTID constraints on temporary tables.

Hello Andrew Garner,

Thank you for the report.
Verifying this as a feature request for relaxing the GTID constraints on temporary tables post bug 85258 fix.

Thanks,
Umesh

-- 5.7.19

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19/mysql-test: ./mtr rpl_bug77354
Logging: ./mtr  rpl_bug77354
MySQL Version 5.7.19
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/GABuilds/mysql-5.7.19/mysql-test/var'...
Installing system database...
Using parallel: 1

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
[connection master]
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//
main.rpl_bug77354                        [ fail ]
        Test ended at 2017-08-23 08:33:00

CURRENT_TEST: main.rpl_bug77354
mysqltest: At line 26: query 'SELECT test_func()' failed: 1787: Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.  These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
safe_process[6713]: Child process: 6714, exit: 1

 - the logfile can be found in '/export/umesh/server/binaries/GABuilds/mysql-5.7.19/mysql-test/var/log/main.rpl_bug77354/rpl_bug77354.log'
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 4 seconds executing testcases

Completed: Failed 1/1 tests, 0.00% were successful.

Failing test(s): main.rpl_bug77354

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html

mysql-test-run: *** ERROR: there were failing test cases