Hi Libing,
Great work, patch approved! Just one suggestion, could you clarify the
comment in the test case? See comment inline.
Also, note that this changes server behavior. Should we have a reviewer
from runtime too?
/Sven
Li-Bing.Song@stripped wrote:
> #At file:///home/anders/work/bzrwork/worktree3/mysql-5.1-bugteam/ based on
> revid:kent.boortz@stripped
>
> 3203 Li-Bing.Song@stripped 2009-11-22
> BUG#47418 RBR fails, failure with mixup of base/temporary/view
>
> 'CREATE TABLE IF NOT EXIST ... SELECT' statement is binlogged as a TEMPORARY
> table if the table exists as a temporary table. This is caused by that the
> temporary table is opened and the results of the 'SELECT' is inserted into
> the temporary table when a temporary exists 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.
>
> modified:
> 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/r/create.result'
> --- a/mysql-test/r/create.result 2009-09-04 06:57:10 +0000
> +++ b/mysql-test/r/create.result 2009-11-22 06:30:09 +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 2009-11-22 06:30:09 +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 2009-11-22 06:30:09
> +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 2009-11-22 06:30:09 +0000
> @@ -67,4 +67,56 @@ SHOW EVENTS in mysqltest;
>
> connection master;
> DROP DATABASE IF EXISTS mysqltest;
> +
> +#
> +# BUG#47418 RBR fails, failure with mixup of base/temporary/view TABLE DDL
> +#
> +# 'CREATE TABLE IF NOT EXIST ... SELECT' statement is binlogged as a TEMPORARY
> +# table if the object exists as a temporary table. This is caused by that the
> +# temporary table is opened and the results of the 'SELECT' is inserted into
> +# the temporary table if a temporary exists with the same name.
The above is a description of the buggy behavior before the bug. Please
make more clear that it does not work like that now. E.g., use past
tense and start with "Before the bug, 'CREATE TABLE IF NOT EXISTS ...
SELECT'", etc.
> +#
> +# 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 2009-11-22 06:30:09 +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 2009-11-22 06:30:09 +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 2009-10-31 10:50:25 +0000
> +++ b/sql/sql_parse.cc 2009-11-22 06:30:09 +0000
> @@ -2657,6 +2657,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-10-19 13:55:04 +0000
> +++ b/sql/sql_prepare.cc 2009-11-22 06:30:09 +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))
>
>
>
> ------------------------------------------------------------------------
>
>
--
Sven Sandberg, Software Engineer
MySQL AB, www.mysql.com
