WL#8872: Allow CREATE TEMPORARY/DROP TEMPORARY in transaction when GTID_MODE=ON in RBR

High-Level Description / Executive Summary

EXECUTIVE SUMMARY
=================
Allow CREATE/DROP TEMPORARY TABLE in a transaction when @@global.gtid_mode=ON
and @@session.binlog_format=ROW/MIXED. And don't replicate CREATE
TEMPORARY / DROP TEMPORARY when @@session.binlog_format=ROW/MIXED.


USER STORIES
============
AS a MySQL user, I want that MySQL is able to support to allow CREATE/DROP
TEMPORARY TABLE in a transaction when @@global.gtid_mode=ON and
@@session.binlog_format=ROW/MIXED. So that their is no concern
to turn GTID_MODE on.


DEV STORIES
===========
As a MySQL dev, I want to not replicate CREATE TEMPORARY / DROP TEMPORARY
when @@session.binlog_format=ROW/MIXED, so that remove the following
problems:
1. If a slave has open temporary tables, and the slave is restarted, the
   temporary tables are lost, because they are dropped when the connection
   is closed, and subsequently the slave diverges or the applier stops
   with an error. No DROP TABLE is written to the slave's binary log.
2. When replicating in a chain like A->B->C, and B is restarted while
   it has open temporary tables, then when C reconnects to B, C will
   notice the restart and drop its temporary tables. This generates a
   DROP TEMPORARY TABLE in the binary log on C. If GTID_MODE=ON, the
   DROP TEMPORARY TABLE will use a new GTID having C's UUID. This is
   an "errant transaction"; it only exists on a slave and not on its
   master. If, at any time in the future, there is a failover and C
   becomes a master of B or C, the errant transaction may cause C to
   fail to connect.

REFERENCES
==========

See also contribution:

- Redundant GTID unsafe mark for CREATE/DROP TEMPORARY TABLE in RBR/MBR
  https://bugs.mysql.com/bug.php?id=89467

Functional and Non-Functional Requirements

FUNCTIONAL REQUIREMENTS
=======================
F-1: When @@session.binlog_format=ROW/MIXED, the server must not write
     CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE to the binary log.

F-2: SET @@session.binlog_format must be disallowed if session has open
     temporary table(s).

F-3: SET @@global.binlog_format and SET @@persist.binlog_format must be
     disallowed if any replication channel has open temporary table(s).

F-4: SET @@global.binlog_format and SET @@persist.binlog_format must be
     disallowed if any replication channel applier is running.

F-5: Allow CREATE/DROP TEMPORARY TABLE inside a transaction when
     @@global.gtid_mode=ON and @@session.binlog_format=ROW/MIXED.

F-6: Allow CREATE/DROP TEMPORARY TABLE inside a PROCDURE when
     @@global.gtid_mode=ON and @@session.binlog_format=ROW/MIXED.

F-7: Allow CREATE/DROP TEMPORARY TABLE inside a FUNCTION when
     @@global.gtid_mode=ON and @@session.binlog_format=ROW/MIXED.

F-8. Allow CREATE/DROP TEMPORARY TABLE inside a TRIGGER when
     @@global.gtid_mode=ON and @@session.binlog_format=ROW/MIXED.


NON-FUNCTIONAL REQUIREMENTS
===========================
NF-1: Cross-version replication OLD master -> NEW slave must work.

NF-2: Cross-version replication NEW master -> OLD slave must work.

High-Level Specification / Interface Specification

HIGH LEVEL SPECIFICATION
========================

1. When @@session.binlog_format=ROW/MIXED, the server must not write
   CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE to the binary log
   (this was already implemented). So that CREATE TEMPORARY TABLE
   and DROP TEMPORARY TABLE can not be replicated.

2. SET @@session.binlog_format must be disallowed if session has open
   temporary table(s). Otherwise DROP TEMPORARY TABLE is written into
   binary log which disobeys F-1 in the following case:
     SET @@session.binlog_format=STATEMENT;
     CREATE TEMPORARY TABLE t1 (a INT);
     SET @@session.binlog_format=ROW;
     DROP TEMPORARY TABLE t1;
   This is more restrictive than before.
   Note: SET @@session.binlog_format to the same binlog format is also
   disallowed if session has open temporary table(s).

