rpl_temp_temporary.test 7.66 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
################################################################################
# BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a
#           transaction
# BUG#52616 Temp table prevents switch binlog format from STATEMENT to ROW
#
# This test verifies what follows:
#
# 1 - If the following statements are classified as unsafe:
#   1.1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
#   1.2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
#
# 2 - If the following statements are classified as safe:
#   1.7 - INSERT INTO t_innodb SELECT * FROM t_myisam_temp
#   1.8 - INSERT INTO t_myisam_temp SELECT * FROM t_innodb
#
# 3 - If we can switch from statement to row format.
#
# 4 - If statements that can manipulate a temporary table and do not cause an
# implicit commit are kept in the boundaries of an on-going transaction. For
# instance:
#   4.1 - CREATE TEMPORARY TABLE
#   4.2 - CREATE TEMPORARY TABLE LIKE
#   4.3 - CREATE TEMPORARY TABLE SELECT * FROM
#   4.4 - INSERT/UPDATE/DELETE (Note that only inserts are verified)
#   4.5 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
#   4.6 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
#   4.7 - INSERT INTO t_innodb SELECT * FROM t_myisam_temp
#   4.8 - INSERT INTO t_myisam_temp SELECT * FROM t_innodb
#
# 5 - If transactions that have a DROP TEMPORARY are always written to the 
# binary log regardless of the mode and the outcome: commit or rollback.
#
# 6 - In particular, if the current statement logging format is set to row
# the CREATE TEMPORARY is not logged and the DROP TEMPORARY is extended with
# the IF EXISTS clause.
#
37 38 39 40
# 7 - It also verifies if the CONNECTION_ID along with a non-transactional
# table is written outside the transaction boundaries and is not classified
# as unsafe. See BUG#53075.
#
41 42 43 44 45 46
################################################################################

source include/master-slave.inc;
source include/have_binlog_format_statement.inc;
source include/have_innodb.inc;

47
CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169

--echo ########################################################################
--echo #                         VERIFY ITEMS 1 and 2
--echo ########################################################################

--echo #
--echo # Create temporary tables to verify when statements involving temporary
--echo # tables are classified as safe or unsafe by printing out the warning
--echo # messages.
--echo #
connection master;
CREATE TABLE t_myisam(id int) engine= MyIsam;
INSERT INTO t_myisam VALUES(1);
CREATE TABLE t_innodb(id int) engine= Innodb;
INSERT INTO t_innodb VALUES(1);
CREATE TEMPORARY TABLE t_myisam_temp(id int) engine= MyIsam;
INSERT INTO t_myisam_temp VALUES(1);
CREATE TEMPORARY TABLE t_innodb_temp(id int) engine= Innodb;
INSERT INTO t_innodb_temp VALUES(1);

let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
BEGIN;
INSERT INTO t_myisam SELECT * FROM t_myisam_temp;
INSERT INTO t_innodb SELECT * FROM t_myisam_temp;
INSERT INTO t_innodb SELECT * FROM t_innodb_temp;
ROLLBACK;

BEGIN;
INSERT INTO t_myisam SELECT * FROM t_innodb_temp;
INSERT INTO t_innodb SELECT * FROM t_myisam_temp;
INSERT INTO t_innodb SELECT * FROM t_innodb_temp;
ROLLBACK;

--echo ########################################################################
--echo #                          VERIFY ITEM 3
--echo ########################################################################

--echo #
--echo # When there is a temporary table we can switch between the mixed and
--echo # row formats. However, we cannot switch to the statement format.
--echo #
SET BINLOG_FORMAT=MIXED;
SET BINLOG_FORMAT=ROW;
SET BINLOG_FORMAT=MIXED;
--error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
SET BINLOG_FORMAT=STATEMENT;

--echo #
--echo # When there is a temporary table we can switch between the mixed and
--echo # row formats. However, we cannot swith to the statement format.
--echo #
SET BINLOG_FORMAT=MIXED;
DROP TABLE t_myisam_temp, t_innodb_temp, t_myisam, t_innodb;
source include/show_binlog_events.inc;

--echo ########################################################################
--echo #                          VERIFY ITEMS 4 and 5
--echo ########################################################################

