From d1d166875ac2eec18d15611d0d82ba49b6def288 Mon Sep 17 00:00:00 2001 From: Jorgen Loland Date: Mon, 21 Feb 2011 16:49:03 +0100 Subject: BUG#11762751: UPDATE STATEMENT THROWS AN ERROR, BUT STILL UPDATES THE TABLE ENTRIES (formerly 55385) BUG#11764529: MULTI UPDATE+INNODB REPORTS ER_KEY_NOT_FOUND IF A TABLE IS UPDATED TWICE (formerly 57373) If multiple-table update updates a row through two aliases and the first update physically moves the row, the second update will fail to locate the row. This results in different errors depending on storage engine: * MyISAM: Got error 134 from storage engine * InnoDB: Can't find record in 'tbl' None of these errors accurately describe the problem. Furthermore, since MyISAM is non-transactional, the update executed first will be performed while the second will not. In addition, for two equal multiple-table update statements, one could succeed and the other fail based on whether or not the record actually moved or not. This was inconsistent. Two update operations may physically move a row: 1) Update of a column in a clustered primary key 2) Update of a column used to calculate which partition the row belongs to BUG#11764529 is about case 1) above, BUG#11762751 was about case 2). The fix for these bugs is to return with an error if multiple-table update is about to: a) Update a table through multiple aliases, and b) Perform an update that may physically more the row in at least one of these aliases This avoids * partial updates as described for MyISAM above, * provides the same error message that describes the actual problem for all SEs * inconsistent behavior where a statement fails or succeeds based on e.g. the partitioning algorithm of the table. mysql-test/r/multi_update.result: Add test for bug#57373 mysql-test/r/multi_update_innodb.result: Add test for bug#57373 mysql-test/r/partition.result: Add test for bug#55385 mysql-test/t/multi_update.test: Add test for bug#57373 mysql-test/t/multi_update_innodb.test: Add test for bug#57373 mysql-test/t/partition.test: Add test for bug#55385 sql/handler.cc: Translate handler error HA_ERR_RECORD_DELETED to server error sql/share/errmsg-utf8.txt: New error message for multi-table update where the same table is updated multiple times. sql/sql_update.cc: Add function unsafe_key_update() --- sql/sql_update.cc | 98 ++++++++++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 97 insertions(+), 1 deletion(-) (limited to 'sql/sql_update.cc') diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 08d804efcb6..59356540201 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -998,6 +998,98 @@ static table_map get_table_map(List *items) return map; } +/** + If one row is updated through two different aliases and the first + update physically moves the row, the second update will error + because the row is no longer located where expected. This function + checks if the multiple-table update is about to do that and if so + returns with an error. + + The following update operations physically moves rows: + 1) Update of a column in a clustered primary key + 2) Update of a column used to calculate which partition the row belongs to + + This function returns with an error if both of the following are + true: + + a) A table in the multiple-table update statement is updated + through multiple aliases (including views) + b) At least one of the updates on the table from a) may physically + moves the row. Note: Updating a column used to calculate which + partition a row belongs to does not necessarily mean that the + row is moved. The new value may or may not belong to the same + partition. + + @param leaves First leaf table + @param tables_for_update Map of tables that are updated + + @return + true if the update is unsafe, in which case an error message is also set, + false otherwise. +*/ +static +bool unsafe_key_update(TABLE_LIST *leaves, table_map tables_for_update) +{ + TABLE_LIST *tl= leaves; + + for (tl= leaves; tl ; tl= tl->next_leaf) + { + if (tl->table->map & tables_for_update) + { + TABLE *table1= tl->table; + bool primkey_clustered= (table1->file->primary_key_is_clustered() && + table1->s->primary_key != MAX_KEY); + + bool table_partitioned= false; +#ifdef WITH_PARTITION_STORAGE_ENGINE + table_partitioned= (table1->part_info != NULL); +#endif + + if (!table_partitioned && !primkey_clustered) + continue; + + for (TABLE_LIST* tl2= tl->next_leaf; tl2 ; tl2= tl2->next_leaf) + { + /* + Look at "next" tables only since all previous tables have + already been checked + */ + TABLE *table2= tl2->table; + if (table2->map & tables_for_update && table1->s == table2->s) + { + // A table is updated through two aliases + if (table_partitioned && + (partition_key_modified(table1, table1->write_set) || + partition_key_modified(table2, table2->write_set))) + { + // Partitioned key is updated + my_error(ER_MULTI_UPDATE_KEY_CONFLICT, MYF(0), + tl->belong_to_view ? tl->belong_to_view->alias + : tl->alias, + tl2->belong_to_view ? tl2->belong_to_view->alias + : tl2->alias); + return true; + } + + if (primkey_clustered && + (bitmap_is_set(table1->write_set, table1->s->primary_key) || + bitmap_is_set(table2->write_set, table2->s->primary_key))) + { + // Clustered primary key is updated + my_error(ER_MULTI_UPDATE_KEY_CONFLICT, MYF(0), + tl->belong_to_view ? tl->belong_to_view->alias + : tl->alias, + tl2->belong_to_view ? tl2->belong_to_view->alias + : tl2->alias); + return true; + } + } + } + } + } + return false; +} + /* make update specific preparation and checks after opening tables @@ -1077,10 +1169,14 @@ int mysql_multi_update_prepare(THD *thd) thd->table_map_for_update= tables_for_update= get_table_map(fields); + leaves= lex->select_lex.leaf_tables; + + if (unsafe_key_update(leaves, tables_for_update)) + DBUG_RETURN(true); + /* Setup timestamp handling and locking mode */ - leaves= lex->select_lex.leaf_tables; for (tl= leaves; tl; tl= tl->next_leaf) { TABLE *table= tl->table; -- cgit v1.2.1