3. SET @@global.binlog_format and SET @@persist.binlog_format must be
   disallowed if any replication channel has open temporary table(s).
   Otherwise DROP TEMPORARY TABLE is written into binary log on slave
   which disobeys F-1 in the following case:
     slave> SET @@global.binlog_format=STATEMENT;
     slave> START SLAVE;
     master> CREATE TEMPORARY TABLE t1(a INT);
     slave> [wait for t1 to replicate] 
     slave> STOP SLAVE;
     slave> SET @@global.binlog_format=ROW / SET @@persist.binlog_format=ROW
     master> DROP TEMPORARY TABLE t1;
     slave> START SLAVE; 
   This is more restrictive than before.
    
   Note: SET @@global.binlog_format and SET @@persist.binlog_format to
   the same binlog format are also disallowed if any replication
   channel has open temporary table(s).
   Note: SET @@persist_only.binlog_format is not disallowed if any
   replication channel has open temporary table(s), since unlike
   PERSIST, PERSIST_ONLY does not modify the runtime global system
   variable value.
   Note: Nuno and I figured out that group replication channels don't have
   the functional requirement F-3, because the group_replication_applier
   and group_replication_applier channels just apply group events in ROW
   binlog format, they don't have open temporary table(s).

4. SET @@global.binlog_format and SET @@persist.binlog_format must be
   disallowed if any replication channel applier is running, because
   SET @@global.binlog_format does not take effect when any replication
   channel applier is running. SET @@global.binlog_format takes effect
   on the channel until its applier is (re)starting. This is more
   restrictive than before.

   Note: SET @@global.binlog_format and SET @@persist.binlog_format to
   the same binlog format are also disallowed if any replication
   channel applier is running.
   Note: SET @@persist_only.binlog_format is not disallowed if any
   replication channel applier is running, since unlike PERSIST,
   PERSIST_ONLY does not modify the runtime global system variable value.
   Note: Nuno and I figured out that the group_replication_applier channel
   does not have the requirement F-4, because GR already handles that.
   If you set global.binlog_format or SET persist.binlog_format while GR
   is not running, once you try to start Group Replication, it will error
   out (as expected). If GR is running once the set global is effective
   on new connections, those connections will not be able to commit
   (they will be rollback).

5. Allow CREATE/DROP TEMPORARY TABLE inside a transaction when
   @@global.gtid_mode=ON and @@session.binlog_format=ROW/MIXED.
   (More specifically, we allow the statements when
    @@global.enforce_gtid_consistency=ON, which then implies
    that it is allowed when @@global.gtid_mode=ON).
     - Suppose user executes the following transactions:
         BEGIN;
         CREATE TEMPORARY TABLE t (a INT);
         COMMIT;
         BEGIN;
         DROP TEMPORARY TABLE t (a INT);
         COMMIT;
       Then both transactions shall succeed and not be written to the binary log.
     - Suppose user executes the following transactions:
         BEGIN;
         DML on a non-temporary table;
         CREATE TEMPORARY TABLE t (a INT);
         COMMIT;
         BEGIN;
         DML on a non-temporary table;
         DROP TEMPORARY TABLE t (a INT);
         COMMIT;
       Then, both transactions shall succeed, be written to the binary log
       without the CREATE/DROP TEMPORARY TABLE statements, and replicate to the
       slave. Slave_open_temp_tables shall be 0 before, between, and after the
       two transactions.
     - Suppose user executes the following transactions:
         BEGIN;
         CREATE TEMPORARY TABLE t (a INT);
         ROLLBACK;
         BEGIN;
         DROP TEMPORARY TABLE t (a INT);
         ROLLBACK;
         BEGIN;
         DML on a non-temporary table;
         CREATE TEMPORARY TABLE temp1 (a INT);
         DML on a temporary table;
         ROLLBACK;
         BEGIN;
         DML on a non-temporary table;
         DML on a temporary table;
         DROP TEMPORARY TABLE temp1 (a INT);
         ROLLBACK;
       After rollback, any above transaction shall roll back with a warning
       "The creation of some temporary tables could not be rolled back." or
       "Some temporary tables were dropped, but these operations could not
       be rolled back.", and not be written to the binary log.

6. Allow CREATE/DROP TEMPORARY TABLE inside a PROCDURE when
   @@global.gtid_mode=ON and @@session.binlog_format=ROW/MIXED.
     - Suppose user creates the following single statement procedures:
         delimiter //;
         CREATE PROCEDURE sp1 ()
         BEGIN
           CREATE TEMPORARY TABLE temp1 (i INT);
         END//
         CREATE PROCEDURE sp2 ()
         BEGIN
           DROP TEMPORARY TABLE temp1;
         END//
         delimiter ;//
       CREATE/DROP TEMPORARY TABLE shall not be written to the
       binary log when calling the two procedures.
     - Suppose user creates the following multi statements procedures:
         CREATE TABLE t1 (c1 INT);
         delimiter //;
         CREATE PROCEDURE sp3 ()
         BEGIN
           INSERT INTO t1 VALUES (9);
           CREATE TEMPORARY TABLE temp1 (i INT);
           INSERT INTO temp1 VALUES (1);
         END//
         CREATE PROCEDURE sp4 ()
         BEGIN
           INSERT INTO t1 VALUES (10);
           INSERT INTO temp1 VALUES (2);
           DROP TEMPORARY TABLE temp1;
         END//
         delimiter ;//
       CREATE/DROP TEMPORARY TABLE shall not be written to the
       binary log when calling the two procedures.

