2 # SP: CREATE DATABASE|TABLE IF NOT EXISTS not binlogged if routine exists.
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
10 # This problem caused some of the tests to fail randomly on PB or PB2.
13 # Fixed this bug by adding calls to write_bin_log in:
15 # mysql_create_table_no_lock
16 # mysql_create_like_table
17 # create_table_from_items
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;
24 # rpl_create_tmp_table_if_not_exists.test tests CREATE TEMPORARY TABLE cases.
27 # http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html
30 source include/master-slave.inc;
32 DROP DATABASE IF EXISTS mysqltest;
34 CREATE DATABASE IF NOT EXISTS 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
42 sync_slave_with_master;
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;
51 CREATE DATABASE IF NOT EXISTS mysqltest;
53 CREATE TABLE IF NOT EXISTS t(c1 int);
54 CREATE TABLE IF NOT EXISTS t1 LIKE t;
55 CREATE TABLE IF NOT EXISTS t2 SELECT * FROM t;
56 CREATE EVENT IF NOT EXISTS e
57 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
59 sync_slave_with_master;
62 SHOW TABLES in mysqltest;
63 #Execution time changes in each run. So we disregard it by calling replace_column.
65 SHOW EVENTS in mysqltest;
69 DROP DATABASE IF EXISTS mysqltest;
72 # BUG#47418 RBR fails, failure with mixup of base/temporary/view TABLE DDL
74 # Before the patch for this bug, 'CREATE TABLE IF NOT EXIST ... SELECT'
75 # statement was binlogged as a TEMPORARY table if the object existed as
76 # a temporary table. This was caused by that the temporary table was opened
77 # and the results of the 'SELECT' was inserted into the temporary table if
78 # a temporary table existed with the same name.
80 # After the patch for this bug, the base table is created and the results of
81 # the 'SELECT' are inserted into it, even though a temporary table exists with
82 # the same name, and the statement is still binlogged as a base table.
85 echo -------------BUG#47418-------------;
88 DROP TABLE IF EXISTS t3;
90 CREATE TABLE t3(c1 INTEGER);
91 INSERT INTO t3 VALUES(33);
93 CREATE TEMPORARY TABLE t1(c1 INTEGER);
94 CREATE TEMPORARY TABLE t2(c1 INTEGER);
95 INSERT INTO t1 VALUES(1);
96 INSERT INTO t2 VALUES(1);
98 CREATE TABLE IF NOT EXISTS t1(c1 INTEGER) SELECT c1 FROM t3;
99 CREATE TABLE t2(c1 INTEGER) SELECT c1 FROM t3;
101 # In these two statements, t1 and t2 are the temporary table. there is only
102 # value '1' in them. The records of t2 are not inserted into them.
105 sync_slave_with_master;
107 # In these two statements, t1 and t2 are the base table. The recoreds of t2
108 # are inserted into it when CREATE TABLE ... SELECT was executed.
113 DROP TEMPORARY TABLE t1;
114 DROP TEMPORARY TABLE t2;
115 #In these two statements, t1 and t2 are the base table.
122 sync_slave_with_master;
125 --echo # Bug#55616 Killing thread or query during CREATE IF NOT EXISTS makes
126 --echo # slave SQL thread abort
130 let $con_id = `SELECT CONNECTION_ID()`;
132 CREATE TABLE t1 ( i INT );
133 send CREATE TABLE IF NOT EXISTS t1
137 let $wait_timeout = 3;
138 let $show_statement = SHOW PROCESSLIST;
140 let $condition = = 'User sleep';
141 source include/wait_show_condition.inc;
143 --replace_result $con_id master1
144 eval KILL QUERY $con_id;
145 sync_slave_with_master;
150 --source include/rpl_end.inc