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#5576 | Li-Bing.Song | 21 Dec |
