List:Commits« Previous MessageNext Message »
From:Li-Bing.Song Date:December 30 2010 8:04am
Subject:bzr push into mysql-trunk branch (Li-Bing.Song:3448 to 3449) Bug#39804
Bug#55876 WL#5576
View as plain text  
 3449 Li-Bing.Song@stripped	2010-12-30
      WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables
      BUG#39804 and BUG#55876 are fixed by this worklog.
      
      In 'CREATE TABLE ... SELECT' statement, SELECT clause could call store functions
      to modify other tables. It made CTS's behavior towards complex and not
      understandable. It also caused some binlogging problem. So the behavior modifying
      other tables is prohibited in this worklog.
      
      In this patch, code is added to check if there is any other table owning WRITE
      lock just after all tables have been locked. CREATE TABLE ... SELECT will fail
      and print an error immediately if any other table has WRITE lock.   
     @ mysql-test/include/unsafe_binlog.inc
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/create.result
        Added test to verify WL#5576.
     @ mysql-test/r/func_rollback.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/mdl_sync.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/unsafe_binlog_innodb.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/suite/innodb/r/innodb.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/suite/innodb/t/innodb.test
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/t/create.test
        Added test to verify WL#5576.
     @ mysql-test/t/func_rollback.test
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/t/mdl_sync.test
        'FOR UPDATE' is disallowed in CREATE TABLE ... SELECT statement
        after WL#5576. Using a new method to lock the table.
     @ sql/share/errmsg-utf8.txt
        Added a new error for WL#5576.
     @ sql/sql_parse.cc
        Implemented WL#5576.

    modified:
      mysql-test/include/unsafe_binlog.inc
      mysql-test/r/create.result
      mysql-test/r/func_rollback.result
      mysql-test/r/mdl_sync.result
      mysql-test/r/unsafe_binlog_innodb.result
      mysql-test/suite/innodb/r/innodb.result
      mysql-test/suite/innodb/t/innodb.test
      mysql-test/t/create.test
      mysql-test/t/func_rollback.test
      mysql-test/t/mdl_sync.test
      sql/share/errmsg-utf8.txt
      sql/sql_parse.cc
 3448 Alexander Nozdrin	2010-12-29 [merge]
      Empty merge from mysql-5.5.

=== modified file 'mysql-test/include/unsafe_binlog.inc'
--- a/mysql-test/include/unsafe_binlog.inc	2010-10-25 09:20:53 +0000
+++ b/mysql-test/include/unsafe_binlog.inc	2010-12-30 08:02:23 +0000
@@ -245,7 +245,7 @@ connection i;
 reap;
 
 connection j;
---error ER_LOCK_WAIT_TIMEOUT
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
 reap;
 
 connection a;

=== modified file 'mysql-test/r/create.result'
--- a/mysql-test/r/create.result	2010-12-02 13:11:15 +0000
+++ b/mysql-test/r/create.result	2010-12-30 08:02:23 +0000
@@ -2402,3 +2402,52 @@ a	b
 1	1
 drop table t1;
 #
+#
+# WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables
+#
+create function f()
+returns int
+begin
+insert into t2 values(1);
+return 1;
+end|
+#
+# 1. The function updates a base table
+#
+create table t2(c1 int);
+create table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created.
+drop table t2;
+#
+# 2. The function updates a view which derives from a base table
+#
+create table t3(c1 int);
+create view t2 as select c1 from t3;
+create table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created.
+drop view t2;
+#
+# 3. The function updates a view which derives from two base tables
+#
+create table t4(c1 int);
+create view t2 as select t3.c1 as c1 from t3, t4;
+create table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created.
+drop view t2;
+drop tables t3, t4;
+#
+# 4. The function updates a view which selects a constant number
+#
+create view t2 as select 1;
+create table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created.
+drop view t2;
+drop function f;

=== modified file 'mysql-test/r/func_rollback.result'
--- a/mysql-test/r/func_rollback.result	2008-04-09 13:56:25 +0000
+++ b/mysql-test/r/func_rollback.result	2010-12-30 08:02:23 +0000
@@ -391,11 +391,11 @@ DROP VIEW v1_func;
 
 # FUNCTION in CREATE TABLE ... AS SELECT
 CREATE TABLE t1_fail AS SELECT f1_two_inserts() FROM t1_select;
