polardbxengine/mysql-test/extra/rpl_tests/rpl_split_statements.test

# ==== Purpose ====
#
# A few special SQL statements/constructs can generate multiple
# transactions in the binary log.  This poses interesting problems
# especially when it comes to generating and preserving GTIDs.
#
# There are four cases to consider:
#
# - When GTID_MODE=ON/ON_PERMISSIVE and GTID_NEXT=AUTOMATIC, a
#   separate GTID should be generated for each of the statements.
#
# - When GTID_MODE=ON and GTID_NEXT=UUID:NUMBER, an error should be
#   generated since it is impossible to log the statement using just
#   the given GTID.  The exact point when error can be generated
#   (before starting to execute, after executing the first transaction
#   of the statement, or after executing all transactions of the
#   statement) depends on the statement.
#
# - When GTID_MODE=OFF/OFF_PERMISSIVE and GTID_NEXT=AUTOMATIC, an
#   Anonymous_gtid_log_event should be generated for each of the
#   statements.
#
# - When GTID_MODE=OFF and GTID_NEXT=ANONYMOUS, an
#   Anonymous_gtid_log_event should be generated for each of the
#   statements.  Moreover, anonymous ownership should not be released
#   until the last transaction generated by the statement is written
#   to the binary log.
#
# The following statements can generate multiple transactions in the
# binary log:
#
#  1. CALL: when a stored procedure executes multiple statements in
#     autocommit mode, each statement will be logged as a separate
#     transaction.
#
#  2. DROP TABLE: when a single statement drops multiple tables, then
#     there will be a separate statement for all dropped non-temporary
#     tables, with statement binlog format a separate statement for all
#     dropped transactional temporary tables, and a separate statement
#     for all dropped non-transactional temporary tables. Moreover,
#     statement for all dropped non-temporary tables can be split
#     further. If no GTID is assigned then there will be individual
#     DROP TABLE statements for each dropped table in SEs which don't
#     support atomic DDL and single statement for all dropped tables in
#     SEs supporting atomic DDL. If original DROP TABLE statement is
#     supposed to be executed under single GTID then all dropped base
#     tables (both supporting atomic DDL and not) are combined in a
#     single statement. Execution of statements which drop both
#     temporary and non-temporary tables or two kinds of temporary
#     tables under single GTID is not allowed.
#
#  3. DROP DATABASE: See details in rpl_split_statements_debug.test
#
#  4. CREATE TABLE ... SELECT: this statement contains both DDL and
#     DML.  When binlog_format='ROW', all DML must be logged in row
#     format, but at the same time DDL can only be logged in statement
#     format.  Therefore this statement is logged as a CREATE TABLE
#     followed by row events.  This statement is not allowed when
#     GTID_MODE = ON.
#
# This test verifies that all these statements work, for all values of
# GTID_MODE/GTID_NEXT, and both on a client connection and in a slave
# connection.
#
# ==== Implementation ====
#
#  1. CALL. We execute a multi-transaction CALL:
#
#     - On master with GTID_MODE='AUTOMATIC';
#     - On master with GTID_MODE!='AUTOMATIC' ('ANONYMOUS'/'UUID:NUMBER',
#       depending on GTID_MODE)
#     - Not on slave. Since CALL does not get logged as CALL, there is
#       no way to get it in the binary log.
#
#  2. DROP TABLE.
#
#     First, we drop all combinations of two or three tables, from the
#     different categories non-temporary, temporary transactional, and
#     temporary non-transactional.  This is done in three ways:
#
#     - On master with GTID_MODE='AUTOMATIC'
#     - On master with GTID_MODE!='AUTOMATIC' ('ANONYMOUS'/'UUID:NUMBER',
#       depending on GTID_MODE)
#     - On slave. This can be done using different table definitions
#       on master and slave. On master we use only non-temporary
#       tables, so that any DROP TABLE statement will succeed and make
#       it to the binary log.  In order to make the tables temporary
#       on slave, we first create the non-temporary table on master,
#       then sync it to slave, then drop it on slave, and then use a
#       stored procedure on master that creates a temporary table with
#       the same name *only* when it executes on the slave.
#
#     Second, we drop group of 4 tables, where 2 tables are in SE which
#     do not support atomic DDL and 2 in SE which support it. We do it
#     in both GTID_MODE='AUTOMATIC' and GTID_MODE='UUID:NUMBER' to see
#     if they are split into several/combined into single statement
#     according to GTID_MODE.
#
#     Third, we drop group of 4 tables, where 2 tables are in SE which
#     do not support atomic DDL and 2 in SE which support it and then
#     fail. This triggers code path in DROP TABLE implementation
#     which differs from case when there is no failure. This test has
#     been moved into rpl_split_statements_debug.test.
#
#     Note that two last tests also provide coverage for changes to binary
#     logging and GTID handling for normal and failed DROP TABLES, which
#     were implemented as part of WL#7743 "New data dictionary: changes to
#     DDL-related parts of SE API".
#
#  3. DROP DATABASE. Tests for this statement were moved into
#     rpl_split_statements_debug.test.
#
#  4. CREATE TABLE ... SELECT.  This is tested in a different test
#     (rpl_gtid_create_select.test), since the statement cannot be
#     executed if gtid_mode=on.
#
# ==== Reference ====
#
# WL#7592: GTIDs: Generate Gtid_log_event and Previous_gtids_log_event always
# - Test was introduced by this worklog.

