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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
|
--source include/have_innodb.inc
--disable_warnings
DROP DATABASE IF EXISTS `drop-temp+table-test`;
--enable_warnings
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
connection con1;
RESET MASTER;
CREATE DATABASE `drop-temp+table-test`;
USE `drop-temp+table-test`;
CREATE TEMPORARY TABLE shortn1 (a INT);
CREATE TEMPORARY TABLE `table:name` (a INT);
CREATE TEMPORARY TABLE shortn2 (a INT);
##############################################################################
# MDEV-20091: DROP TEMPORARY TABLE IF EXISTS statements will be written
# to binlog only if the corresponding temporary table exists.
##############################################################################
CREATE TEMPORARY TABLE tmp(c1 int);
CREATE TEMPORARY TABLE tmp1(c1 int);
CREATE TEMPORARY TABLE tmp2(c1 int);
CREATE TEMPORARY TABLE tmp3(c1 int);
CREATE TABLE t(c1 int);
DROP TEMPORARY TABLE IF EXISTS tmp;
--disable_warnings
# Post MDEV-20091: Following DROP TEMPORARY TABLE statement should not be
# logged as the table is already dropped above.
DROP TEMPORARY TABLE IF EXISTS tmp;
# Post MDEV-20091: Only DROP TEMPORARY TABLE statement should be written only
# for 'tmp1' table.
DROP TEMPORARY TABLE IF EXISTS tmp, tmp1;
DROP TEMPORARY TABLE tmp3;
#In RBR, tmp2 will NOT be binlogged, because it is a temporary table.
DROP TABLE IF EXISTS tmp2, t;
#In RBR, tmp2 will be binlogged, because it does not exist and master do not know
# whether it is a temporary table or not.
DROP TABLE IF EXISTS tmp2, t;
--enable_warnings
SELECT GET_LOCK("a",10);
#
# BUG48216 Replication fails on all slaves after upgrade to 5.0.86 on master
#
# When the session is closed, any temporary tables of the session are dropped
# and are binlogged. But it will be binlogged with a wrong database name when
# the length of the database name('drop-temp-table-test') is greater than the
# current database name('test').
#
USE test;
disconnect con1;
connection con2;
# We want to SHOW BINLOG EVENTS, to know what was logged. But there is no
# guarantee that logging of the terminated con1 has been done yet.
# To be sure that logging has been done, we use a user lock.
SELECT GET_LOCK("a",10);
let $VERSION=`SELECT VERSION()`;
source include/show_binlog_events.inc;
DROP DATABASE `drop-temp+table-test`;
#
# Bug #54842: DROP TEMPORARY TABLE not binlogged after manual switching binlog format to ROW
#
# Sanity test. Checking that implicit DROP event is logged.
#
# After BUG#52616, the switch to ROW mode becomes effective even
# if there are open temporary tables. As such the implicit drop
# for temporary tables on session closing must be logged.
#
# MDEV-20091: DROP TEMPORARY TABLE IF EXISTS statements will be written to
# binlog only if the corresponding temporary table exists. In row based
# replication temporary tables are not replicated hence their corresponding
# DROP TEMPORARY TABLE statement will be not be written to binary log upon
# session closure.
#
RESET MASTER;
CREATE TABLE t1 ( i text );
--connect(con1,localhost,root,,)
CREATE TEMPORARY TABLE ttmp1 ( i text );
SET @@session.binlog_format=ROW;
INSERT INTO t1 VALUES ('1');
SELECT @@session.binlog_format;
--disconnect con1
-- connection default
if (!`SELECT @@BINLOG_FORMAT = 'ROW'`) {
--let $wait_binlog_event= DROP
--source include/wait_for_binlog_event.inc
}
-- source include/show_binlog_events.inc
RESET MASTER;
DROP TABLE t1;
# End of 4.1 tests
--echo #
--echo # BUG#28642318: POINT IN TIME RECOVERY USING MYSQLBINLOG BROKEN
--echo # WITH TEMPORARY TABLE -> ERRORS
--echo # Test case for DELETE query.
RESET MASTER;
connect (con1,localhost,root,,);
--echo # Set up.
--connection default
--disable_warnings
SET @save_binlog_format= @@session.binlog_format;
SET @@session.binlog_format=STATEMENT;
let $MYSQLD_DATADIR= `select @@datadir`;
CREATE TABLE t1 (a INT) ENGINE=INNODB;
--connection con1
SET @@session.binlog_format=STATEMENT;
CREATE TEMPORARY TABLE t1 (b BLOB) ENGINE=INNODB;
--connection default
DELETE d1, d2 FROM t1 AS d1, t1 AS d2 WHERE d1.a<>d2.a;
--exec $MYSQL_BINLOG --force-if-open $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/bug28642318.sql
--connection default
DROP TABLE t1;
--echo # DELETE query fails with table re-open error without patch.
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug28642318.sql
--echo # Clean up.
--connection con1
DROP TABLE IF EXISTS t1;
--connection default
DROP TABLE IF EXISTS t1;
RESET MASTER;
--echo # Test case for DROP query.
--connection default
CREATE TABLE t2 (a INT) ENGINE=INNODB;
--connection con1
CREATE TEMPORARY TABLE t2 (b BLOB) ENGINE=INNODB;
--connection default
DROP TABLE t2;
--connection con1
DROP TABLE t2;
--connection default
--exec $MYSQL_BINLOG --force-if-open $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/bug28642318.sql
--echo # DROP table query fails with unknown table error without patch.
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug28642318.sql
--echo # Clean up
--connection default
SET @@session.binlog_format= @save_binlog_format;
RESET MASTER;
--disconnect con1
--enable_warnings
|
