#At file:///home/anders/work/bzrwork/bug45574/mysql-5.1-bugteam/ based on revid:mattias.jonsson@stripped
3067 Li-Bing.Song@stripped 2009-08-13
BUG#45574 CREATE IF NOT EXISTS is not binlogged if the object exists
There is an inconsistency with DROP DATABASE|TABLE|EVENT IF EXISTS and
CREATE DATABASE|TABLE|EVENT IF NOT EXISTS. DROP IF EXISTS statements are
binlogged even if either the DB, TABLE or EVENT does not exist. In
contrast, Only the CREATE EVENT IF NOT EXISTS is binlogged when the EVENT
exists.
This patch fixes the following cases for all the replication formats:
CREATE DATABASE IF NOT EXISTS.
CREATE TABLE IF NOT EXISTS,
CREATE TABLE IF NOT EXISTS ... LIKE,
CREAET TABLE IF NOT EXISTS ... SELECT.
@ sql/sql_insert.cc
Part of the code was moved from the create_table_from_items to select_create::prepare.
When replication is row based, CREATE TABLE IF NOT EXISTS.. SELECT is binlogged if the table exists.
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-08-13 02:48:57 +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-08-13 02:48:57 +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-08-13 02:48:57 +0000
@@ -0,0 +1,70 @@
+# 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 DATABASE|TABLE|EVENT IF NOT EXISTS. DROP IF EXISTS statements are
+# binlogged even if either the DB, TABLE or EVENT does not exist. In
+# contrast, Only the CREATE EVENT IF NOT EXISTS is binlogged when the EVENT
+# 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;
+#Execution time changes in each run. So we disregard it by calling replace_column.
+replace_column 6 #;
+SHOW EVENTS in mysqltest;
+
+
+connection master;
+DROP DATABASE IF EXISTS mysqltest;
+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-08-13 02:48:57 +0000
@@ -0,0 +1,41 @@
+# 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 DATABASE|TABLE|EVENT IF NOT EXISTS. DROP IF EXISTS statements are
+# binlogged even if either the DB, TABLE or EVENT does not exist. In
+# contrast, Only the CREATE EVENT IF NOT EXISTS is binlogged when the EVENT
+# 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-06-19 08:24:43 +0000
+++ b/sql/sql_db.cc 2009-08-13 02:48:57 +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-08-11 16:29:45 +0000
+++ b/sql/sql_insert.cc 2009-08-13 02:48:57 +0000
@@ -3392,25 +3392,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;
@@ -3612,10 +3593,35 @@ 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)
+ {
+ /* Mark that table existed */
+ create_info->table_existed= 1;
+ 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)))
+ /* abort() deletes table */
+ DBUG_RETURN(-1);
if (extra_lock)
{
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2009-07-29 08:54:20 +0000
+++ b/sql/sql_table.cc 2009-08-13 02:48:57 +0000
@@ -3476,6 +3476,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
@@ -3738,6 +3773,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);
@@ -3858,18 +3894,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));
@@ -3885,6 +3910,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;
}
@@ -3936,6 +3962,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
{
@@ -5276,6 +5303,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););
/*
@@ -5339,20 +5384,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-20090813024857-1emgf5lhj0zikoj8.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (Li-Bing.Song:3067) Bug#45574 | Li-Bing.Song | 20 Aug |
