3
# Test if statements used temporary tables are binlogged correctly
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.
11
# The statements current tested include:
12
# CREATE TEMPORARY TABLE
13
# CREATE TEMPORARY TABLE LIKE
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.
24
# ==== Related bugs ====
26
# BUG#35583 mysqlbinlog replay fails with ERROR 1146 when temp tables are used
28
source include/have_log_bin.inc;
29
source include/have_binlog_format_mixed_or_statement.inc;
34
CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
37
connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
38
connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,);
40
create table foo (a int);
45
create temporary table tmp1_foo like foo;
47
create temporary table tmp2_foo (a int);
50
insert into tmp1_foo values (1), (2), (3), (4);
52
replace into tmp2_foo values (1), (2), (3), (4);
55
update tmp1_foo set a=2*a-1;
57
update tmp2_foo set a=2*a;
60
delete from tmp1_foo where a < 5;
62
delete from tmp2_foo where a < 5;
66
insert into foo select * from tmp1_foo;
68
insert into foo select * from tmp2_foo;
72
truncate table tmp1_foo;
74
truncate table tmp2_foo;
76
let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
83
# prepare for the replay
85
create table foo (a int);
87
# replay from binary log
88
let $MYSQLD_DATADIR= `select @@datadir`;
89
exec $MYSQL_BINLOG $MYSQLD_DATADIR/$binlog_file | $MYSQL;
95
#################################################################
97
#################################################################
101
-- let $dbname=b51226
103
connect (con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
104
connect (con2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
107
# action: on con1 create the database and the tmp table
110
-- eval create database $dbname
112
create temporary table t1(i int);
115
# action: on con1 create the tmp table
119
create temporary table t1(i int);
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).
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
134
create temporary table t1(i int);
137
-- connection default
138
-- let $wait_binlog_event= DROP
139
-- source include/wait_for_binlog_event.inc
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.
146
insert into t1 values(1);
149
-- connection default
150
-- let $wait_binlog_event= DROP
151
-- source include/wait_for_binlog_event.inc
153
-- eval DROP DATABASE $dbname
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