Debian Codegraph @

mysql-test/suite/rpl/t/rpl_create_if_not_exists.test

raw
1 # BUG#45574:
2 # SP: CREATE DATABASE|TABLE IF NOT EXISTS not binlogged if routine exists.
3 #
4 # There is an inconsistency with DROP DATABASE|TABLE|EVENT IF EXISTS and
5 # CREATE DATABASE|TABLE|EVENT IF NOT EXISTS. DROP IF EXISTS statements are
6 # binlogged even if either the DB, TABLE or EVENT does not exist. In
7 # contrast, Only the CREATE EVENT IF NOT EXISTS is binlogged when the EVENT
8 # exists.
9 #
10 # This problem caused some of the tests to fail randomly on PB or PB2.
11 #
12 # Description:
13 # Fixed this bug by adding calls to write_bin_log in:
14 # mysql_create_db
15 # mysql_create_table_no_lock
16 # mysql_create_like_table
17 # create_table_from_items
18 #
19 # Test is implemented as follows:
20 # i) test each "CREATE IF NOT EXISTS" (DDL), found in MySQL 5.1 manual
21 # exclude CREATE TEMPORARY TABLE, on existent objects;
22 #
23 # Note:
24 # rpl_create_tmp_table_if_not_exists.test tests CREATE TEMPORARY TABLE cases.
25 #
26 # References:
27 # http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html
28 #
29
30 source include/master-slave.inc;
31 disable_warnings;
32 DROP DATABASE IF EXISTS mysqltest;
33
34 CREATE DATABASE IF NOT EXISTS mysqltest;
35 USE mysqltest;
36 CREATE TABLE IF NOT EXISTS t(c1 int);
37 CREATE TABLE IF NOT EXISTS t1 LIKE t;
38 CREATE TABLE IF NOT EXISTS t2 SELECT * FROM t;
39 CREATE EVENT IF NOT EXISTS e
40 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
41 DO SELECT now();
42 sync_slave_with_master;
43
44 connection slave;
45 #DROP database from slave.
46 #The database and all tables can be recreated in slave
47 #if binlog of the second CREATE command is recorded and sent from master to slave.
48 DROP DATABASE mysqltest;
49
50 connection master;
51 CREATE DATABASE IF NOT EXISTS mysqltest;
52 USE mysqltest;
53 CREATE TABLE IF NOT EXISTS t(c1 int);
54 CREATE TABLE IF NOT EXISTS t1 LIKE t;
55 # The following will not be logged because t2 existed and we will not
56 # put the data of SELECT into the binary log
57 CREATE TABLE IF NOT EXISTS t2 SELECT * FROM t;
58 CREATE EVENT IF NOT EXISTS e
59 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
60 DO SELECT now();
61 sync_slave_with_master;
62
63 connection slave;
64 SHOW TABLES in mysqltest;
65 #Execution time changes in each run. So we disregard it by calling replace_column.
66 replace_column 6 #;
67 SHOW EVENTS in mysqltest;
68
69
70 connection master;
71 DROP DATABASE IF EXISTS mysqltest;
72
73 #
74 # BUG#47418 RBR fails, failure with mixup of base/temporary/view TABLE DDL
75 #
76 # Before the patch for this bug, 'CREATE TABLE IF NOT EXIST ... SELECT'
77 # statement was binlogged as a TEMPORARY table if the object existed as
78 # a temporary table. This was caused by that the temporary table was opened
79 # and the results of the 'SELECT' was inserted into the temporary table if
80 # a temporary table existed with the same name.
81 #
82 # After the patch for this bug, the base table is created and the results of
83 # the 'SELECT' are inserted into it, even though a temporary table exists with
84 # the same name, and the statement is still binlogged as a base table.
85 #
86
87 echo -------------BUG#47418-------------;
88 connection master;
89 USE test;
90 DROP TABLE IF EXISTS t3;
91 --enable_warnings
92 CREATE TABLE t3(c1 INTEGER);
93 INSERT INTO t3 VALUES(33);
94
95 CREATE TEMPORARY TABLE t1(c1 INTEGER);
96 CREATE TEMPORARY TABLE t2(c1 INTEGER);
97 INSERT INTO t1 VALUES(1);
98 INSERT INTO t2 VALUES(1);
99
100 CREATE TABLE IF NOT EXISTS t1(c1 INTEGER) SELECT c1 FROM t3;
101 CREATE TABLE t2(c1 INTEGER) SELECT c1 FROM t3;
102
103 # In these two statements, t1 and t2 are the temporary table. there is only
104 # value '1' in them. The records of t2 are not inserted into them.
105 SELECT * FROM t1;
106 SELECT * FROM t2;
107 sync_slave_with_master;
108
109 # In these two statements, t1 and t2 are the base table. The records of t2
110 # are inserted into it when CREATE TABLE ... SELECT was executed.
111 SELECT * FROM t1;
112 SELECT * FROM t2;
113
114 connection master;
115 DROP TEMPORARY TABLE t1;
116 DROP TEMPORARY TABLE t2;
117 #In these two statements, t1 and t2 are the base table.
118 SELECT * FROM t1;
119 SELECT * FROM t2;
120
121 DROP TABLE t1;
122 DROP TABLE t2;
123 DROP TABLE t3;
124
125 --echo # WL#5370
126 --echo # The behavior of statement 'CREATE TABLE SELECT IF NOT EXISTS' is changed.
127 --echo # After the worklog, it will insert nothing and the statement will not be
128 --echo # binlogged if the table already exists.
129 --echo # After the worklog, some bugs will disappear automotically.
130 --source include/rpl_reset.inc
131
132 --echo
133 --echo # Case 1: BUG#47132
134 connection master;
135 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*");
136
137 CREATE TABLE t1 (id int);
138 CREATE TABLE t2 (id int);
139 INSERT INTO t1 VALUES (1), (1);
140 INSERT INTO t2 VALUES (2), (2);
141
142 CREATE VIEW v1 AS SELECT id FROM t2;
143 --let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)
144 CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id as di FROM t1;
145 --source include/show_binlog_events.inc
146
147 SHOW CREATE TABLE v1;
148 SELECT * FROM t2;
149 SELECT * FROM v1;
150 DROP VIEW v1;
151
152 # the warning only happens on SBR, so we disable it.
153 --disable_warnings
154 CREATE TEMPORARY TABLE tt1 AS SELECT id FROM t2;
155 --enable_warnings
156
157 --let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)
158 CREATE TEMPORARY TABLE IF NOT EXISTS tt1(a int, b int) SELECT id, id FROM t1;
159 --source include/show_binlog_events.inc
160 SELECT * FROM t2;
161 SELECT * FROM tt1;
162 DROP TEMPORARY TABLE tt1;
163
164 --echo
165 --echo # Case 1: BUG#47132
166 --echo # RBR breaks on CREATE TABLE IF EXISTS <existing VIEW> AS SELECT
167 CREATE VIEW v1 AS SELECT 1 as a;
168 --let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)
169 CREATE TABLE IF NOT EXISTS v1 SELECT 2 as a;
170 --source include/show_binlog_events.inc
171 sync_slave_with_master;
172
173 connection master;
174 DROP VIEW v1;
175
176 DROP TABLE t1, t2;
177
178
179 --echo #
180 --echo # Test case which has failed on assertion after refactoring which was
181 --echo # made as part of fix for bug #27480 "Extend CREATE TEMPORARY TABLES
182 --echo # privilege to allow temp table operations".
183 --echo #
184 CREATE TEMPORARY TABLE t1 (id int);
185 CREATE TABLE IF NOT EXISTS t2 LIKE t1;
186 --echo # The below statement should succeed with warning and
187 --echo # should not crash due to failing assertion.
188 CREATE TABLE IF NOT EXISTS t2 LIKE t1;
189 --echo # Clean-up.
190 DROP TABLE t1, t2;
191 sync_slave_with_master;
192 connection master;
193
194
195 --source include/rpl_end.inc