#At file:///home/anders/Work/bzrwork/wt1/mysql-trunk-wl5576/ based on revid:alexander.nozdrin@stripped11421-owla1k03goqah2z2
3207 Li-Bing.Song@stripped 2010-11-25
WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables
Implementation of the worklog.
@ mysql-test/r/create.result
Add test for WL#5576.
@ mysql-test/r/delete.result
Updated result file, as error message is changed.
@ mysql-test/r/insert.result
Updated result file, as error message is changed.
@ mysql-test/r/lock.result
Updated result file, as error message is changed.
@ mysql-test/r/merge.result
Updated result file, as error message is changed.
@ mysql-test/r/sp-error.result
Updated result file, as error message is changed.
@ mysql-test/r/trigger.result
Updated result file, as error message is changed.
@ mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result
Updated result file, as error message is changed.
@ mysql-test/suite/funcs_1/r/memory_trig_1011ext.result
Updated result file, as error message is changed.
@ mysql-test/suite/funcs_1/r/myisam_trig_1011ext.result
Updated result file, as error message is changed.
@ 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/r/delete.result
mysql-test/r/insert.result
mysql-test/r/lock.result
mysql-test/r/merge.result
mysql-test/r/sp-error.result
mysql-test/r/trigger.result
mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result
mysql-test/suite/funcs_1/r/memory_trig_1011ext.result
mysql-test/suite/funcs_1/r/myisam_trig_1011ext.result
mysql-test/t/create.test
sql/share/errmsg-utf8.txt
sql/sql_parse.cc
=== modified file 'mysql-test/r/create.result'
--- a/mysql-test/r/create.result 2010-11-05 16:23:32 +0000
+++ b/mysql-test/r/create.result 2010-11-25 09:29:16 +0000
@@ -1939,7 +1939,7 @@ BEGIN
INSERT INTO t1 ( `int` ) VALUES (4 ),( 8 ),( 2 ) ;
END ; |
INSERT INTO t1 (pk, int_key) SELECT `pk` , `int_key` FROM B ;
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
UPDATE A SET `pk`=1 WHERE `pk`=0 ;
@@ -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' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
+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' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
+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' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
+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' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
+drop view t2;
+drop function f;
=== modified file 'mysql-test/r/delete.result'
--- a/mysql-test/r/delete.result 2010-10-04 08:25:04 +0000
+++ b/mysql-test/r/delete.result 2010-11-25 09:29:16 +0000
@@ -488,7 +488,7 @@ BEGIN
INSERT INTO t1 SELECT * FROM t1 AS A;
END |
DELETE IGNORE FROM t1;
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
DROP TABLE t1;
#
# Bug #53450: Crash/assertion
=== modified file 'mysql-test/r/insert.result'
--- a/mysql-test/r/insert.result 2010-08-09 12:11:29 +0000
+++ b/mysql-test/r/insert.result 2010-11-25 09:29:16 +0000
@@ -407,19 +407,19 @@ f2()
INSERT INTO t1 VALUES (3);
INSERT DELAYED INTO t1 VALUES (4);
INSERT INTO t1 VALUES (f1());
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
INSERT DELAYED INTO t1 VALUES (f1());
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
INSERT INTO t1 VALUES (f2());
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
INSERT DELAYED INTO t1 VALUES (f2());
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
INSERT INTO t1 VALUES (NEW.i);
INSERT INTO t1 VALUES (1);
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
INSERT DELAYED INTO t1 VALUES (1);
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
SELECT * FROM t1;
i
1
=== modified file 'mysql-test/r/lock.result'
--- a/mysql-test/r/lock.result 2010-02-01 11:43:06 +0000
+++ b/mysql-test/r/lock.result 2010-11-25 09:29:16 +0000
@@ -445,7 +445,7 @@ UPDATE LOW_PRIORITY t1 SET f2 = 7;
# Statement below should fail with ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
# error instead of failing on assertion in table-level locking subsystem.
INSERT INTO t1(f1) VALUES(0);
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
DROP TABLE t1;
#
# Bug#43685 Lock table affects other non-related tables
=== modified file 'mysql-test/r/merge.result'
--- a/mysql-test/r/merge.result 2010-11-05 16:23:32 +0000
+++ b/mysql-test/r/merge.result 2010-11-25 09:29:16 +0000
@@ -2637,14 +2637,14 @@ CREATE TRIGGER tm1_ai AFTER INSERT ON tm
FOR EACH ROW INSERT INTO t1 VALUES(11);
LOCK TABLE tm1 WRITE, t1 WRITE;
INSERT INTO tm1 VALUES (1);
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
SELECT * FROM tm1;
c1
1
UNLOCK TABLES;
LOCK TABLE t1 WRITE, tm1 WRITE;
INSERT INTO tm1 VALUES (1);
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
SELECT * FROM tm1;
c1
1
=== modified file 'mysql-test/r/sp-error.result'
--- a/mysql-test/r/sp-error.result 2010-10-27 10:28:09 +0000
+++ b/mysql-test/r/sp-error.result 2010-11-25 09:29:16 +0000
@@ -310,15 +310,15 @@ insert into t1 (val) values (i);
return 0;
end|
select val, f1(val) from t1|
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
select val, f1(val) from t1 as tab|
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
select * from t1|
val x
42 3.1
19 1.2
update t1 set val= f1(val)|
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
select * from t1|
val x
42 3.1
@@ -1699,7 +1699,7 @@ INSERT INTO t1 SELECT * FROM t1 ;
RETURN 1 ;
END|
INSERT INTO t1 SELECT * FROM (SELECT 2 AS f1, 2 AS f2) AS A WHERE func_1() = 5;
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
DROP FUNCTION func_1;
DROP TABLE t1;
#
=== modified file 'mysql-test/r/trigger.result'
--- a/mysql-test/r/trigger.result 2010-11-10 11:26:45 +0000
+++ b/mysql-test/r/trigger.result 2010-11-25 09:29:16 +0000
@@ -766,7 +766,7 @@ for each row insert into t1 values (new.
set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth;
set @@max_sp_recursion_depth=100;
insert into t1 values (1);
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS;
select * from t1;
f1
@@ -779,7 +779,7 @@ drop trigger t2_ai;
create trigger t1_bu before update on t1
for each row insert into t1 values (2);
update t1 set f1= 10;
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
select * from t1;
f1
1
@@ -787,7 +787,7 @@ drop trigger t1_bu;
create trigger t1_bu before update on t1
for each row delete from t1 where f1=new.f1;
update t1 set f1= 10;
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
select * from t1;
f1
1
@@ -1955,7 +1955,7 @@ DROP TRIGGER t_insert;
CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET
date_insert=NOW(),b=b+1 WHERE t1.a=t2.b AND t2.a=NEW.a; END |
INSERT INTO t2 (a,b) VALUES (3,5);
-ERROR HY000: Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
DROP TABLE t1;
DROP TRIGGER t_insert;
DROP TABLE t2;
=== modified file 'mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result'
--- a/mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result 2009-01-31 19:22:59 +0000
+++ b/mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result 2010-11-25 09:29:16 +0000
@@ -269,7 +269,7 @@ for each row insert into t4 (f4) values
create trigger tr4 after insert on t4
for each row insert into t1 (f1) values (new.f4+1);
insert into t1 values (1);
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
select * from t1 order by f1;
f1
0
@@ -334,7 +334,7 @@ select @counter;
@counter
0
call trig_sp();
-ERROR HY000: Can't update table 't1_sp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1_sp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
select @counter;
@counter
2
=== modified file 'mysql-test/suite/funcs_1/r/memory_trig_1011ext.result'
--- a/mysql-test/suite/funcs_1/r/memory_trig_1011ext.result 2009-01-31 19:22:59 +0000
+++ b/mysql-test/suite/funcs_1/r/memory_trig_1011ext.result 2010-11-25 09:29:16 +0000
@@ -270,7 +270,7 @@ for each row insert into t4 (f4) values
create trigger tr4 after insert on t4
for each row insert into t1 (f1) values (new.f4+1);
insert into t1 values (1);
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
select * from t1 order by f1;
f1
0
@@ -339,7 +339,7 @@ select @counter;
@counter
0
call trig_sp();
-ERROR HY000: Can't update table 't1_sp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1_sp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
select @counter;
@counter
2
=== modified file 'mysql-test/suite/funcs_1/r/myisam_trig_1011ext.result'
--- a/mysql-test/suite/funcs_1/r/myisam_trig_1011ext.result 2009-01-31 19:22:59 +0000
+++ b/mysql-test/suite/funcs_1/r/myisam_trig_1011ext.result 2010-11-25 09:29:16 +0000
@@ -270,7 +270,7 @@ for each row insert into t4 (f4) values
create trigger tr4 after insert on t4
for each row insert into t1 (f1) values (new.f4+1);
insert into t1 values (1);
-ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
select * from t1 order by f1;
f1
0
@@ -339,7 +339,7 @@ select @counter;
@counter
0
call trig_sp();
-ERROR HY000: Can't update table 't1_sp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+ERROR HY000: Can't update table 't1_sp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement.
select @counter;
@counter
2
=== 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-11-25 09:29:16 +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_USED_TABLE_IN_SF_OR_TRG
+create table t1 select f();
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+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_USED_TABLE_IN_SF_OR_TRG
+create table t1 select f();
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+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_USED_TABLE_IN_SF_OR_TRG
+create table t1 select f();
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+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_USED_TABLE_IN_SF_OR_TRG
+create table t1 select f();
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+create temporary table t1 select f();
+
+drop view t2;
+drop function f;
=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt 2010-11-15 16:43:41 +0000
+++ b/sql/share/errmsg-utf8.txt 2010-11-25 09:29:16 +0000
@@ -5556,7 +5556,7 @@ ER_DATETIME_FUNCTION_OVERFLOW 22008
eng "Datetime function: %-.32s field overflow"
ger "Datetime-Funktion: %-.32s Feldüberlauf"
ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
- eng "Can't update table '%-.192s' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."
+ eng "Can't update table '%-.192s' in stored function/trigger because it is already used by statement which invoked this stored function/trigger or it is in a CREATE TABLE statement."
ger "Kann Tabelle '%-.192s' in gespeicherter Funktion oder Trigger nicht aktualisieren, weil sie bereits von der Anweisung verwendet wird, die diese gespeicherte Funktion oder den Trigger aufrief"
ER_VIEW_PREVENT_UPDATE
eng "The definition of table '%-.192s' prevents operation %.192s on table '%-.192s'."
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2010-11-15 16:43:41 +0000
+++ b/sql/sql_parse.cc 2010-11-25 09:29:16 +0000
@@ -2454,7 +2454,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)
{
@@ -2479,6 +2479,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_USED_TABLE_IN_SF_OR_TRG, MYF(0),
+ table->table_name);
+ 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;
Attachment: [text/bzr-bundle] bzr/li-bing.song@sun.com-20101125092916-wrbdjngc0b29lhzv.bundle
