Debian Codegraph @

mysql-test/suite/binlog/t/binlog_tmp_table.test

raw
1 # ==== Purpose ====
2 #
3 # Test if statements used temporary tables are binlogged correctly
4 #
5 # ==== Method ====
6 #
7 # Use two connections, use temporary tables on both of them, and by
8 # switching connections between statements, the test can check if the
9 # statements are logged with the correct thread id.
10 #
11 # The statements current tested include:
12 # CREATE TEMPORARY TABLE
13 # CREATE TEMPORARY TABLE LIKE
14 # INSERT
15 # REPLACE
16 # UPDATE
17 # INSERT SELECT
18 # TRUNCATE
19 #
20 # Note: When adding new query statements, please add them between the
21 # two 'flush logs'. And aslo please make sure the connection is
22 # switched between each statement.
23 #
24 # ==== Related bugs ====
25 #
26 # BUG#35583 mysqlbinlog replay fails with ERROR 1146 when temp tables are used
27 #
28 source include/have_log_bin.inc;
29 source include/have_binlog_format_mixed_or_statement.inc;
30
31 RESET MASTER;
32
33 --disable_query_log
34 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
35 --enable_query_log
36
37 connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
38 connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,);
39
40 create table foo (a int);
41
42 flush logs;
43
44 connection master;
45 create temporary table tmp1_foo like foo;
46 connection master1;
47 create temporary table tmp2_foo (a int);
48
49 connection master;
50 insert into tmp1_foo values (1), (2), (3), (4);
51 connection master1;
52 replace into tmp2_foo values (1), (2), (3), (4);
53
54 connection master;
55 update tmp1_foo set a=2*a-1;
56 connection master1;
57 update tmp2_foo set a=2*a;
58
59 connection master;
60 delete from tmp1_foo where a < 5;
61 connection master1;
62 delete from tmp2_foo where a < 5;
63
64 --disable_warnings
65 connection master;
66 insert into foo select * from tmp1_foo;
67 connection master1;
68 insert into foo select * from tmp2_foo;
69 --enable_warnings
70
71 connection master;
72 truncate table tmp1_foo;
73 connection master1;
74 truncate table tmp2_foo;
75
76 let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
77
78 flush logs;
79
80 connection default;
81 select * from foo;
82
83 # prepare for the replay
84 drop table foo;
85 create table foo (a int);
86
87 # replay from binary log
88 let $MYSQLD_DATADIR= `select @@datadir`;
89 exec $MYSQL_BINLOG $MYSQLD_DATADIR/$binlog_file | $MYSQL;
90 select * from foo;
91
92 # clean up
93 drop table foo;
94
95 #################################################################
96 # BUG#51226
97 #################################################################
98
99 RESET MASTER;
100
101 -- let $dbname=b51226
102
103 connect (con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
104 connect (con2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
105
106 #
107 # action: on con1 create the database and the tmp table
108 #
109 -- connection con1
110 -- eval create database $dbname
111 -- eval use $dbname
112 create temporary table t1(i int);
113
114 #
115 # action: on con1 create the tmp table
116 #
117 -- connection con2
118 -- eval use $dbname
119 create temporary table t1(i int);
120
121 # action: at this point, the last event binlogged contains the
122 # pseudo_thread_id from con2. So now we switch to con1, issue
123 # a statement that fails and close the connection (which logs
124 # implicitely a DROP TEMPORARY TABLE).
125 #
126 # Before the patch this would not log con1's pseudo_thread_id
127 # because the failing statement would reset THD context
128 # (unsetting the thread_specific_used flag, and consequently,
129 # causing the DROP event to be logged without pseudo_thread_id
130 # in its header).
131
132 -- connection con1
133 -- error 1050
134 create temporary table t1(i int);
135 -- disconnect con1
136
137 -- connection default
138 -- let $wait_binlog_event= DROP
139 -- source include/wait_for_binlog_event.inc
140
141 # action: insert in the t1. This would cause the the test to fail,
142 # because when replaying the binlog the previous implicit drop
143 # temp table would have been executed under the wrong
144 # pseudo_thread_id, dropping the tmp table on con2.
145 -- connection con2
146 insert into t1 values(1);
147 -- disconnect con2
148
149 -- connection default
150 -- let $wait_binlog_event= DROP
151 -- source include/wait_for_binlog_event.inc
152
153 -- eval DROP DATABASE $dbname
154 FLUSH LOGS;
155
156 # assertion: assert that when replaying the binary log will succeed,
157 # instead of failing with "Table 'XXX.YYY' doesn't exist"
158 -- let $MYSQLD_DATADIR= `select @@datadir`
159 -- exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL