diff options
-rw-r--r-- | mysql-test/r/merge.result | 102 | ||||
-rw-r--r-- | mysql-test/t/merge-big.test | 4 | ||||
-rw-r--r-- | mysql-test/t/merge.test | 119 | ||||
-rw-r--r-- | sql/lock.cc | 104 | ||||
-rw-r--r-- | sql/lock.h | 2 | ||||
-rw-r--r-- | sql/sql_base.cc | 85 |
6 files changed, 288 insertions, 128 deletions
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 8f7ebb06c06..63b957e7a3f 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -2718,4 +2718,106 @@ m2 CREATE TABLE `m2` ( `i` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`) drop tables m1, m2, t1; +# +# Test case for Bug#54811 "Assert in mysql_lock_have_duplicate()" +# Check that unique_table() works correctly for merge tables. +# +drop table if exists t1, t2, t3, m1, m2; +create table t1 (a int); +create table t2 (a int); +create table t3 (b int); +create view v1 as select * from t3,t1; +create table m1 (a int) engine=merge union (t1, t2) insert_method=last; +create table m2 (a int) engine=merge union (t1, t2) insert_method=first; +create temporary table tmp (b int); +insert into tmp (b) values (1); +insert into t1 (a) values (1); +insert into t3 (b) values (1); +insert into m1 (a) values ((select max(a) from m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t3, m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t3, m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t3, t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t3, t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from tmp, m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from tmp, m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from tmp, t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from tmp, t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from v1)); +ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'. +insert into m1 (a) values ((select max(a) from tmp, v1)); +ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'. +update m1 set a = ((select max(a) from m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t3, m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t3, m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t3, t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t3, t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from tmp, m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from tmp, m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from tmp, t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from tmp, t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from v1)); +ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'. +update m1 set a = ((select max(a) from tmp, v1)); +ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'. +delete from m1 where a = (select max(a) from m1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from m2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t3, m1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t3, m2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t3, t1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t3, t2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from tmp, m1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from tmp, m2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from tmp, t1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from tmp, t2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from v1); +ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1'. +delete from m1 where a = (select max(a) from tmp, v1); +ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1'. +drop view v1; +drop temporary table tmp; +drop table t1, t2, t3, m1, m2; End of 6.0 tests diff --git a/mysql-test/t/merge-big.test b/mysql-test/t/merge-big.test index 33bd93791f1..509c7742dac 100644 --- a/mysql-test/t/merge-big.test +++ b/mysql-test/t/merge-big.test @@ -51,7 +51,7 @@ connection default; #--sleep 8 #SELECT ID,STATE,INFO FROM INFORMATION_SCHEMA.PROCESSLIST; let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST - WHERE ID = $con1_id AND STATE = 'Table lock'; + WHERE ID = $con1_id AND STATE = 'Waiting for table'; --source include/wait_condition.inc #SELECT NOW(); --echo # Kick INSERT out of thr_multi_lock(). @@ -61,7 +61,7 @@ FLUSH TABLES; #--sleep 8 #SELECT ID,STATE,INFO FROM INFORMATION_SCHEMA.PROCESSLIST; let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST - WHERE ID = $con1_id AND STATE = 'Table lock'; + WHERE ID = $con1_id AND STATE = 'Waiting for table'; --source include/wait_condition.inc #SELECT NOW(); --echo # Unlock and close table and wait for con1 to close too. diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 29c0eae1df6..d7026011055 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -2201,6 +2201,125 @@ show create table m1; show create table m2; drop tables m1, m2, t1; +--echo # +--echo # Test case for Bug#54811 "Assert in mysql_lock_have_duplicate()" +--echo # Check that unique_table() works correctly for merge tables. +--echo # +--disable_warnings +drop table if exists t1, t2, t3, m1, m2; +--enable_warnings +create table t1 (a int); +create table t2 (a int); +create table t3 (b int); +create view v1 as select * from t3,t1; +create table m1 (a int) engine=merge union (t1, t2) insert_method=last; +create table m2 (a int) engine=merge union (t1, t2) insert_method=first; +create temporary table tmp (b int); +insert into tmp (b) values (1); + +insert into t1 (a) values (1); +insert into t3 (b) values (1); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from m1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from m2)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t2)); + +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, m1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, m2)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, t1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, t2)); + +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, m1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, m2)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, t1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, t2)); + +--error ER_VIEW_PREVENT_UPDATE +insert into m1 (a) values ((select max(a) from v1)); +--error ER_VIEW_PREVENT_UPDATE +insert into m1 (a) values ((select max(a) from tmp, v1)); + + +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from m1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from m2)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t2)); + +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t3, m1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t3, m2)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t3, t1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t3, t2)); + +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from tmp, m1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from tmp, m2)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from tmp, t1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from tmp, t2)); + +--error ER_VIEW_PREVENT_UPDATE +update m1 set a = ((select max(a) from v1)); +--error ER_VIEW_PREVENT_UPDATE +update m1 set a = ((select max(a) from tmp, v1)); + + +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from m1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from m2); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t2); + +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t3, m1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t3, m2); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t3, t1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t3, t2); + +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from tmp, m1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from tmp, m2); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from tmp, t1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from tmp, t2); + +--error ER_VIEW_PREVENT_UPDATE +delete from m1 where a = (select max(a) from v1); +--error ER_VIEW_PREVENT_UPDATE +delete from m1 where a = (select max(a) from tmp, v1); + +drop view v1; +drop temporary table tmp; +drop table t1, t2, t3, m1, m2; --echo End of 6.0 tests diff --git a/sql/lock.cc b/sql/lock.cc index 52d97a2422b..de0f39018f7 100644 --- a/sql/lock.cc +++ b/sql/lock.cc @@ -627,110 +627,6 @@ MYSQL_LOCK *mysql_lock_merge(MYSQL_LOCK *a,MYSQL_LOCK *b) } -/** - Find duplicate lock in tables. - - Temporary tables are ignored here like they are ignored in - get_lock_data(). If we allow two opens on temporary tables later, - both functions should be checked. - - @param thd The current thread. - @param needle The table to check for duplicate lock. - @param haystack The list of tables to search for the dup lock. - - @note - This is mainly meant for MERGE tables in INSERT ... SELECT - situations. The 'real', underlying tables can be found only after - the MERGE tables are opened. This function assumes that the tables are - already locked. - - @retval - NULL No duplicate lock found. - @retval - !NULL First table from 'haystack' that matches a lock on 'needle'. -*/ - -TABLE_LIST *mysql_lock_have_duplicate(THD *thd, TABLE_LIST *needle, - TABLE_LIST *haystack) -{ - MYSQL_LOCK *mylock; - TABLE **lock_tables; - TABLE *table; - TABLE *table2; - THR_LOCK_DATA **lock_locks; - THR_LOCK_DATA **table_lock_data; - THR_LOCK_DATA **end_data; - THR_LOCK_DATA **lock_data2; - THR_LOCK_DATA **end_data2; - DBUG_ENTER("mysql_lock_have_duplicate"); - - /* - Table may not be defined for derived or view tables. - Table may not be part of a lock for delayed operations. - */ - if (! (table= needle->table) || ! table->lock_count) - goto end; - - /* A temporary table does not have locks. */ - if (table->s->tmp_table == NON_TRANSACTIONAL_TMP_TABLE) - goto end; - - /* Get command lock or LOCK TABLES lock. Maybe empty for INSERT DELAYED. */ - if (! (mylock= thd->lock)) - goto end; - - /* If we have less than two tables, we cannot have duplicates. */ - if (mylock->table_count < 2) - goto end; - - lock_locks= mylock->locks; - lock_tables= mylock->table; - - /* Prepare table related variables that don't change in loop. */ - DBUG_ASSERT((table->lock_position < mylock->table_count) && - (table == lock_tables[table->lock_position])); - table_lock_data= lock_locks + table->lock_data_start; - end_data= table_lock_data + table->lock_count; - - for (; haystack; haystack= haystack->next_global) - { - if (haystack->placeholder()) - continue; - table2= haystack->table; - if (table2->s->tmp_table == NON_TRANSACTIONAL_TMP_TABLE) - continue; - - /* All tables in list must be in lock. */ - DBUG_ASSERT((table2->lock_position < mylock->table_count) && - (table2 == lock_tables[table2->lock_position])); - - for (lock_data2= lock_locks + table2->lock_data_start, - end_data2= lock_data2 + table2->lock_count; - lock_data2 < end_data2; - lock_data2++) - { - THR_LOCK_DATA **lock_data; - THR_LOCK *lock2= (*lock_data2)->lock; - - for (lock_data= table_lock_data; - lock_data < end_data; - lock_data++) - { - if ((*lock_data)->lock == lock2) - { - DBUG_PRINT("info", ("haystack match: '%s'", haystack->table_name)); - DBUG_RETURN(haystack); - } - } - } - } - - end: - DBUG_PRINT("info", ("no duplicate found")); - DBUG_RETURN(NULL); -} - - /** Unlock a set of external. */ static int unlock_external(THD *thd, TABLE **table,uint count) diff --git a/sql/lock.h b/sql/lock.h index 84c7bce0679..4bdf0085d07 100644 --- a/sql/lock.h +++ b/sql/lock.h @@ -60,8 +60,6 @@ void mysql_lock_remove(THD *thd, MYSQL_LOCK *locked,TABLE *table); void mysql_lock_abort(THD *thd, TABLE *table, bool upgrade_lock); bool mysql_lock_abort_for_thread(THD *thd, TABLE *table); MYSQL_LOCK *mysql_lock_merge(MYSQL_LOCK *a,MYSQL_LOCK *b); -TABLE_LIST *mysql_lock_have_duplicate(THD *thd, TABLE_LIST *needle, - TABLE_LIST *haystack); void broadcast_refresh(void); /* Lock based on name */ bool lock_table_names(THD *thd, TABLE_LIST *table_list); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 0d4d949d701..3758ec7253d 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1931,15 +1931,14 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table, } -/* + +/** Test that table is unique (It's only exists once in the table list) - SYNOPSIS - unique_table() - thd thread handle - table table which should be checked - table_list list of tables - check_alias whether to check tables' aliases + @param thd thread handle + @param table table which should be checked + @param table_list list of tables + @param check_alias whether to check tables' aliases NOTE: to exclude derived tables from check we use following mechanism: a) during derived table processing set THD::derived_tables_processing @@ -1950,7 +1949,7 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table, processing loop, because multi-update call fix_fields() for some its items (which mean JOIN::prepare for subqueries) before unique_table call to detect which tables should be locked for write). - c) unique_table skip all tables which belong to SELECT with + c) find_dup_table skip all tables which belong to SELECT with SELECT::exclude_from_table_unique_test set. Also SELECT::exclude_from_table_unique_test used to exclude from check tables of main SELECT of multi-delete and multi-update @@ -1962,17 +1961,17 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table, TODO: when we will have table/view change detection we can do this check only once for PS/SP - RETURN - found duplicate - 0 if table is unique + @retval !=0 found duplicate + @retval 0 if table is unique */ -TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, - bool check_alias) +static +TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, + bool check_alias) { TABLE_LIST *res; const char *d_name, *t_name, *t_alias; - DBUG_ENTER("unique_table"); + DBUG_ENTER("find_dup_table"); DBUG_PRINT("enter", ("table alias: %s", table->alias)); /* @@ -1987,6 +1986,9 @@ TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, */ if (table->table) { + /* All MyISAMMRG children are plain MyISAM tables. */ + DBUG_ASSERT(table->table->file->ht->db_type != DB_TYPE_MRG_MYISAM); + /* temporary table is always unique */ if (table->table && table->table->s->tmp_table != NO_TMP_TABLE) DBUG_RETURN(0); @@ -2008,8 +2010,7 @@ TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, Table is unique if it is present only once in the global list of tables and once in the list of table locks. */ - if (! (res= find_table_in_global_list(table_list, d_name, t_name)) && - ! (res= mysql_lock_have_duplicate(thd, table, table_list))) + if (! (res= find_table_in_global_list(table_list, d_name, t_name))) break; /* Skip if same underlying table. */ @@ -2048,6 +2049,37 @@ next: } +/** + Test that the subject table of INSERT/UPDATE/DELETE/CREATE + or (in case of MyISAMMRG) one of its children are not used later + in the query. + + @retval non-NULL The table list element for the table that + represents the duplicate. + @retval NULL No duplicates found. +*/ + +TABLE_LIST* +unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, + bool check_alias) +{ + TABLE_LIST *dup; + if (table->table && table->table->file->ht->db_type == DB_TYPE_MRG_MYISAM) + { + TABLE_LIST *child; + dup= NULL; + /* Check duplicates of all merge children. */ + for (child= table->next_global; child && child->parent_l == table; + child= child->next_global) + { + if ((dup= find_dup_table(thd, child, child->next_global, check_alias))) + break; + } + } + else + dup= find_dup_table(thd, table, table_list, check_alias); + return dup; +} /* Issue correct error message in case we found 2 duplicate tables which prevent some update operation @@ -3204,8 +3236,7 @@ bool open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, table_list->table= table; DBUG_ASSERT(table->key_read == 0); /* Tables may be reused in a sub statement. */ - if (table->file->extra(HA_EXTRA_IS_ATTACHED_CHILDREN)) - table->file->extra(HA_EXTRA_DETACH_CHILDREN); + DBUG_ASSERT(! table->file->extra(HA_EXTRA_IS_ATTACHED_CHILDREN)); DBUG_RETURN(FALSE); err_lock: @@ -5563,11 +5594,25 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags) or schema tables) as free for reuse. */ -static void mark_real_tables_as_free_for_reuse(TABLE_LIST *table) +static void mark_real_tables_as_free_for_reuse(TABLE_LIST *table_list) { - for (; table; table= table->next_global) + TABLE_LIST *table; + for (table= table_list; table; table= table->next_global) if (!table->placeholder()) + { table->table->query_id= 0; + } + for (table= table_list; table; table= table->next_global) + if (!table->placeholder()) + { + /* + Detach children of MyISAMMRG tables used in + sub-statements, they will be reattached at open. + This has to be done in a separate loop to make sure + that children have had their query_id cleared. + */ + table->table->file->extra(HA_EXTRA_DETACH_CHILDREN); + } } |