summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/r/merge.result102
-rw-r--r--mysql-test/t/merge-big.test4
-rw-r--r--mysql-test/t/merge.test119
-rw-r--r--sql/lock.cc104
-rw-r--r--sql/lock.h2
-rw-r--r--sql/sql_base.cc85
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);
+ }
}