Debian Codegraph @

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

raw
1 # ==== Purpose ====
2 #
3 # Test that temporary tables are correctly replicated after switching to ROW format in MIX mode.
4 # This test case will test the condition of the bug#40013.
5 # The test step is:
6 # 1: create temp table on connection 'master';
7 # 2: switch to ROW format using 'INSERT INTO t1 VALUES (SYS_GUID());'
8 # 3: disconnect 'master' and connect to a new connection 'master1';
9 # 4: sync to slave and check the number of temp tables on slave.
10 #
11
12 source include/have_binlog_format_mixed.inc;
13 source include/have_innodb.inc;
14 source include/master-slave.inc;
15
16 --echo ==== Initialize ====
17
18 --connection master
19
20 CREATE TABLE t1 (a CHAR(48));
21 CREATE TEMPORARY TABLE t1_tmp1(a INT);
22 INSERT INTO t1 VALUES (SYS_GUID());
23
24 sync_slave_with_master;
25
26 --echo ==== Verify results on slave ====
27 SHOW STATUS LIKE "Slave_open_temp_tables";
28
29 --connection master
30
31 disconnect master;
32 --connection master1
33
34 # waiting DROP TEMPORARY TABLE event to be written into binlog
35 let $wait_binlog_event= DROP;
36 source include/wait_for_binlog_event.inc;
37
38 sync_slave_with_master;
39
40 --echo ==== Verify results on slave ====
41 SHOW STATUS LIKE "Slave_open_temp_tables";
42
43 --echo ==== Clean up ====
44
45 --let $rpl_connection_name= master
46 --let $rpl_server_number= 1
47 --source include/rpl_connect.inc
48 --connection master
49 DROP TABLE t1;
50
51 sync_slave_with_master;
52
53 #
54 # BUG#43046: mixed mode switch to row format with temp table lead to wrong
55 # result
56 #
57 # NOTES
58 # =====
59 #
60 # 1. Temporary tables cannot be logged using the row-based
61 # format. Thus, once row-based logging is used, all subsequent
62 # statements using that table are unsafe, and we approximate this
63 # condition by treating all statements made by that client as
64 # unsafe until the client no longer holds any temporary tables.
65 #
66 # 2. Two different connections can use the same temporary table
67 # name without conflicting with each other or with an
68 # existing non-TEMPORARY table of the same name.
69 #
70 # DESCRIPTION
71 # ===========
72 #
73 # The test is implemented as follows:
74 # 1. create regular tables
75 # 2. create a temporary table t1_tmp: should be logged as statement
76 # 3. issue an alter table: should be logged as statement
77 # 4. issue statement that forces switch to RBR
78 # 5. create another temporary table t2_tmp: should not be logged
79 # 6. issue alter table on t1_tmp: should not be logged
80 # 7. drop t1_tmp and regular table on same statement: should log both in
81 # statement format (but different statements)
82 # 8. issue deterministic insert: logged as row (because t2_tmp still
83 # exists).
84 # 9. drop t2_tmp and issue deterministic statement: should log drop and
85 # query in statement format (show switch back to STATEMENT format)
86 # 10. in the end the slave should not have open temp tables.
87 #
88
89 --source include/rpl_reset.inc
90 -- connection master
91
92 # action: setup environment
93 CREATE TABLE t1 (a int) engine=innodb;
94 CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) )
95 engine=innodb;
96 CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
97 CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ();
98
99 # assertion: assert that CREATE is logged as STATEMENT
100 CREATE TEMPORARY TABLE t1_tmp (i1 int);
101
102 # assertion: assert that ALTER TABLE is logged as STATEMENT
103 ALTER TABLE t1_tmp ADD COLUMN b INT;
104
105 # action: force switch to RBR
106 INSERT INTO t1 values(1);
107 INSERT INTO t2 (i1) select * from t1;
108
109 # assertion: assert that t2_tmp will not make into the binlog (RBR logging atm)
110 CREATE TEMPORARY TABLE t2_tmp (a int);
111
112 # assertion: assert that ALTER TABLE on t1_tmp will not make into the binlog
113 ALTER TABLE t1_tmp ADD COLUMN c INT;
114
115 -- echo ### assertion: assert that there is one open temp table on slave
116 -- sync_slave_with_master
117 SHOW STATUS LIKE 'Slave_open_temp_tables';
118
119 -- connection master
120
121 # assertion: assert that both drops are logged
122 DROP TABLE t1_tmp, t2;
123
124 # assertion: assert that statement is logged as row (master still has one
125 # opened temporary table - t2_tmp.
126 INSERT INTO t1 VALUES (1);
127
128 # assertion: assert that DROP TABLE *is* logged despite CREATE is not.
129 DROP TEMPORARY TABLE t2_tmp;
130
131 # assertion: assert that statement is now logged as STMT (mixed mode switches
132 # back to STATEMENT).
133 INSERT INTO t1 VALUES (2);
134
135 -- sync_slave_with_master
136
137 -- echo ### assertion: assert that slave has no temporary tables opened
138 SHOW STATUS LIKE 'Slave_open_temp_tables';
139
140 -- connection master
141
142 # action: drop remaining tables
143 DROP TABLE t3, t1;
144
145 -- sync_slave_with_master
146
147 -- source include/show_binlog_events.inc
148
149 --echo
150 --echo # Bug#55478 Row events wrongly apply on the temporary table of the same name
151 --echo # ==========================================================================
152 connection master;
153
154 let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
155 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
156
157 --echo # The statement should be binlogged
158 CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=InnoDB;
159
160 --echo
161 --echo # Case 1: CREATE TABLE t1 ... SELECT
162 --echo # ----------------------------------
163 let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
164 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
165
166 --echo
167 --echo # The statement generates row events on t1. And the rows events should
168 --echo # be inserted into the base table on slave.
169 CREATE TABLE t1 ENGINE=MyISAM SELECT rand();
170
171 source include/show_binlog_events.inc;
172 let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
173 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
174
175 --echo
176 --echo # Case 2: DROP TEMPORARY TABLE in a transacation
177 --echo # ----------------------------------------------
178 --echo
179
180 BEGIN;
181 DROP TEMPORARY TABLE t1;
182
183 # The patch for BUG#55478 fixed the problem only on RBR. The problem on SBR
184 # will be fixed by the patch for bug#55709. So This statement cannot be
185 # executed until Bug#55709 is fixed
186 #
187 # INSERT INTO t1 VALUES(1);
188
189 --echo # The rows event will binlogged after 'INSERT INTO t1 VALUES(1)'
190 --disable_warnings
191 INSERT IGNORE INTO t1 VALUES(SYS_GUID()+0);
192 --enable_warnings
193 COMMIT;
194
195 source include/show_binlog_events.inc;
196
197 --sync_slave_with_master
198
199 --echo # Compare the base table.
200 --let $diff_tables= master:t1, slave:t1
201 --source include/diff_tables.inc
202
203 --echo
204 connection master;
205 DROP TABLE t1;
206 --source include/rpl_end.inc