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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
|
# BUG#45574:
# SP: CREATE DATABASE|TABLE IF NOT EXISTS not binlogged if routine exists.
#
# There is an inconsistency with DROP DATABASE|TABLE|EVENT IF EXISTS and
# CREATE DATABASE|TABLE|EVENT IF NOT EXISTS. DROP IF EXISTS statements are
# binlogged even if either the DB, TABLE or EVENT does not exist. In
# contrast, Only the CREATE EVENT IF NOT EXISTS is binlogged when the EVENT
# exists.
#
# This problem caused some of the tests to fail randomly on PB or PB2.
#
# Description:
# Fixed this bug by adding calls to write_bin_log in:
# mysql_create_db
# mysql_create_table_no_lock
# mysql_create_like_table
# create_table_from_items
#
# Test is implemented as follows:
# i) test each "CREATE IF NOT EXISTS" (DDL), found in MySQL 5.1 manual
# exclude CREATE TEMPORARY TABLE, on existent objects;
#
# Note:
# rpl_create_tmp_table_if_not_exists.test tests CREATE TEMPORARY TABLE cases.
#
# References:
# http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html
#
source include/master-slave.inc;
disable_warnings;
DROP DATABASE IF EXISTS mysqltest;
CREATE DATABASE IF NOT EXISTS mysqltest;
USE mysqltest;
CREATE TABLE IF NOT EXISTS t(c1 int);
CREATE TABLE IF NOT EXISTS t1 LIKE t;
CREATE TABLE IF NOT EXISTS t2 SELECT * FROM t;
CREATE EVENT IF NOT EXISTS e
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO SELECT now();
sync_slave_with_master;
connection slave;
#DROP database from slave.
#The database and all tables can be recreated in slave
#if binlog of the second CREATE command is recorded and sent from master to slave.
DROP DATABASE mysqltest;
connection master;
CREATE DATABASE IF NOT EXISTS mysqltest;
USE mysqltest;
CREATE TABLE IF NOT EXISTS t(c1 int);
CREATE TABLE IF NOT EXISTS t1 LIKE t;
# The following will not be logged because t2 existed and we will not
# put the data of SELECT into the binary log
CREATE TABLE IF NOT EXISTS t2 SELECT * FROM t;
CREATE EVENT IF NOT EXISTS e
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO SELECT now();
sync_slave_with_master;
connection slave;
SHOW TABLES in mysqltest;
#Execution time changes in each run. So we disregard it by calling replace_column.
replace_column 6 #;
SHOW EVENTS in mysqltest;
connection master;
DROP DATABASE IF EXISTS mysqltest;
#
# BUG#47418 RBR fails, failure with mixup of base/temporary/view TABLE DDL
#
# Before the patch for this bug, 'CREATE TABLE IF NOT EXIST ... SELECT'
# statement was binlogged as a TEMPORARY table if the object existed as
# a temporary table. This was caused by that the temporary table was opened
# and the results of the 'SELECT' was inserted into the temporary table if
# a temporary table existed with the same name.
#
# After the patch for this bug, the base table is created and the results of
# the 'SELECT' are inserted into it, even though a temporary table exists with
# the same name, and the statement is still binlogged as a base table.
#
echo -------------BUG#47418-------------;
connection master;
USE test;
DROP TABLE IF EXISTS t3;
--enable_warnings
CREATE TABLE t3(c1 INTEGER);
INSERT INTO t3 VALUES(33);
CREATE TEMPORARY TABLE t1(c1 INTEGER);
CREATE TEMPORARY TABLE t2(c1 INTEGER);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1);
CREATE TABLE IF NOT EXISTS t1(c1 INTEGER) SELECT c1 FROM t3;
CREATE TABLE t2(c1 INTEGER) SELECT c1 FROM t3;
# In these two statements, t1 and t2 are the temporary table. there is only
# value '1' in them. The records of t2 are not inserted into them.
SELECT * FROM t1;
SELECT * FROM t2;
sync_slave_with_master;
# In these two statements, t1 and t2 are the base table. The records of t2
# are inserted into it when CREATE TABLE ... SELECT was executed.
SELECT * FROM t1;
SELECT * FROM t2;
connection master;
DROP TEMPORARY TABLE t1;
DROP TEMPORARY TABLE t2;
#In these two statements, t1 and t2 are the base table.
SELECT * FROM t1;
SELECT * FROM t2;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
--echo # WL#5370
--echo # The behavior of statement 'CREATE TABLE SELECT IF NOT EXISTS' is changed.
--echo # After the worklog, it will insert nothing and the statement will not be
--echo # binlogged if the table already exists.
--echo # After the worklog, some bugs will disappear automotically.
--source include/rpl_reset.inc
--echo
--echo # Case 1: BUG#47132
connection master;
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*");
CREATE TABLE t1 (id int);
CREATE TABLE t2 (id int);
INSERT INTO t1 VALUES (1), (1);
INSERT INTO t2 VALUES (2), (2);
CREATE VIEW v1 AS SELECT id FROM t2;
--let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)
CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id as di FROM t1;
--source include/show_binlog_events.inc
SHOW CREATE TABLE v1;
SELECT * FROM t2;
SELECT * FROM v1;
DROP VIEW v1;
# the warning only happens on SBR, so we disable it.
--disable_warnings
CREATE TEMPORARY TABLE tt1 AS SELECT id FROM t2;
--enable_warnings
--let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)
CREATE TEMPORARY TABLE IF NOT EXISTS tt1(a int, b int) SELECT id, id FROM t1;
--source include/show_binlog_events.inc
SELECT * FROM t2;
SELECT * FROM tt1;
DROP TEMPORARY TABLE tt1;
--echo
--echo # Case 1: BUG#47132
--echo # RBR breaks on CREATE TABLE IF EXISTS <existing VIEW> AS SELECT
CREATE VIEW v1 AS SELECT 1 as a;
--let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)
CREATE TABLE IF NOT EXISTS v1 SELECT 2 as a;
--source include/show_binlog_events.inc
sync_slave_with_master;
connection master;
DROP VIEW v1;
DROP TABLE t1, t2;
--echo #
--echo # Test case which has failed on assertion after refactoring which was
--echo # made as part of fix for bug #27480 "Extend CREATE TEMPORARY TABLES
--echo # privilege to allow temp table operations".
--echo #
CREATE TEMPORARY TABLE t1 (id int);
CREATE TABLE IF NOT EXISTS t2 LIKE t1;
--echo # The below statement should succeed with warning and
--echo # should not crash due to failing assertion.
CREATE TABLE IF NOT EXISTS t2 LIKE t1;
--echo # Clean-up.
DROP TABLE t1, t2;
sync_slave_with_master;
connection master;
--source include/rpl_end.inc
|
