#At file:///home/anders/Work/bzrwork/wt1/mysql-trunk-wl5576/ based on revid:alexander.nozdrin@stripped
3207 Li-Bing.Song@stripped 2010-12-16
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/r/create.result'
--- a/mysql-test/r/create.result 2010-11-05 16:23:32 +0000
+++ b/mysql-test/r/create.result 2010-12-16 12:33:08 +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 TABLE ... SELECT statement aborted.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created. CREATE TABLE ... SELECT statement aborted.
+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 TABLE ... SELECT statement aborted.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created. CREATE TABLE ... SELECT statement aborted.
+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 TABLE ... SELECT statement aborted.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created. CREATE TABLE ... SELECT statement aborted.
+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 TABLE ... SELECT statement aborted.
+create temporary table t1 select f();
+ERROR HY000: Can't update table 't2' using a stored function while 't1' is being created. CREATE TABLE ... SELECT statement aborted.
+drop view t2;
+drop function f;
=== 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-16 12:33:08 +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_CREATE_TABLE_SELECT
+create table t1 select f();
+--error ER_CANT_UPDATE_USED_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_USED_TABLE_IN_CREATE_TABLE_SELECT
+create table t1 select f();
+--error ER_CANT_UPDATE_USED_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_USED_TABLE_IN_CREATE_TABLE_SELECT
+create table t1 select f();
+--error ER_CANT_UPDATE_USED_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_USED_TABLE_IN_CREATE_TABLE_SELECT
+create table t1 select f();
+--error ER_CANT_UPDATE_USED_TABLE_IN_CREATE_TABLE_SELECT
+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-12-16 12:33:08 +0000
@@ -6446,3 +6446,6 @@ ER_RPL_INFO_DATA_TOO_LONG
eng "Data for column '%s' too long"
ER_CANT_LOCK_RPL_INFO_TABLE
eng "You can't use locks with rpl info tables."
+
+ER_CANT_UPDATE_USED_TABLE_IN_CREATE_TABLE_SELECT
+ eng "Can't update table '%-.192s' using a stored function while '%-.192s' is being created. CREATE TABLE ... SELECT statement aborted."
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2010-11-15 16:43:41 +0000
+++ b/sql/sql_parse.cc 2010-12-16 12:33:08 +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_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;
Attachment: [text/bzr-bundle] bzr/li-bing.song@sun.com-20101216123308-y6ns1l7fdm6atkyo.bundle