# It suffices to test one binlog_format.
--source include/have_binlog_format_statement.inc

--let $rpl_gtid_utils= 1
--source include/master-slave.inc

CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
if ($gtid_mode_on)
{
  CALL mtr.add_suppression('Cannot execute statement because it needs to be written to the binary log as multiple statements');
  CALL mtr.add_suppression('DROP DATABASE failed; some tables may have been dropped but the database directory remains.');
}
--connection slave
CALL mtr.add_suppression("Error dropping database");
CALL mtr.add_suppression("Can't drop database '.*'; database doesn't exist");
CALL mtr.add_suppression("Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. .* Error_code: 1756");
--connection master

--let $gtid_step_gtid_mode_agnostic= 1

--echo ==== Case 1: CALL is split on master ====

# Note: CALL cannot be executed on a slave, since CALL statements are
# not written as CALL in the binary log.

--echo ---- Initialize ----

CREATE TABLE t (a INT);
--delimiter |
CREATE PROCEDURE proc ()
BEGIN
  INSERT INTO t VALUES (1);
  INSERT INTO t VALUES (2);
END|
--delimiter ;

--echo ---- GTID_NEXT=AUTOMATIC ----

--source include/gtid_step_reset.inc

CALL proc();

--let $gtid_step_count= 2
--source include/gtid_step_assert.inc

--let $assert_cond= COUNT(*) = 2 FROM t
--let $assert_text= Both rows were inserted
--source include/assert.inc

DELETE FROM t;

--echo ---- GTID_NEXT=non-automatic ----

--source include/gtid_step_reset.inc

--source include/set_gtid_next_gtid_mode_agnostic.inc

if ($gtid_mode_on)
{
  --replace_result $server_1_uuid MASTER_UUID
  --error ER_GTID_NEXT_TYPE_UNDEFINED_GTID
  CALL proc();
}
if (!$gtid_mode_on)
{
  CALL proc();
}
SET GTID_NEXT= 'AUTOMATIC';

--let $gtid_step_count= 1
--source include/gtid_step_assert.inc

if ($gtid_mode_on)
{
  --let $assert_cond= COUNT(*) = 1 FROM t
}
if (!$gtid_mode_on)
{
  --let $assert_cond= COUNT(*) = 2 FROM t
}
--let $assert_text= One row inserted if GTID_MODE=ON, two if GTID_MODE=OFF
--source include/assert.inc

DROP TABLE t;
DROP PROCEDURE proc;

--echo ==== Case 2A: DROP TABLE is split on master ====

--echo ---- Initialize ----

--delimiter |
CREATE PROCEDURE create_tables()
BEGIN
  CREATE TABLE base (a INT) ENGINE = InnoDB;
  CREATE TEMPORARY TABLE temp_t (a INT) ENGINE = InnoDB;
  CREATE TEMPORARY TABLE temp_n (a INT) ENGINE = MyISAM;
END|
CREATE PROCEDURE drop_tables()
BEGIN
  DROP TABLE IF EXISTS base;
  DROP TABLE IF EXISTS temp_t;
  DROP TABLE IF EXISTS temp_n;
