MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Li-Bing.Song Date:January 16 2010 7:28am
Subject:bzr commit into mysql-5.1-bugteam branch (Li-Bing.Song:3317) Bug#47418
View as plain text  
#At file:///home/anders/work/bzrwork/worktree3/mysql-5.1-bugteam/ based on revid:joro@stripped

 3317 Li-Bing.Song@stripped	2010-01-16
      BUG#47418 RBR fails, failure with mixup of base/temporary/view 
      
      'CREATE TABLE IF NOT EXISTS ... SELECT' statement were causing 'CREATE
      TEMPORARY TABLE ...' to be written to the binary log in row-based 
      mode (a.k.a. RBR), when there was a temporary table with the same name.
      Because the 'CREATE TABLE ... SELECT' statement was executed as 
      'INSERT ... SELECT' into the temporary table. Since in RBR mode no 
      other statements related to temporary tables are written into binary log,
      this sometimes broke replication.
      
      This patch changes behavior of 'CREATE TABLE [IF NOT EXISTS] ... SELECT ...'.
      it ignores existence of temporary table with the 
      same name as table being created and is interpreted
      as attempt to create/insert into base table. This makes behavior of
      'CREATE TABLE [IF NOT EXISTS] ... SELECT' consistent with
      how ordinary 'CREATE TABLE' and 'CREATE TABLE ... LIKE' behave.

    modified:
      mysql-test/mysql-test-run.pl
      mysql-test/r/create.result
      mysql-test/r/ps_ddl.result
      mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
      mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
      mysql-test/t/create.test
      mysql-test/t/ps_ddl.test
      sql/sql_parse.cc
      sql/sql_prepare.cc
