Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/rpl_temp_table_mix_row.test

raw
1 # BUG#43046: mixed mode switch to row format with temp table lead to wrong
2 # result
3 #
4 # NOTES
5 # =====
6 #
7 # 1. Two different connections can use the same temporary table
8 # name without conflicting with each other or with an
9 # existing non-TEMPORARY table of the same name.
10 #
11 # DESCRIPTION
12 # ===========
13 #
14 # The test is implemented as follows:
15 # 1. create regular tables
16 # 2. create a temporary table t1_tmp: should not be logged
17 # 3. issue an alter table: should not be logged
18 # 4. issue statement that forces the query to be logged in Row format.
19 # 5. create another temporary table t2_tmp: should not be logged
20 # 6. issue alter table on t1_tmp: should not be logged
21 # 7. drop t1_tmp and regular table on same statement: should log both in
22 # statement format (but different statements)
23 # 8. issue deterministic insert: logged as statement as the statement is
24 # safe.
25 # 9. drop t2_tmp and issue deterministic statement: should log drop and
26 # query in statement format.
27 # 10. in the end the slave should not have open temp tables.
28 #
29
30 --source include/set_privilege_checks_user_as_system_user.inc
31 source include/master-slave.inc;
32 source include/have_binlog_format_mixed.inc;
33 source include/have_myisam.inc;
34
35 -- connection master
36
37 # action: setup environment
38 CREATE TABLE t1 (a int);
39 CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
40 CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
41 CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ();
42
43 # assertion: assert that CREATE is not logged.
44 CREATE TEMPORARY TABLE t1_tmp (i1 int);
45
46 # assertion: assert that ALTER TABLE is not logged.
47 ALTER TABLE t1_tmp ADD COLUMN b INT;
48
49 # action: Log this in ROW format
50 DELETE FROM t2;
51
52 # assertion: assert that t2_tmp will not make into the binlog.
53 CREATE TEMPORARY TABLE t2_tmp (a int);
54
55 # assertion: assert that ALTER TABLE on t1_tmp will not make into the binlog
56 ALTER TABLE t1_tmp ADD COLUMN c INT;
57
58 -- echo ### assertion: assert that slave has no temp table opened.
59 --source include/sync_slave_sql_with_master.inc
60 SHOW STATUS LIKE 'Replica_open_temp_tables';
61
62 -- connection master
63
64 # assertion: assert that both drops are logged
65 DROP TABLE t1_tmp, t2;
66
67 # assertion: assert that statement is logged as statement format.
68
69 INSERT INTO t1 VALUES (1);
70
71 # assertion: assert that DROP TABLE *is* logged despite CREATE is not.
72 DROP TEMPORARY TABLE t2_tmp;
73
74 # assertion: assert that statement is now logged as STMT.
75 INSERT INTO t1 VALUES (2);
76
77 --source include/sync_slave_sql_with_master.inc
78
79 -- echo ### assertion: assert that slave has no temporary tables opened
80 SHOW STATUS LIKE 'Replica_open_temp_tables';
81
82 -- connection master
83
84 # action: drop remaining tables
85 DROP TABLE t3, t1;
86
87 --source include/sync_slave_sql_with_master.inc
88
89 -- source include/show_binlog_events.inc
90
91 --echo
92 --echo # Bug#55478 Row events wrongly apply on the temporary table of the same name
93 --echo # ==========================================================================
94 connection master;
95
96 let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
97 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
98
99 --echo # The statement should be binlogged
100 CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=InnoDB;
101
102 --echo
103 --echo # Case 1: CREATE TABLE t1 ... SELECT
104 --echo # ----------------------------------
105 let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
106 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
107
108 --echo
109 --echo # The statement generates row events on t1. And the rows events should
110 --echo # be inserted into the base table on slave.
111 CREATE TABLE t1 ENGINE=MyISAM SELECT rand();
112
113 source include/show_binlog_events.inc;
114 let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
115 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
116
117 --echo
118 --echo # Case 2: DROP TEMPORARY TABLE in a transacation
119 --echo # ----------------------------------------------
120 --echo
121
122 BEGIN;
123 DROP TEMPORARY TABLE t1;
124
125 # The patch for BUG#55478 fixed the problem only on RBR. The problem on SBR
126 # will be fixed by the patch for bug#55709. So This statement cannot be
127 # executed until Bug#55709 is fixed
128 #
129 #INSERT INTO t1 VALUES(1);
130
131 --echo # The rows event will binlogged before 'DROP TEMPORARY TABLE t1',
132 --echo # as t1 is non-transactional table
133 INSERT INTO t1 VALUES(Rand());
134 COMMIT;
135
136 source include/show_binlog_events.inc;
137
138 --source include/sync_slave_sql_with_master.inc
139
140 --echo # Compare the base table.
141 --let $diff_tables= master:t1, slave:t1
142 --source include/diff_tables.inc
143
144 --echo
145 connection master;
146 DROP TABLE t1;
147
148 --source include/sync_slave_sql_with_master.inc
149
150 --source include/rpl_end.inc