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.
123
source include/master-slave-end.inc;