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
37
38
39
40
41
42
43
44
45
46
47
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
|
# ==== Purpose ====
#
# Test if statements used temporary tables are binlogged correctly
#
# ==== Method ====
#
# Use two connections, use temporary tables on both of them, and by
# switching connections between statements, the test can check if the
# statements are logged with the correct thread id.
#
# The statements current tested include:
# CREATE TEMPORARY TABLE
# CREATE TEMPORARY TABLE LIKE
# INSERT
# REPLACE
# UPDATE
# INSERT SELECT
# TRUNCATE
#
# Note: When adding new query statements, please add them between the
# two 'flush logs'. And aslo please make sure the connection is
# switched between each statement.
#
# ==== Related bugs ====
#
# BUG#35583 mysqlbinlog replay fails with ERROR 1146 when temp tables are used
#
source include/have_log_bin.inc;
source include/have_binlog_format_mixed_or_statement.inc;
RESET MASTER;
--disable_query_log
CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
--enable_query_log
connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,);
create table foo (a int);
flush logs;
connection master;
create temporary table tmp1_foo like foo;
connection master1;
create temporary table tmp2_foo (a int);
connection master;
insert into tmp1_foo values (1), (2), (3), (4);
connection master1;
replace into tmp2_foo values (1), (2), (3), (4);
connection master;
update tmp1_foo set a=2*a-1;
connection master1;
update tmp2_foo set a=2*a;
connection master;
delete from tmp1_foo where a < 5;
connection master1;
delete from tmp2_foo where a < 5;
--disable_warnings
connection master;
insert into foo select * from tmp1_foo;
connection master1;
insert into foo select * from tmp2_foo;
--enable_warnings
connection master;
truncate table tmp1_foo;
connection master1;
truncate table tmp2_foo;
let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
flush logs;
connection default;
select * from foo;
# prepare for the replay
drop table foo;
create table foo (a int);
# replay from binary log
let $MYSQLD_DATADIR= `select @@datadir`;
exec $MYSQL_BINLOG $MYSQLD_DATADIR/$binlog_file | $MYSQL;
select * from foo;
# clean up
drop table foo;
#################################################################
# BUG#51226
#################################################################
RESET MASTER;
-- let $dbname=b51226
connect (con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connect (con2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
#
# action: on con1 create the database and the tmp table
#
-- connection con1
-- eval create database $dbname
-- eval use $dbname
create temporary table t1(i int);
#
# action: on con1 create the tmp table
#
-- connection con2
-- eval use $dbname
create temporary table t1(i int);
# action: at this point, the last event binlogged contains the
# pseudo_thread_id from con2. So now we switch to con1, issue
# a statement that fails and close the connection (which logs
# implicitely a DROP TEMPORARY TABLE).
#
# Before the patch this would not log con1's pseudo_thread_id
# because the failing statement would reset THD context
# (unsetting the thread_specific_used flag, and consequently,
# causing the DROP event to be logged without pseudo_thread_id
# in its header).
-- connection con1
-- error 1050
create temporary table t1(i int);
-- disconnect con1
-- connection default
-- let $wait_binlog_event= DROP
-- source include/wait_for_binlog_event.inc
# action: insert in the t1. This would cause the the test to fail,
# because when replaying the binlog the previous implicit drop
# temp table would have been executed under the wrong
# pseudo_thread_id, dropping the tmp table on con2.
-- connection con2
insert into t1 values(1);
-- disconnect con2
-- connection default
-- let $wait_binlog_event= DROP
-- source include/wait_for_binlog_event.inc
-- eval DROP DATABASE $dbname
FLUSH LOGS;
# assertion: assert that when replaying the binary log will succeed,
# instead of failing with "Table 'XXX.YYY' doesn't exist"
-- let $MYSQLD_DATADIR= `select @@datadir`
-- exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL
|
