bzr commit into mysql-6.0-codebase-bugfixing branch (Li-Bing.Song:3649) Bug#47442

[Li-Bing.Song]

#At file:///home/anders/work/bzrwork/worktree4/mysql-6.0-codebase-bugfixing/ based on revid:alik@ibmvm-20091008093643-fjns7fky4mc00jfw

 3649 Li-Bing.Song@stripped	2009-11-05
      BUG#47442 BR breaks on CREATE TABLE IF EXISTS <existing VIEW> AS SELECT
      
      'CREATE TABLE IF NOT EXIST ... SELECT' statement is binlogged as 
      'CREATE TEMPORARY TABLE ...' with a wrong table name when a view exists 
      with the same name. 
      
      Base table is in the same name space with view. when excuting 
      open_and_lock_tables_derived, create_table is initialized as the
      existing view. and create_table->table is now a temporary table for
      the view. This cause the bug to happen.
      
      In this patch, create_table->table will be masqueraded as a base table 
      with the correct table name and db name if it is exists as a view.

    modified:
      mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
      mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
      sql/sql_insert.cc
=== 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	2009-11-05 07:24:26 +0000
@@ -31,3 +31,38 @@ 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#47442-------------
+USE test;
+DROP DATABASE IF EXISTS db1;
+DROP VIEW IF EXISTS t1;
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP TABLE IF EXISTS t3;
+CREATE DATABASE db1;
+CREATE TABLE t2(c1 INTEGER);
+CREATE TABLE t3(c1 INTEGER);
+CREATE TABLE db1.t3(c1 INTEGER);
+INSERT INTO t2 VALUES(33);
+CREATE VIEW t1 AS SELECT c1 FROM t3;
+CREATE VIEW db1.t1 AS SELECT c1 FROM db1.t3;
+DROP VIEW t1;
+DROP VIEW db1.t1;
+SELECT * FROM t1;
+c1
+SELECT * FROM db1.t1;
+c1
+CREATE TABLE IF NOT EXISTS t1(c1 INTEGER) SELECT c1 from t2;
+CREATE TABLE IF NOT EXISTS db1.t1(c1 INTEGER) SELECT c1 from t2;
+SELECT * FROM t1;
+c1
+33
+SELECT * FROM db1.t1;
+c1
+33
+INSERT INTO t1 VALUES(10);
+INSERT INTO db1.t1 VALUES(10);
+DROP VIEW IF EXISTS t1;
+DROP TABLE IF EXISTS t1;
+DROP TABLE t2;
+DROP TABLE t3;
+DROP DATABASE db1;

=== 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	2009-11-05 07:24:26 +0000
@@ -64,7 +64,64 @@ SHOW TABLES in mysqltest;
 replace_column 6 #;
 SHOW EVENTS in mysqltest;
 
-
 connection master;
 DROP DATABASE IF EXISTS mysqltest;
+
+#
+# BUG#47442
+#
+# 'CREATE TABLE IF NOT EXIST ... SELECT' statement is binlogged as a TEMPORARY
+# table if the object exists as a view. At the same time a wrong table name is
+# binlogged, this causes an error of SQL thread.
+#
+
+echo -------------BUG#47442-------------;
+connection master;
+USE test;
+--disable_warnings
+DROP DATABASE IF EXISTS db1;
+DROP VIEW IF EXISTS t1;
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP TABLE IF EXISTS t3;
+--enable_warnings
+
+CREATE DATABASE db1;
+CREATE TABLE t2(c1 INTEGER);
+CREATE TABLE t3(c1 INTEGER);
+CREATE TABLE db1.t3(c1 INTEGER);
+INSERT INTO t2 VALUES(33);
+CREATE VIEW t1 AS SELECT c1 FROM t3;
+CREATE VIEW db1.t1 AS SELECT c1 FROM db1.t3;
+sync_slave_with_master;
+
+# Drop the views, so the tables with the same name can be created on slave.
+DROP VIEW t1;
+DROP VIEW db1.t1;
+
+connection master;
+SELECT * FROM t1;
+SELECT * FROM db1.t1;
+--disable_warnings
+CREATE TABLE IF NOT EXISTS t1(c1 INTEGER) SELECT c1 from t2;
+CREATE TABLE IF NOT EXISTS db1.t1(c1 INTEGER) SELECT c1 from t2;
+--enable_warnings
+# Because t1 exists as a view, so base table t1 can not be created and the
+# data of t2 are inserted into the view t1;
+SELECT * FROM t1;
+SELECT * FROM db1.t1;
+
+sync_slave_with_master;
+INSERT INTO t1 VALUES(10);
+INSERT INTO db1.t1 VALUES(10);
+
+connection master;
+--disable_warnings
+DROP VIEW IF EXISTS t1;
+DROP TABLE IF EXISTS t1;
+DROP TABLE t2;
+DROP TABLE t3;
+DROP DATABASE db1;
+--enable_warnings
+
 source include/master-slave-end.inc;

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	2009-10-03 19:49:08 +0000
+++ b/sql/sql_insert.cc	2009-11-05 07:24:26 +0000
@@ -3760,7 +3760,45 @@ select_create::prepare(List<Item> &value
                           ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
                           create_table->table_name);
       if (thd->current_stmt_binlog_row_based)
+      {
+        TABLE_SHARE *share;
+        LEX_STRING db, table_name;
+        enum tmp_table_type tmp_table;
+
+        share= create_table->table->s;
+        /*
+          BUG#47442 'CREATE TABLE IF NOT EXIST ... SELECT' statement is
+          binlogged as 'CREATE TEMPORARY TABLE ...' with a wrong table name
+          when a view exists with the same name.
+
+          Base table is in the same name space with view. when excuting
+          open_and_lock_tables_derived, create_table is initialized as the
+          existing view. and create_table->table is now a temporary table for
+          the view. This cause the bug to happen.
+
+          create_table->table will be masqueraded as a base table if it is
+          exists as a view.
+         */
+        if (create_table->view && share)
+        {
+          db= share->db;
+          table_name= share->table_name;
+          tmp_table= share->tmp_table;
+
+          share->db= create_table->view_db;
+          share->table_name= create_table->view_name;
+          share->tmp_table= NO_TMP_TABLE;
+        }
+
         binlog_show_create_table(&(create_table->table), 1);
+
+        if (create_table->view && share)
+        {
+          share->db= db;
+          share->table_name= table_name;
+          share->tmp_table= tmp_table;
+        }
+      }
       table= create_table->table;
     }
     else


Attachment: [text/bzr-bundle] bzr/[e-mail]