Debian Codegraph @

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