-ERROR 23000: Column 'f2' cannot be null
+ERROR HY000: Can't update table 't1_not_null' using a stored function while 't1_fail' is being created.
 SELECT * FROM t1_not_null ORDER BY f1,f2;
 f1	f2
 CREATE TABLE t1_fail AS SELECT * FROM t1_select WHERE 1 = f1_two_inserts();
-ERROR 23000: Column 'f2' cannot be null
+ERROR HY000: Can't update table 't1_not_null' using a stored function while 't1_fail' is being created.
 SELECT * FROM t1_not_null ORDER BY f1,f2;
 f1	f2
 

=== modified file 'mysql-test/r/mdl_sync.result'
--- a/mysql-test/r/mdl_sync.result	2010-11-30 17:55:36 +0000
+++ b/mysql-test/r/mdl_sync.result	2010-12-30 08:02:23 +0000
@@ -2293,13 +2293,18 @@ set global log_output=@save_log_output;
 #
 drop tables if exists t1, t2;
 create table t1 (i int);
+insert into t1 values(1);
 # Let us check that we won't deadlock if during filling
 # of I_S table we encounter conflicting metadata lock
 # which owner is in its turn waiting for our connection.
 lock tables t1 read;
+# Switching to connection 'con46044_2'.
+# Sending:
+update t1 set i = 2;
 # Switching to connection 'con46044'.
+# Waiting until UPDATE t1 SET ... is blocked.
 # Sending:
-create table t2 select * from t1 for update;;
+create table t2 select * from t1;;
 # Switching to connection 'default'.
 # Waiting until CREATE TABLE ... SELECT ... is blocked.
 # First let us check that SHOW FIELDS/DESCRIBE doesn't
@@ -2329,6 +2334,7 @@ unlock tables;
 # Switching to connection 'con46044'.
 # Reaping CREATE TABLE ... SELECT ... .
 drop table t2;
+# Reaping UPDATE t1 statement
 #
 # Let us also check that queries to I_S wait for conflicting metadata
 # locks to go away instead of skipping table with a warning in cases
@@ -2338,9 +2344,13 @@ drop table t2;
 # We check same three queries to I_S in this new situation.
 # Switching to connection 'con46044_2'.
 lock tables t1 read;
+# Switching to connection 'con46044_3'.
+# Sending:
+update t1 set i = 3;
 # Switching to connection 'con46044'.
+# Waiting until UPDATE t1 SET ... is blocked.
 # Sending:
-create table t2 select * from t1 for update;;
+create table t2 select * from t1;;
 # Switching to connection 'default'.
 # Waiting until CREATE TABLE ... SELECT ... is blocked.
 # Let us check that SHOW FIELDS/DESCRIBE gets blocked.
@@ -2356,11 +2366,16 @@ unlock tables;
 Field	Type	Null	Key	Default	Extra
 i	int(11)	YES		NULL	
 drop table t2;
+# Reaping UPDATE t1 statement
 # Switching to connection 'con46044_2'.
 lock tables t1 read;
+# Switching to connection 'con46044_3'.
+# Sending:
+update t1 set i = 4;
 # Switching to connection 'con46044'.
+# Waiting until UPDATE t1 SET ... is blocked.
 # Sending:
-create table t2 select * from t1 for update;;
+create table t2 select * from t1;;
 # Switching to connection 'default'.
 # Waiting until CREATE TABLE ... SELECT ... is blocked.
 # Check that I_S query which reads only .FRMs gets blocked.
@@ -2376,11 +2391,16 @@ unlock tables;
 column_name
 i
 drop table t2;
+# Reaping UPDATE t1 statement
 # Switching to connection 'con46044_2'.
 lock tables t1 read;
+# Switching to connection 'con46044_3'.
+# Sending:
+update t1 set i = 5;
 # Switching to connection 'con46044'.
+# Waiting until UPDATE t1 SET ... is blocked.
 # Sending:
-create table t2 select * from t1 for update;;
+create table t2 select * from t1;;
 # Switching to connection 'default'.
 # Waiting until CREATE TABLE ... SELECT ... is blocked.
 # Finally, check that I_S query which does full-blown table open
@@ -2397,6 +2417,7 @@ unlock tables;
 table_name	table_type	auto_increment	table_comment
 t2	BASE TABLE	NULL	
 drop table t2;
+# Reaping UPDATE t1 statement
 # Switching to connection 'default'.
 # Clean-up.
 drop table t1;

=== modified file 'mysql-test/r/unsafe_binlog_innodb.result'
--- a/mysql-test/r/unsafe_binlog_innodb.result	2010-10-25 09:20:53 +0000
+++ b/mysql-test/r/unsafe_binlog_innodb.result	2010-12-30 08:02:23 +0000
@@ -118,6 +118,6 @@ ERROR HY000: Lock wait timeout exceeded;
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
-ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+ERROR HY000: Can't update table 't2' using a stored function while 't10' is being created.
 commit;
 drop table t1, t2, t3, t5, t6, t8, t9;

=== modified file 'mysql-test/suite/innodb/r/innodb.result'
--- a/mysql-test/suite/innodb/r/innodb.result	2010-12-06 13:12:51 +0000
+++ b/mysql-test/suite/innodb/r/innodb.result	2010-12-30 08:02:23 +0000
@@ -2949,7 +2949,7 @@ ERROR HY000: Lock wait timeout exceeded;
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
-ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+ERROR HY000: Can't update table 't2' using a stored function while 't10' is being created.
 commit;
 drop table t1, t2, t3, t5, t6, t8, t9;
 CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;

=== modified file 'mysql-test/suite/innodb/t/innodb.test'
--- a/mysql-test/suite/innodb/t/innodb.test	2010-10-26 09:10:59 +0000
+++ b/mysql-test/suite/innodb/t/innodb.test	2010-12-30 08:02:23 +0000
@@ -2239,7 +2239,7 @@ connection i;
 reap;
 
 connection j;
---error 1205
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
 reap;
 
 connection a;

