From 50ed1ef7520bbff8a88ea33f7838544b1c3ce4e2 Mon Sep 17 00:00:00 2001 From: Staale Smedseng Date: Fri, 27 Mar 2009 12:09:15 +0100 Subject: Bug#39953 Triggers are not working properly with multi table updates Attempt to execute trigger or stored function with multi-UPDATE which used - but didn't update - a table that was also used by the calling statement led to an error. Read-only reference to tables used in the calling statement should be allowed. This problem was caused by the fact that check for conflicting use of tables in SP/triggers was performed in open_tables(), and in case of multi-UPDATE we didn't know exact lock type at this stage. We solve the problem by moving this check to lock_tables(), so it can be performed after exact lock types for tables used by multi-UPDATE are determined. mysql-test/r/trigger.result: Results for the added test case is added. mysql-test/t/trigger.test: A new test case is added, verifying correct table multi-update conflict resolution, both read-only and write. sql/sql_base.cc: The check for conflicting use of tables in SP/triggers is moved to lock_tables(), to be performed after the exact lock types have been determined. Also, an assert is added to open_ltable() to ensure this func is not used in a prelocked context. --- sql/sql_base.cc | 64 ++++++++++++++++++++++++++++++++++----------------------- 1 file changed, 38 insertions(+), 26 deletions(-) (limited to 'sql/sql_base.cc') diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 0cf3e023be9..b43085436e5 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1596,27 +1596,11 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, { // Using table locks TABLE *best_table= 0; int best_distance= INT_MIN; - bool check_if_used= thd->prelocked_mode && - ((int) table_list->lock_type >= - (int) TL_WRITE_ALLOW_WRITE); for (table=thd->open_tables; table ; table=table->next) { if (table->s->key_length == key_length && !memcmp(table->s->table_cache_key, key, key_length)) { - if (check_if_used && table->query_id && - table->query_id != thd->query_id) - { - /* - If we are in stored function or trigger we should ensure that - we won't change table that is already used by calling statement. - So if we are opening table for writing, we should check that it - is not already open by some calling stamement. - */ - my_error(ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG, MYF(0), - table->s->table_name); - DBUG_RETURN(0); - } if (!my_strcasecmp(system_charset_info, table->alias, alias) && table->query_id != thd->query_id && /* skip tables already used */ !(thd->prelocked_mode && table->query_id)) @@ -1640,13 +1624,13 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, { best_distance= distance; best_table= table; - if (best_distance == 0 && !check_if_used) + if (best_distance == 0) { /* - If we have found perfect match and we don't need to check that - table is not used by one of calling statements (assuming that - we are inside of function or trigger) we can finish iterating - through open tables list. + We have found a perfect match and can finish iterating + through open tables list. Check for table use conflict + between calling statement and SP/trigger is done in + lock_tables(). */ break; } @@ -2944,9 +2928,9 @@ static bool check_lock_and_start_stmt(THD *thd, TABLE *table, lock_type Lock to use for open NOTE - This function don't do anything like SP/SF/views/triggers analysis done - in open_tables(). It is intended for opening of only one concrete table. - And used only in special contexts. + This function doesn't do anything like SP/SF/views/triggers analysis done + in open_tables()/lock_tables(). It is intended for opening of only one + concrete table. And used only in special contexts. RETURN VALUES table Opened table @@ -3262,8 +3246,36 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count, bool *need_reopen) TABLE_LIST *first_not_own= thd->lex->first_not_own_table(); for (table= tables; table != first_not_own; table= table->next_global) { - if (!table->placeholder() && - check_lock_and_start_stmt(thd, table->table, table->lock_type)) + if (table->placeholder()) + continue; + + /* + In a stored function or trigger we should ensure that we won't change + a table that is already used by the calling statement. + */ + if (thd->prelocked_mode && + table->lock_type >= TL_WRITE_ALLOW_WRITE) + { + for (TABLE* opentab= thd->open_tables; opentab; opentab= opentab->next) + { + /* + issue an error if the tables are the same (by key comparison), + but query_id isn't + */ + if (opentab->query_id && + table->table->query_id != opentab->query_id && + table->table->s->key_length == opentab->s->key_length && + !memcmp(table->table->s->table_cache_key, + opentab->s->table_cache_key, opentab->s->key_length)) + { + my_error(ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG, MYF(0), + table->table->s->table_name); + DBUG_RETURN(-1); + } + } + } + + if (check_lock_and_start_stmt(thd, table->table, table->lock_type)) { ha_rollback_stmt(thd); DBUG_RETURN(-1); -- cgit v1.2.1