List:Commits« Previous MessageNext Message »
From:Li-Bing.Song Date:December 16 2010 12:33pm
Subject:bzr commit into mysql-trunk branch (Li-Bing.Song:3207) WL#5576
View as plain text  
#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
Thread
bzr commit into mysql-trunk branch (Li-Bing.Song:3207) WL#5576Li-Bing.Song16 Dec
  • Re: bzr commit into mysql-trunk branch (Li-Bing.Song:3207) WL#5576Davi Arnaut16 Dec
    • Re: bzr commit into mysql-trunk branch (Li-Bing.Song:3207) WL#5576Libing Song17 Dec