Debian Codegraph @

mysql-test/suite/binlog/include/drop_temp_table.test

raw
1 --source include/have_innodb.inc
2
3 --disable_warnings
4 DROP DATABASE IF EXISTS `drop-temp+table-test`;
5 --enable_warnings
6
7 connect (con1,localhost,root,,);
8 connect (con2,localhost,root,,);
9 connection con1;
10 RESET MASTER;
11 CREATE DATABASE `drop-temp+table-test`;
12 USE `drop-temp+table-test`;
13 CREATE TEMPORARY TABLE shortn1 (a INT);
14 CREATE TEMPORARY TABLE `table:name` (a INT);
15 CREATE TEMPORARY TABLE shortn2 (a INT);
16
17 ##############################################################################
18 # MDEV-20091: DROP TEMPORARY TABLE IF EXISTS statements will be written
19 # to binlog only if the corresponding temporary table exists.
20 ##############################################################################
21 CREATE TEMPORARY TABLE tmp(c1 int);
22 CREATE TEMPORARY TABLE tmp1(c1 int);
23 CREATE TEMPORARY TABLE tmp2(c1 int);
24 CREATE TEMPORARY TABLE tmp3(c1 int);
25 CREATE TABLE t(c1 int);
26
27 DROP TEMPORARY TABLE IF EXISTS tmp;
28
29 --disable_warnings
30 # Post MDEV-20091: Following DROP TEMPORARY TABLE statement should not be
31 # logged as the table is already dropped above.
32 DROP TEMPORARY TABLE IF EXISTS tmp;
33
34 # Post MDEV-20091: Only DROP TEMPORARY TABLE statement should be written only
35 # for 'tmp1' table.
36 DROP TEMPORARY TABLE IF EXISTS tmp, tmp1;
37 DROP TEMPORARY TABLE tmp3;
38
39 #In RBR, tmp2 will NOT be binlogged, because it is a temporary table.
40 DROP TABLE IF EXISTS tmp2, t;
41
42 #In RBR, tmp2 will be binlogged, because it does not exist and master do not know
43 # whether it is a temporary table or not.
44 DROP TABLE IF EXISTS tmp2, t;
45 --enable_warnings
46
47 SELECT GET_LOCK("a",10);
48
49 #
50 # BUG48216 Replication fails on all slaves after upgrade to 5.0.86 on master
51 #
52 # When the session is closed, any temporary tables of the session are dropped
53 # and are binlogged. But it will be binlogged with a wrong database name when
54 # the length of the database name('drop-temp-table-test') is greater than the
55 # current database name('test').
56 #
57 USE test;
58 disconnect con1;
59
60 connection con2;
61 # We want to SHOW BINLOG EVENTS, to know what was logged. But there is no
62 # guarantee that logging of the terminated con1 has been done yet.
63 # To be sure that logging has been done, we use a user lock.
64 SELECT GET_LOCK("a",10);
65 let $VERSION=`SELECT VERSION()`;
66 source include/show_binlog_events.inc;
67 DROP DATABASE `drop-temp+table-test`;
68
69
70 #
71 # Bug #54842: DROP TEMPORARY TABLE not binlogged after manual switching binlog format to ROW
72 #
73 # Sanity test. Checking that implicit DROP event is logged.
74 #
75 # After BUG#52616, the switch to ROW mode becomes effective even
76 # if there are open temporary tables. As such the implicit drop
77 # for temporary tables on session closing must be logged.
78 #
79 # MDEV-20091: DROP TEMPORARY TABLE IF EXISTS statements will be written to
80 # binlog only if the corresponding temporary table exists. In row based
81 # replication temporary tables are not replicated hence their corresponding
82 # DROP TEMPORARY TABLE statement will be not be written to binary log upon
83 # session closure.
84 #
85
86 RESET MASTER;
87
88 CREATE TABLE t1 ( i text );
89
90 --connect(con1,localhost,root,,)
91 CREATE TEMPORARY TABLE ttmp1 ( i text );
92 SET @@session.binlog_format=ROW;
93 INSERT INTO t1 VALUES ('1');
94 SELECT @@session.binlog_format;
95 --disconnect con1
96
97 -- connection default
98 if (!`SELECT @@BINLOG_FORMAT = 'ROW'`) {
99 --let $wait_binlog_event= DROP
100 --source include/wait_for_binlog_event.inc
101 }
102 -- source include/show_binlog_events.inc
103 RESET MASTER;
104
105 DROP TABLE t1;
106
107 # End of 4.1 tests
108
109
110 --echo #
111 --echo # BUG#28642318: POINT IN TIME RECOVERY USING MYSQLBINLOG BROKEN
112 --echo # WITH TEMPORARY TABLE -> ERRORS
113
114 --echo # Test case for DELETE query.
115
116 RESET MASTER;
117 connect (con1,localhost,root,,);
118
119 --echo # Set up.
120 --connection default
121 --disable_warnings
122 SET @save_binlog_format= @@session.binlog_format;
123 SET @@session.binlog_format=STATEMENT;
124 let $MYSQLD_DATADIR= `select @@datadir`;
125 CREATE TABLE t1 (a INT) ENGINE=INNODB;
126
127 --connection con1
128 SET @@session.binlog_format=STATEMENT;
129 CREATE TEMPORARY TABLE t1 (b BLOB) ENGINE=INNODB;
130
131 --connection default
132 DELETE d1, d2 FROM t1 AS d1, t1 AS d2 WHERE d1.a<>d2.a;
133
134 --exec $MYSQL_BINLOG --force-if-open $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/bug28642318.sql
135
136 --connection default
137 DROP TABLE t1;
138
139 --echo # DELETE query fails with table re-open error without patch.
140 --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug28642318.sql
141
142 --echo # Clean up.
143 --connection con1
144 DROP TABLE IF EXISTS t1;
145
146 --connection default
147 DROP TABLE IF EXISTS t1;
148 RESET MASTER;
149
150 --echo # Test case for DROP query.
151
152 --connection default
153 CREATE TABLE t2 (a INT) ENGINE=INNODB;
154
155 --connection con1
156 CREATE TEMPORARY TABLE t2 (b BLOB) ENGINE=INNODB;
157
158 --connection default
159 DROP TABLE t2;
160
161 --connection con1
162 DROP TABLE t2;
163
164 --connection default
165 --exec $MYSQL_BINLOG --force-if-open $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/bug28642318.sql
166
167 --echo # DROP table query fails with unknown table error without patch.
168 --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug28642318.sql
169
170 --echo # Clean up
171 --connection default
172 SET @@session.binlog_format= @save_binlog_format;
173 RESET MASTER;
174
175 --disconnect con1
176 --enable_warnings