=== modified file 'mysql-test/mysql-test-run.pl'
--- a/mysql-test/mysql-test-run.pl	2009-10-20 10:05:28 +0000
+++ b/mysql-test/mysql-test-run.pl	2010-01-16 07:27:53 +0000
@@ -1381,7 +1381,7 @@ sub set_build_thread_ports($) {
     $build_thread = 300;	# Start attempts from here
     while (! $found_free)
     {
-      $build_thread= mtr_get_unique_id($build_thread, 349);
+      $build_thread= mtr_get_unique_id($build_thread, 449);
       if ( !defined $build_thread ) {
         mtr_error("Could not get a unique build thread id");
       }

=== modified file 'mysql-test/r/create.result'
--- a/mysql-test/r/create.result	2009-09-04 06:57:10 +0000
+++ b/mysql-test/r/create.result	2010-01-16 07:27:53 +0000
@@ -820,16 +820,13 @@ i
 drop table t1;
 create temporary table t1 (j int);
 create table if not exists t1 select 1;
-Warnings:
-Note	1050	Table 't1' already exists
 select * from t1;
 j
-1
 drop temporary table t1;
 select * from t1;
-ERROR 42S02: Table 'test.t1' doesn't exist
+1
+1
 drop table t1;
-ERROR 42S02: Unknown table 't1'
 create table t1 (i int);
 insert into t1 values (1), (2);
 lock tables t1 read;

=== modified file 'mysql-test/r/ps_ddl.result'
--- a/mysql-test/r/ps_ddl.result	2008-08-13 19:42:21 +0000
+++ b/mysql-test/r/ps_ddl.result	2010-01-16 07:27:53 +0000
@@ -1695,23 +1695,23 @@ SUCCESS
 drop table t2;
 create temporary table t2 (a int);
 execute stmt;
-ERROR 42S01: Table 't2' already exists
 call p_verify_reprepare_count(1);
 SUCCESS
 
 execute stmt;
 ERROR 42S01: Table 't2' already exists
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
 SUCCESS
 
 drop temporary table t2;
 execute stmt;
-call p_verify_reprepare_count(1);
+ERROR 42S01: Table 't2' already exists
+call p_verify_reprepare_count(0);
 SUCCESS
 
 drop table t2;
 execute stmt;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
 SUCCESS
 
 drop table t2;

=== modified file 'mysql-test/suite/rpl/r/rpl_create_if_not_exists.result'
--- a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result	2009-08-29 08:52:22 +0000
+++ b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result	2010-01-16 07:27:53 +0000
@@ -31,3 +31,37 @@ 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	root@localhost	SYSTEM	ONE TIME	#	NULL	NULL	NULL	NULL	SLAVESIDE_DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
 DROP DATABASE IF EXISTS mysqltest;
+-------------BUG#47418-------------
+USE test;
+DROP TABLE IF EXISTS t3;
+CREATE TABLE t3(c1 INTEGER);
+INSERT INTO t3 VALUES(33);
+CREATE TEMPORARY TABLE t1(c1 INTEGER);
+CREATE TEMPORARY TABLE t2(c1 INTEGER);
+INSERT INTO t1 VALUES(1);
+INSERT INTO t2 VALUES(1);
+CREATE TABLE IF NOT EXISTS t1(c1 INTEGER) SELECT c1 FROM t3;
+CREATE TABLE t2(c1 INTEGER) SELECT c1 FROM t3;
+SELECT * FROM t1;
+c1
+1
+SELECT * FROM t2;
+c1
+1
+SELECT * FROM t1;
+c1
+33
+SELECT * FROM t2;
+c1
+33
+DROP TEMPORARY TABLE t1;
+DROP TEMPORARY TABLE t2;
+SELECT * FROM t1;
+c1
+33
+SELECT * FROM t2;
+c1
+33
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;

=== modified file 'mysql-test/suite/rpl/t/rpl_create_if_not_exists.test'
--- a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test	2009-08-13 02:48:57 +0000
+++ b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test	2010-01-16 07:27:53 +0000
@@ -67,4 +67,57 @@ SHOW EVENTS in mysqltest;
 
 connection master;
 DROP DATABASE IF EXISTS mysqltest;
+
+#
+# BUG#47418 RBR fails, failure with mixup of base/temporary/view TABLE DDL
+# 
+# Before the patch for this bug, 'CREATE TABLE IF NOT EXIST ... SELECT'
+# statement was binlogged as a TEMPORARY table if the object existed as
+# a temporary table.  This was caused by that the temporary table was opened
+# and the results of the 'SELECT' was inserted into the temporary table if
+# a temporary table existed with the same name.
+# 
+# After the patch for this bug, the base table is created and the results of
+# the 'SELECT' are inserted into it, even though a temporary table exists with
+# the same name, and the statement is still binlogged as a base table.
+#
+
+echo -------------BUG#47418-------------;
+connection master;
+USE test;
+DROP TABLE IF EXISTS t3;
+--enable_warnings
+CREATE TABLE t3(c1 INTEGER);
+INSERT INTO t3 VALUES(33);
+
+CREATE TEMPORARY TABLE t1(c1 INTEGER);
+CREATE TEMPORARY TABLE t2(c1 INTEGER);
+INSERT INTO t1 VALUES(1);
+INSERT INTO t2 VALUES(1);
+
+CREATE TABLE IF NOT EXISTS t1(c1 INTEGER) SELECT c1 FROM t3;
+CREATE TABLE t2(c1 INTEGER) SELECT c1 FROM t3;
+
+# In these two statements, t1 and t2 are the temporary table. there is only
+# value '1' in them.  The records of t2 are not inserted into them.  
+SELECT * FROM t1; 
+SELECT * FROM t2; 
+sync_slave_with_master; 
+
+# In these two statements, t1 and t2 are the base table. The recoreds of t2
+# are inserted into it when CREATE TABLE ...  SELECT was executed.  
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+connection master; 
+DROP TEMPORARY TABLE t1; 
+DROP TEMPORARY TABLE t2; 
+#In these two statements, t1 and t2 are the base table.
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+
 source include/master-slave-end.inc;

=== modified file 'mysql-test/t/create.test'
--- a/mysql-test/t/create.test	2009-09-04 06:57:10 +0000
+++ b/mysql-test/t/create.test	2010-01-16 07:27:53 +0000
@@ -721,16 +721,15 @@ drop table t1;
 # Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent
 # results of CREATE TABLE ... SELECT when temporary table exists").
 # In this situation we either have to create non-temporary table and
-# insert data in it or insert data in temporary table without creation
-# of permanent table. Since currently temporary tables always shadow
-# permanent tables we adopt second approach.
+# insert data in it or insert data in temporary table without creation of
+# permanent table. After patch for Bug#47418, we create the base table and
+# instert data into it, even though a temporary table exists with the same
+# name.
 create temporary table t1 (j int);
 create table if not exists t1 select 1;
 select * from t1;
 drop temporary table t1;
---error ER_NO_SUCH_TABLE
 select * from t1;
---error ER_BAD_TABLE_ERROR
 drop table t1;
 
 

=== modified file 'mysql-test/t/ps_ddl.test'
--- a/mysql-test/t/ps_ddl.test	2008-08-13 19:42:21 +0000
+++ b/mysql-test/t/ps_ddl.test	2010-01-16 07:27:53 +0000
@@ -1445,18 +1445,19 @@ call p_verify_reprepare_count(0);
 drop table t2;
 # Temporary table with name of table to be created exists
 create temporary table t2 (a int);
---error ER_TABLE_EXISTS_ERROR
+# Temporary table and base table are not in the same name space.
 execute stmt;
 call p_verify_reprepare_count(1);
 --error ER_TABLE_EXISTS_ERROR
 execute stmt;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
 drop temporary table t2;
+--error ER_TABLE_EXISTS_ERROR
 execute stmt;
-call p_verify_reprepare_count(1);
+call p_verify_reprepare_count(0);
 drop table t2;
 execute stmt;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
 drop table t2;
 # View with name of table to be created exists
 # Attention:

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2010-01-13 14:01:49 +0000
+++ b/sql/sql_parse.cc	2010-01-16 07:27:53 +0000
@@ -2659,6 +2659,8 @@ mysql_execute_command(THD *thd)
       {
         lex->link_first_table_back(create_table, link_to_local);
         create_table->create= TRUE;
+        /* Base table and temporary table are not in the same name space. */
+        create_table->skip_temporary= 1;
       }
 
       if (!(res= open_and_lock_tables(thd, lex->query_tables)))

=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc	2009-12-08 09:26:11 +0000
+++ b/sql/sql_prepare.cc	2010-01-16 07:27:53 +0000
@@ -1596,6 +1596,8 @@ static bool mysql_test_create_table(Prep
     {
       lex->link_first_table_back(create_table, link_to_local);
       create_table->create= TRUE;
+      /* Base table and temporary table are not in the same name space. */
+      create_table->skip_temporary= true;
     }
 
     if (open_normal_and_derived_tables(stmt->thd, lex->query_tables, 0))


Attachment: [text/bzr-bundle] bzr/li-bing.song@sun.com-20100116072753-0z8b21wf5pcxbbz4.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (Li-Bing.Song:3317) Bug#47418Li-Bing.Song16 Jan