List:Commits« Previous MessageNext Message »
From:Li-Bing.Song Date:December 21 2010 11:27am
Subject:bzr push into mysql-trunk branch (Li-Bing.Song:3207 to 3208) WL#5576
View as plain text  
 3208 Li-Bing.Song@stripped	2010-12-21
      WL#5576 Postfix
      Fixed the test affected by this worklog

    modified:
      mysql-test/include/unsafe_binlog.inc
      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/func_rollback.test
      mysql-test/t/mdl_sync.test
 3207 Li-Bing.Song@stripped	2010-12-17
      WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables
            
      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.
      
      BUG#39804 and BUG#55876 are fixed by this worklog.
     @ mysql-test/r/create.result
        Add test for WL#5576.
     @ mysql-test/t/create.test
        Add test for WL#5576.
     @ sql/share/errmsg-utf8.txt
        Changed error message of ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
     @ sql/sql_parse.cc
        create_table->table will be null if the creating table exists as a view
        which derives from more than two tables, so create_table->view should be
        checked to decide if the creating table already exists.

    modified:
      mysql-test/r/create.result
      mysql-test/t/create.test
      sql/share/errmsg-utf8.txt
      sql/sql_parse.cc
=== 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-21 10:56:49 +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/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-21 10:56:49 +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-10-21 11:34:17 +0000
+++ b/mysql-test/r/mdl_sync.result	2010-12-21 10:56:49 +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
@@ -2327,6 +2332,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
@@ -2336,9 +2342,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.
@@ -2354,11 +2364,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.
@@ -2374,11 +2389,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
@@ -2395,6 +2415,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-21 10:56:49 +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-11-05 16:23:32 +0000
+++ b/mysql-test/suite/innodb/r/innodb.result	2010-12-21 10:56:49 +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-21 10:56:49 +0000
@@ -2175,6 +2175,7 @@ set autocommit = 0;
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 --send
 insert into t5 (select * from t2 lock in share mode);
+
 connection f;
 SET binlog_format='MIXED';
 set autocommit = 0;
@@ -2239,7 +2240,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/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-21 10:56:49 +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-10-06 14:34:28 +0000
+++ b/mysql-test/t/mdl_sync.test	2010-12-21 10:56:49 +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;
 
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (Li-Bing.Song:3207 to 3208) WL#5576Li-Bing.Song21 Dec