#At file:///home/anders/work/bzrwork/bug45574/mysql-5.1-bugteam/ based on revid:davi.arnaut@stripped
2941 Li-Bing.Song@stripped 2009-07-31
BUG#45574 CREATE IF NOT EXISTS is not binlogged if the object exists
There is an inconsistency with DROP DATABASE|TABLE IF EXISTS and CREATE
EVENT IF NOT EXISTS:DROP IF EXISTS statements are binlogged even if the DB or TABLE does not
exist, CREATE EVENT IF NOT EXISTS are binlogged even if the event exists. whereas CREATE
DATEBASE|TABLE IF NOT EXIST dose not binlog when the DB or TABLE exists.
This patch includes 2 tests to test all CREATE IF NOT EXISTS statement.
CREATE DATABASE IF NOT EXISTS,
CREATE TABLE IF NOT EXISTS,
CREATE TABLE IF NOT EXISTS ... LIKE have this bug.
CREAET TABLE IF NOT EXISTS ... SELECT has this bug in row mode.
This patch adds code to fix all above.
@ sql/sql_insert.cc
This paragraph codes was moved from create_table_from_items to select_create::prepare.
This paragraph codes check if the table which will be created exists.
When table existing and row mode, the CREATE TABLE IF NOT EXIST statement is binlogged.
added:
mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result
mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
mysql-test/suite/rpl/t/rpl_create_tmp_table_if_not_exists.test
modified:
sql/sql_db.cc
sql/sql_insert.cc
sql/sql_table.cc
=== added file 'mysql-test/suite/rpl/r/rpl_create_if_not_exists.result'
--- a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result 2009-07-31 12:28:47 +0000
@@ -0,0 +1,33 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+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();
+DROP DATABASE 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();
+SHOW TABLES in mysqltest;
+Tables_in_mysqltest
+t
+t1
+t2
+SHOW EVENTS in mysqltest;
+Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
+mysqltest e @ SYSTEM ONE TIME - NULL NULL NULL NULL SLAVESIDE_DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+DROP DATABASE IF EXISTS mysqltest;
=== added file 'mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result'
--- a/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result 2009-07-31 12:28:47 +0000
@@ -0,0 +1,22 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+DROP DATABASE IF EXISTS mysqltest;
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp(c1 int);
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp(c1 int);
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp;
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp;
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp;
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp;
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # DROP DATABASE IF EXISTS mysqltest
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp(c1 int)
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp(c1 int)
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp
=== added file 'mysql-test/suite/rpl/t/rpl_create_if_not_exists.test'
--- a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test 2009-07-31 12:28:47 +0000
@@ -0,0 +1,73 @@
+# 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 EVENT IF NOT EXISTS :DROP IF EXISTS statements are binlogged even
+# if either the DB, TABLE or EVENT does not exist, CREATE EVENT IF NOT
+# EXISTS is binlogged even if the EVENT exists. In contrast, CREATE
+# DATEBASE|TABLE IF NOT EXISTS are not binlogged when either the DB or TABLE
+# 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;
+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;
+#Executing time changes each test. this test do't care the executing time.
+#It is replaced by '-'.
+replace_column 6 -;
+SHOW EVENTS in mysqltest;
+
+
+connection master;
+DROP DATABASE IF EXISTS mysqltest;
+sync_slave_with_master;
+source include/master-slave-end.inc;
=== added file 'mysql-test/suite/rpl/t/rpl_create_tmp_table_if_not_exists.test'
--- a/mysql-test/suite/rpl/t/rpl_create_tmp_table_if_not_exists.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_create_tmp_table_if_not_exists.test 2009-07-31 12:28:47 +0000
@@ -0,0 +1,42 @@
+# 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 EVENT IF NOT EXISTS :DROP IF EXISTS statements are binlogged even
+# if either the DB, TABLE or EVENT does not exist, CREATE EVENT IF NOT
+# EXISTS is binlogged even if the EVENT exists. In contrast, CREATE
+# DATEBASE|TABLE IF NOT EXISTS are not binlogged when either the DB or TABLE
+# exists.
+#
+# This problem caused some of the tests to fail randomly on PB or PB2.
+#
+# Test is implemented as follows:
+#
+# i) test each "CREATE TEMPORARY TABLE IF EXISTS" (DDL), found in MySQL
+# 5.1 manual, on existent objects;
+# ii) show binlog events;
+#
+# Note:
+# rpl_create_if_not_exists.test tests other cases.
+#
+# References:
+# http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html
+#
+
+source include/master-slave.inc;
+#CREATE TEMPORARY TABLE statements are not binlogged in row mode,
+#So it must be test by itself.
+source include/have_binlog_format_mixed_or_statement.inc;
+disable_warnings;
+
+DROP DATABASE IF EXISTS mysqltest;
+
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp(c1 int);
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp(c1 int);
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp;
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp;
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp;
+CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp;
+source include/show_binlog_events.inc;
+
+source include/master-slave-end.inc;
=== modified file 'sql/sql_db.cc'
--- a/sql/sql_db.cc 2009-05-30 13:32:28 +0000
+++ b/sql/sql_db.cc 2009-07-31 12:28:47 +0000
@@ -658,10 +658,8 @@ int mysql_create_db(THD *thd, char *db,
}
push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
ER_DB_CREATE_EXISTS, ER(ER_DB_CREATE_EXISTS), db);
- if (!silent)
- my_ok(thd);
error= 0;
- goto exit;
+ goto not_silent;
}
else
{
@@ -698,7 +696,8 @@ int mysql_create_db(THD *thd, char *db,
happened. (This is a very unlikely senario)
*/
}
-
+
+not_silent:
if (!silent)
{
char *query;
=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc 2009-06-04 23:30:08 +0000
+++ b/sql/sql_insert.cc 2009-07-31 12:28:47 +0000
@@ -3364,25 +3364,6 @@ static TABLE *create_table_from_items(TH
bool not_used;
DBUG_ENTER("create_table_from_items");
- DBUG_EXECUTE_IF("sleep_create_select_before_check_if_exists", my_sleep(6000000););
-
- if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE) &&
- create_table->table->db_stat)
- {
- /* Table already exists and was open at open_and_lock_tables() stage. */
- if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
- {
- create_info->table_existed= 1; // Mark that table existed
- push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
- ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
- create_table->table_name);
- DBUG_RETURN(create_table->table);
- }
-
- my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_table->table_name);
- DBUG_RETURN(0);
- }
-
tmp_table.alias= 0;
tmp_table.timestamp_field= 0;
tmp_table.s= &share;
@@ -3584,10 +3565,33 @@ select_create::prepare(List<Item> &value
thd->binlog_start_trans_and_stmt();
}
- if (!(table= create_table_from_items(thd, create_info, create_table,
- alter_info, &values,
- &extra_lock, hook_ptr)))
- DBUG_RETURN(-1); // abort() deletes table
+ DBUG_EXECUTE_IF("sleep_create_select_before_check_if_exists", my_sleep(6000000););
+
+ if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE) &&
+ create_table->table->db_stat)
+ {
+ /* Table already exists and was open at open_and_lock_tables() stage. */
+ if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
+ {
+ create_info->table_existed= 1; // Mark that table existed
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
+ create_table->table_name);
+ if (thd->current_stmt_binlog_row_based)
+ binlog_show_create_table(&(create_table->table), 1);
+ table= create_table->table;
+ }
+ else
+ {
+ my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_table->table_name);
+ DBUG_RETURN(-1);
+ }
+ }
+ else
+ if (!(table= create_table_from_items(thd, create_info, create_table,
+ alter_info, &values,
+ &extra_lock, hook_ptr)))
+ DBUG_RETURN(-1); // abort() deletes table
if (extra_lock)
{
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2009-06-02 09:07:17 +0000
+++ b/sql/sql_table.cc 2009-07-31 12:28:47 +0000
@@ -3432,6 +3432,41 @@ void sp_prepare_create_field(THD *thd, C
/*
+ Write CREATE TABLE binlog
+
+ SYNOPSIS
+ write_create_table_bin_log()
+ thd Thread object
+ create_info Create information
+ internal_tmp_table Set to 1 if this is an internal temporary table
+
+ DESCRIPTION
+ This function only is called in mysql_create_table_no_lock and
+ mysql_create_table
+
+ RETURN VALUES
+ NONE
+ */
+static inline void write_create_table_bin_log(THD *thd,
+ const HA_CREATE_INFO *create_info,
+ bool internal_tmp_table)
+{
+ /*
+ Don't write statement if:
+ - It is an internal temporary table,
+ - Row-based logging is used and it we are creating a temporary table, or
+ - The binary log is not open.
+ Otherwise, the statement shall be binlogged.
+ */
+ if (!internal_tmp_table &&
+ (!thd->current_stmt_binlog_row_based ||
+ (thd->current_stmt_binlog_row_based &&
+ !(create_info->options & HA_LEX_CREATE_TMP_TABLE))))
+ write_bin_log(thd, TRUE, thd->query, thd->query_length);
+}
+
+
+/*
Create a table
SYNOPSIS
@@ -3694,6 +3729,7 @@ bool mysql_create_table_no_lock(THD *thd
ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
alias);
error= 0;
+ write_create_table_bin_log(thd, create_info, internal_tmp_table);
goto err;
}
my_error(ER_TABLE_EXISTS_ERROR, MYF(0), alias);
@@ -3814,18 +3850,7 @@ bool mysql_create_table_no_lock(THD *thd
thd->thread_specific_used= TRUE;
}
- /*
- Don't write statement if:
- - It is an internal temporary table,
- - Row-based logging is used and it we are creating a temporary table, or
- - The binary log is not open.
- Otherwise, the statement shall be binlogged.
- */
- if (!internal_tmp_table &&
- (!thd->current_stmt_binlog_row_based ||
- (thd->current_stmt_binlog_row_based &&
- !(create_info->options & HA_LEX_CREATE_TMP_TABLE))))
- write_bin_log(thd, TRUE, thd->query, thd->query_length);
+ write_create_table_bin_log(thd, create_info, internal_tmp_table);
error= FALSE;
unlock_and_end:
VOID(pthread_mutex_unlock(&LOCK_open));
@@ -3841,6 +3866,7 @@ warn:
ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
alias);
create_info->table_existed= 1; // Mark that table existed
+ write_create_table_bin_log(thd, create_info, internal_tmp_table);
goto unlock_and_end;
}
@@ -3892,6 +3918,7 @@ bool mysql_create_table(THD *thd, const
table_name);
create_info->table_existed= 1;
result= FALSE;
+ write_create_table_bin_log(thd, create_info, internal_tmp_table);
}
else
{
@@ -5231,6 +5258,24 @@ bool mysql_create_like_table(THD* thd, T
goto err; /* purecov: inspected */
}
+goto binlog;
+
+table_exists:
+ if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
+ {
+ char warn_buff[MYSQL_ERRMSG_SIZE];
+ my_snprintf(warn_buff, sizeof(warn_buff),
+ ER(ER_TABLE_EXISTS_ERROR), table_name);
+ push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_TABLE_EXISTS_ERROR,warn_buff);
+ }
+ else
+ {
+ my_error(ER_TABLE_EXISTS_ERROR, MYF(0), table_name);
+ goto err;
+ }
+
+binlog:
DBUG_EXECUTE_IF("sleep_create_like_before_binlogging", my_sleep(6000000););
/*
@@ -5294,20 +5339,6 @@ bool mysql_create_like_table(THD* thd, T
write_bin_log(thd, TRUE, thd->query, thd->query_length);
res= FALSE;
- goto err;
-
-table_exists:
- if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
- {
- char warn_buff[MYSQL_ERRMSG_SIZE];
- my_snprintf(warn_buff, sizeof(warn_buff),
- ER(ER_TABLE_EXISTS_ERROR), table_name);
- push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
- ER_TABLE_EXISTS_ERROR,warn_buff);
- res= FALSE;
- }
- else
- my_error(ER_TABLE_EXISTS_ERROR, MYF(0), table_name);
err:
if (name_lock)
Attachment: [text/bzr-bundle] bzr/li-bing.song@sun.com-20090731122847-2t1i1l9w3usvk4vw.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (Li-Bing.Song:2941) Bug#45574 | Li-Bing.Song | 31 Jul |