--echo #
let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
BEGIN;
CREATE TEMPORARY TABLE tmp1(id int) engine= MyIsam;
INSERT INTO tmp1 VALUES(1);
CREATE TEMPORARY TABLE tmp2 LIKE tmp1;
INSERT INTO tmp2 VALUES(1);
CREATE TEMPORARY TABLE tmp3 engine= MyIsam SELECT * FROM tmp1;
INSERT INTO tmp3 VALUES(1);
DROP TEMPORARY TABLE tmp1;
DROP TEMPORARY TABLE tmp2;
DROP TEMPORARY TABLE tmp3;
COMMIT;
source include/show_binlog_events.inc;

let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
BEGIN;
CREATE TEMPORARY TABLE tmp1(id int) engine= MyIsam;
INSERT INTO tmp1 VALUES(1);
CREATE TEMPORARY TABLE tmp2 LIKE tmp1;
INSERT INTO tmp2 VALUES(1);
CREATE TEMPORARY TABLE tmp3 engine= MyIsam SELECT * FROM tmp1;
INSERT INTO tmp3 VALUES(1);
DROP TEMPORARY TABLE tmp1;
DROP TEMPORARY TABLE tmp2;
DROP TEMPORARY TABLE tmp3;
ROLLBACK;
source include/show_binlog_events.inc;

--echo ########################################################################
--echo #                          VERIFY ITEM 6
--echo ########################################################################

SET BINLOG_FORMAT=STATEMENT;
let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
CREATE TEMPORARY TABLE tmp1(id int) engine= MyIsam;
CREATE TEMPORARY TABLE tmp2 LIKE tmp1;
CREATE TEMPORARY TABLE tmp3 engine= MyIsam SELECT * FROM tmp1;
SET BINLOG_FORMAT=ROW;
DROP TEMPORARY TABLE tmp1;
BEGIN;
INSERT INTO tmp2 VALUES(1);
DROP TEMPORARY TABLE tmp2;
INSERT INTO tmp3 VALUES(1);
DROP TEMPORARY TABLE tmp3;
COMMIT;
source include/show_binlog_events.inc;

SET BINLOG_FORMAT=STATEMENT;
let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
CREATE TEMPORARY TABLE tmp1(id int) engine= MyIsam;
CREATE TEMPORARY TABLE tmp2 LIKE tmp1;
CREATE TEMPORARY TABLE tmp3 engine= MyIsam SELECT * FROM tmp1;
SET BINLOG_FORMAT=ROW;
DROP TEMPORARY TABLE tmp1;
BEGIN;
INSERT INTO tmp2 VALUES(1);
DROP TEMPORARY TABLE tmp2;
INSERT INTO tmp3 VALUES(1);
DROP TEMPORARY TABLE tmp3;
ROLLBACK;
source include/show_binlog_events.inc;

170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192
--echo ########################################################################
--echo #                          VERIFY ITEM 7
--echo ########################################################################

SET BINLOG_FORMAT=STATEMENT;
let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);

CREATE TEMPORARY TABLE tmp1(id int) engine= MyIsam;
CREATE TABLE t_myisam (f1 BIGINT) ENGINE = MyISAM;
CREATE TABLE t_innodb (f1 BIGINT) ENGINE = Innodb;

BEGIN;
INSERT INTO t_myisam VALUES(CONNECTION_ID());
INSERT INTO tmp1 VALUES(1);
INSERT INTO t_myisam VALUES(1);
INSERT INTO t_innodb VALUES(1);
INSERT INTO t_myisam VALUES(CONNECTION_ID());
INSERT INTO t_innodb VALUES(1);
COMMIT;
DROP TABLE t_myisam;
DROP TABLE t_innodb;
source include/show_binlog_events.inc;

193 194 195 196 197 198 199 200 201
--echo ###################################################################################
--echo #                               CHECK CONSISTENCY
--echo ###################################################################################
connection master;
sync_slave_with_master;

--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/test-nmt-master.sql
--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/test-nmt-slave.sql
--diff_files $MYSQLTEST_VARDIR/tmp/test-nmt-master.sql $MYSQLTEST_VARDIR/tmp/test-nmt-slave.sql
202 203