=== modified file 'mysql-test/t/create.test'
--- a/mysql-test/t/create.test	2010-10-25 09:20:53 +0000
+++ b/mysql-test/t/create.test	2010-12-30 08:02:23 +0000
@@ -2017,3 +2017,69 @@ create table if not exists t1 (a int uni
 select * from t1;
 drop table t1;
 --echo #
+
+--echo #
+--echo # WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables
+--echo #
+
+delimiter |;
+create function f()
+returns int
+begin
+insert into t2 values(1);
+return 1;
+end|
+delimiter ;|
+
+--echo #
+--echo # 1. The function updates a base table
+--echo #
+create table t2(c1 int);
+
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
+create table t1 select f();
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
+create temporary table t1 select f();
+
+
+drop table t2;
+
+--echo #
+--echo # 2. The function updates a view which derives from a base table
+--echo #
+create table t3(c1 int);
+create view t2 as select c1 from t3;
+
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
+create table t1 select f();
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
+create temporary table t1 select f();
+
+drop view t2;
+
+--echo #
+--echo # 3. The function updates a view which derives from two base tables
+--echo #
+create table t4(c1 int);
+create view t2 as select t3.c1 as c1 from t3, t4;
+
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
+create table t1 select f();
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
+create temporary table t1 select f();
+
+drop view t2;
+drop tables t3, t4;
+
+--echo #
+--echo # 4. The function updates a view which selects a constant number
+--echo #
+create view t2 as select 1;
+
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
+create table t1 select f();
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
+create temporary table t1 select f();
+
+drop view t2;
+drop function f;

=== modified file 'mysql-test/t/func_rollback.test'
--- a/mysql-test/t/func_rollback.test	2008-04-09 13:56:25 +0000
+++ b/mysql-test/t/func_rollback.test	2010-12-30 08:02:23 +0000
@@ -409,11 +409,11 @@ DROP VIEW v1_func;
 
 --echo
 --echo # FUNCTION in CREATE TABLE ... AS SELECT
---error ER_BAD_NULL_ERROR
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
 CREATE TABLE t1_fail AS SELECT f1_two_inserts() FROM t1_select;
 SELECT * FROM t1_not_null ORDER BY f1,f2;
 #
---error ER_BAD_NULL_ERROR
+--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
 CREATE TABLE t1_fail AS SELECT * FROM t1_select WHERE 1 = f1_two_inserts();
 SELECT * FROM t1_not_null ORDER BY f1,f2;
 #

=== modified file 'mysql-test/t/mdl_sync.test'
--- a/mysql-test/t/mdl_sync.test	2010-11-11 17:11:05 +0000
+++ b/mysql-test/t/mdl_sync.test	2010-12-30 08:02:23 +0000
@@ -3345,18 +3345,33 @@ drop tables if exists t1, t2;
 --enable_warnings
 connect (con46044, localhost, root,,);
 connect (con46044_2, localhost, root,,);
+connect (con46044_3, localhost, root,,);
 connection default;
 create table t1 (i int);
+insert into t1 values(1);
 
 --echo # Let us check that we won't deadlock if during filling
 --echo # of I_S table we encounter conflicting metadata lock
 --echo # which owner is in its turn waiting for our connection.
 lock tables t1 read;
 
+--echo # Switching to connection 'con46044_2'.
+connection con46044_2;
+--echo # Sending:
+--send update t1 set i = 2
+
 --echo # Switching to connection 'con46044'.
 connection con46044;
+
+--echo # Waiting until UPDATE t1 SET ... is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table level lock" and
+        info = "update t1 set i = 2";
+--source include/wait_condition.inc
+
 --echo # Sending:
---send create table t2 select * from t1 for update;
+--send create table t2 select * from t1;
 
 --echo # Switching to connection 'default'.
 connection default;
@@ -3364,7 +3379,7 @@ connection default;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
   where state = "Waiting for table level lock" and
-        info = "create table t2 select * from t1 for update";
+        info = "create table t2 select * from t1";
 --source include/wait_condition.inc
 
 --echo # First let us check that SHOW FIELDS/DESCRIBE doesn't
@@ -3395,6 +3410,10 @@ connection con46044;
 --reap
 drop table t2;
 
+connection con46044_2;
+--echo # Reaping UPDATE t1 statement
+--reap
+
 --echo #
 --echo # Let us also check that queries to I_S wait for conflicting metadata
 --echo # locks to go away instead of skipping table with a warning in cases
@@ -3407,10 +3426,23 @@ drop table t2;
 connection con46044_2;
 lock tables t1 read;
 
+--echo # Switching to connection 'con46044_3'.
+connection con46044_3;
+--echo # Sending:
+send update t1 set i = 3;
+
 --echo # Switching to connection 'con46044'.
 connection con46044;
+
+--echo # Waiting until UPDATE t1 SET ... is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table level lock" and
+        info = "update t1 set i = 3";
+--source include/wait_condition.inc
+
 --echo # Sending:
---send create table t2 select * from t1 for update;
+--send create table t2 select * from t1;
 
 --echo # Switching to connection 'default'.
 connection default;
@@ -3418,7 +3450,7 @@ connection default;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
   where state = "Waiting for table level lock" and
-        info = "create table t2 select * from t1 for update";
+        info = "create table t2 select * from t1";
 --source include/wait_condition.inc
 
 --echo # Let us check that SHOW FIELDS/DESCRIBE gets blocked.
@@ -3447,14 +3479,31 @@ connection default;
 --reap
 drop table t2;
 
+connection con46044_3;
+--echo # Reaping UPDATE t1 statement
+--reap
+
 --echo # Switching to connection 'con46044_2'.
 connection con46044_2;
 lock tables t1 read;
 
+--echo # Switching to connection 'con46044_3'.
+connection con46044_3;
+--echo # Sending:
+--send update t1 set i = 4
+
 --echo # Switching to connection 'con46044'.
 connection con46044;
+
+--echo # Waiting until UPDATE t1 SET ... is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table level lock" and
+        info = "update t1 set i = 4";
+--source include/wait_condition.inc
+
 --echo # Sending:
---send create table t2 select * from t1 for update;
+--send create table t2 select * from t1;
 
 --echo # Switching to connection 'default'.
 connection default;
@@ -3462,7 +3511,7 @@ connection default;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
   where state = "Waiting for table level lock" and
-        info = "create table t2 select * from t1 for update";
+        info = "create table t2 select * from t1";
 --source include/wait_condition.inc
 
 --echo # Check that I_S query which reads only .FRMs gets blocked.
@@ -3491,14 +3540,31 @@ connection default;
 --reap
 drop table t2;
 
+connection con46044_3;
+--echo # Reaping UPDATE t1 statement
+--reap
+
 --echo # Switching to connection 'con46044_2'.
 connection con46044_2;
 lock tables t1 read;
 
+--echo # Switching to connection 'con46044_3'.
+connection con46044_3;
+--echo # Sending:
+--send update t1 set i = 5
+
 --echo # Switching to connection 'con46044'.
 connection con46044;
+
+--echo # Waiting until UPDATE t1 SET ... is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table level lock" and
+        info = "update t1 set i = 5";
+--source include/wait_condition.inc
+
 --echo # Sending:
---send create table t2 select * from t1 for update;
+--send create table t2 select * from t1;
 
 --echo # Switching to connection 'default'.
 connection default;
@@ -3506,7 +3572,7 @@ connection default;
 let $wait_condition=
   select count(*) = 1 from information_schema.processlist
   where state = "Waiting for table level lock" and
-        info = "create table t2 select * from t1 for update";
+        info = "create table t2 select * from t1";
 --source include/wait_condition.inc
 
 --echo # Finally, check that I_S query which does full-blown table open
@@ -3536,11 +3602,16 @@ connection default;
 --reap
 drop table t2;
 
+connection con46044_3;
+--echo # Reaping UPDATE t1 statement
+--reap
+
 --echo # Switching to connection 'default'.
 connection default;
 --echo # Clean-up.
 disconnect con46044;
 disconnect con46044_2;
+disconnect con46044_3;
 drop table t1;
 
 

=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt	2010-12-05 22:51:49 +0000
+++ b/sql/share/errmsg-utf8.txt	2010-12-30 08:02:23 +0000
@@ -6454,3 +6454,6 @@ ER_STMT_CACHE_FULL
         eng "Multi-row statements required more than 'max_binlog_stmt_cache_size' bytes of storage; increase this mysqld variable and try again"
 ER_BINLOG_STMT_CACHE_SIZE_GREATER_THAN_MAX
   eng "Option binlog_stmt_cache_size (%lu) is greater than max_binlog_stmt_cache_size (%lu); setting binlog_stmt_cache_size equal to max_binlog_stmt_cache_size."
+
+ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
+  eng "Can't update table '%-.192s' using a stored function while '%-.192s' is being created."

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2010-12-29 00:38:59 +0000
+++ b/sql/sql_parse.cc	2010-12-30 08:02:23 +0000
@@ -2428,7 +2428,7 @@ case SQLCOM_PREPARE:
       if (!(res= open_and_lock_tables(thd, lex->query_tables, TRUE, 0)))
       {
         /* The table already exists */
-        if (create_table->table)
+        if (create_table->table || create_table->view)
         {
           if (create_info.options & HA_LEX_CREATE_IF_NOT_EXISTS)
           {
@@ -2453,6 +2453,17 @@ case SQLCOM_PREPARE:
         */
         lex->unlink_first_table(&link_to_local);
 
+        /* Updating any other table is prohibited in CTS statement */
+        for (TABLE_LIST *table= lex->query_tables; table;
+             table= table->next_global)
+          if (table->lock_type >= TL_WRITE_ALLOW_WRITE)
+          {
+            res= 1;
+            my_error(ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT, MYF(0),
+                     table->table_name, create_info.alias);
+            goto end_with_restore_list;
+          }
+
         /* So that CREATE TEMPORARY TABLE gets to binlog at commit/rollback */
         if (create_info.options & HA_LEX_CREATE_TMP_TABLE)
           thd->variables.option_bits|= OPTION_KEEP_LOG;

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (Li-Bing.Song:3448 to 3449) Bug#39804Bug#55876 WL#5576Li-Bing.Song30 Dec