END|
--delimiter ;

--source include/rpl_sync.inc

--echo ---- GTID_MODE=AUTOMATIC ----

--let $automatic= 1
--source extra/rpl_tests/rpl_drop_multiple_tables_in_multiple_ways.inc

--echo ---- GTID_MODE=non-automatic ----

--let $automatic= 0
--source extra/rpl_tests/rpl_drop_multiple_tables_in_multiple_ways.inc

--echo ==== Case 2B: DROP TABLE is split on slave ====

--echo ---- Initialize ----

CREATE TABLE dummy (a INT);
DROP PROCEDURE create_tables;

--delimiter |
CREATE FUNCTION create_tables_func() RETURNS INT
BEGIN
  IF @@GLOBAL.SERVER_ID = 2 THEN
    CREATE TEMPORARY TABLE temp_t (a INT) ENGINE = InnoDB;
    CREATE TEMPORARY TABLE temp_n (a INT) ENGINE = MyISAM;
  END IF;
  RETURN 0;
END|
CREATE PROCEDURE create_tables()
BEGIN
  CREATE TABLE base (a INT);
  SET @@SESSION.SQL_LOG_BIN = 0;
  CREATE TABLE temp_t (a INT);
  CREATE TABLE temp_n (a INT);
  SET @@SESSION.SQL_LOG_BIN = 1;
  INSERT INTO dummy VALUES (create_tables_func());
END|
--delimiter ;

--source include/rpl_sync.inc

--echo ---- GTID_MODE=AUTOMATIC ----

--let $automatic= 1
--let $transaction_count= 1
--source extra/rpl_tests/rpl_drop_multiple_tables_in_multiple_ways.inc

--echo ---- Clean up ----

DROP FUNCTION create_tables_func;
DROP PROCEDURE create_tables;
DROP PROCEDURE drop_tables;
DROP TABLE dummy;

--echo ==== Case 2C: DROP TABLES for base tables in engines with and without atomic DDL support ====

CREATE TABLE base_1_n (a INT) ENGINE = MyISAM;
CREATE TABLE base_2_n (a INT) ENGINE = MyISAM;
CREATE TABLE base_3_a (a INT) ENGINE = InnoDB;
CREATE TABLE base_4_a (a INT) ENGINE = InnoDB;

--source include/rpl_sync.inc

--echo ---- GTID_MODE=AUTOMATIC ----

--source include/gtid_step_reset.inc

DROP TABLES base_1_n, base_2_n, base_3_a, base_4_a;

--echo # In AUTOMATIC mode the above statement should be split into three:
--echo # two statements for each of MyISAM tables and single statement
--echo # dropping both of InnoDB tables.
--let $gtid_step_count= 3
--source include/gtid_step_assert.inc

CREATE TABLE base_1_n (a INT) ENGINE = MyISAM;
CREATE TABLE base_2_n (a INT) ENGINE = MyISAM;
CREATE TABLE base_3_a (a INT) ENGINE = InnoDB;
CREATE TABLE base_4_a (a INT) ENGINE = InnoDB;

--source include/rpl_sync.inc

--echo ---- GTID_NEXT=non-automatic ----

--source include/gtid_step_reset.inc

--source include/set_gtid_next_gtid_mode_agnostic.inc

DROP TABLES base_1_n, base_2_n, base_3_a, base_4_a;

SET GTID_NEXT= 'AUTOMATIC';

if ($gtid_mode_on)
{
--echo # With GTID assigned the above statement should not be split.
--let $gtid_step_count= 1
--source include/gtid_step_assert.inc
}
if (!$gtid_mode_on)
{
--echo # Without GTID assigned the above statement should be split into
--echo # three as in AUTOMATIC mode.
--let $gtid_step_count= 3
--source include/gtid_step_assert.inc
}

--source include/rpl_sync.inc

--echo ==== Case 2D: Failing DROP TABLES for base tables with and without atomic DDL support ====

--echo See rpl_split_statements_debug.test

--echo ==== Case 3: DROP DATABASE ====

--echo See rpl_split_statements_debug.test

--echo ==== Case 4: CREATE TABLE ... SELECT ====

--echo See rpl_gtid_create_select.test

--source include/rpl_end.inc