7. Allow CREATE/DROP TEMPORARY TABLE inside a FUNCTION when
   @@global.gtid_mode=ON and @@session.binlog_format=ROW/MIXED.
   CREATE/DROP TEMPORARY TABLE in the FUNCTION are not written
   into binlog when using the FUNCTION in SELECT QUERY and
   in INSERT/UPDATE/DELETE QUERY.

8. Allow CREATE/DROP TEMPORARY TABLE inside a TRIGGER when
   @@global.gtid_mode=ON and @@session.binlog_format=ROW/MIXED.
   CREATE/DROP TEMPORARY TABLE in the TRIGGER are not written
   into binlog when the TRIGGER is triggered before/after
   INSERT/UPDATE/DELETE QUERY.

UPGRADE/DOWNGRADE and CROSS-VERSION REPLICATION
===============================================
NEW->OLD: Suppose user issues CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE
          inside a transaction on NEW master, when gtid_mode=ON,
          enforce_gtid_consistency=ON, and binlog_format=ROW/MIXED. Even if
          this would be disallowed when executed in a client to the OLD slave,
          it does not cause replication to break, because CREATE/DROP
          TEMPORARY TABLE is not replicated when committing/rolling back
          the transaction.

NEW->OLD: Suppose user issues CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE
          inside a PROCDURE/FUNCTION/TRIGGER on NEW master, when gtid_mode=ON,
          enforce_gtid_consistency=ON, and binlog_format=ROW/MIXED. Even if
          this would be disallowed when executed in a client to the OLD slave,
          it does not cause replication to break, because CREATE/DROP
          TEMPORARY TABLE is not replicated when calling the
          PROCDURE/FUNCTION/TRIGGER.

NEW->OLD: Suppose user issues CREATE TEMPORARY TABLE or DROP TEMPORARY
          TABLE outside a transaction on a NEW master when
          @@session.binlog_format=ROW/MIXED. This does not cause replication
          to break, because CREATE/DROP TEMPORARY TABLE is not replicated.

OLD->NEW: Suppose user issues SET @@session.binlog_format=ROW on a OLD
          master when there are open temporary tables in the session.
          Even if this would be disallowed when executed on a NEW slave,
          this does not cause replication to break, because SET is not
          replicated. The same to SET @@global.binlog_format and
          SET @@persist.binlog_format.

On upgrade, one limitation is removed, see above 5, 6, 7 and 8, there are
three new limitations, see above 2, 3 and 4.

OBSERVABILITY
=============
Introduce several new errors and emit them. See below.

ER_TEMP_TABLE_PREVENTS_SWITCH_SESSION_BINLOG_FORMAT
  eng "Changing @@session.binlog_format is disallowed when the session has open
temporary table(s). You could wait until these temporary table(s) are dropped
and try again."
Emit the above error to the client side on the session when it has open
temporary table(s). Don't write the error into error log.

ER_TEMP_TABLE_PREVENTS_SWITCH_GLOBAL_BINLOG_FORMAT
  eng "Changing @@global.binlog_format or @@persist.binlog_format is disallowed
when any replication channel has open temporary table(s). You could wait until
Slave_open_temp_tables = 0 and try again"
Emit the above error to the client side on the session when any replication
channel has open temporary table(s). Don't write the error into error log.

ER_RUNNING_APPLIER_PREVENTS_SWITCH_GLOBAL_BINLOG_FORMAT
  eng "Changing @@global.binlog_format or @@persist.binlog_format is disallowed
when any replication channel applier thread is running. You could execute STOP
SLAVE SQL_THREAD and try again."
Emit the above error to the client side on the session when any replication
channel applier is running. Don't write the error into error log.

ER_CLIENT_GTID_UNSAFE_CREATE_DROP_TEMP_TABLE_IN_TRX_IN_SBR
  eng "Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP
TEMPORARY TABLE are not allowed inside a transaction or inside a procedure in a
transactional context when @@session.binlog_format=STATEMENT."
Emit the above error to the client side on the session when creating/droping
TEMPORARY TABLE inside a transaction or procedure, in case
@@session.binlog_format=STATEMENT.

ER_SERVER_GTID_UNSAFE_CREATE_DROP_TEMP_TABLE_IN_TRX_IN_SBR
  eng "Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP
TEMPORARY TABLE are not allowed inside a transaction or inside a procedure in a
transactional context when @@session.binlog_format=STATEMENT."
Write the above warning message to server error log when creating/droping
TEMPORARY TABLE inside a transaction or procedure, in case
@@session.binlog_format=STATEMENT and ENFORCE_GTID_CONSISTENCY = WARN.