From 8b0ece5e88de965aeb2af410d193bf8c6d96dcde Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 5 Nov 2004 17:29:47 +0200 Subject: new lock for multiupdate: - open and create derived tables - detect which tables should be locked for write - lock and fill derived tables some unitialized variables fixed mysql-test/r/lock_multi.result: correct results returned mysql-test/r/multi_update.result: correct results returned mysql-test/r/view.result: correct results returned mysql-test/t/multi_update.test: correct results returned mysql-test/t/view.test: correct results returned sql/mysql_priv.h: derived tables processing splited on table creation and table filling sql/sql_base.cc: derived tables processing splited on table creation and table filling sql/sql_class.h: function to detect when we need fill derived tables sql/sql_derived.cc: derived tables processing splited on table creation and table filling sql/sql_lex.cc: fixed uninitialized value sql/sql_load.cc: fixed uninitialized value sql/sql_parse.cc: initialization muved (will be done for all queries) sql/sql_prepare.cc: preparation of multiupdate changed a bit because new locking procedure sql/sql_update.cc: new lock for multiupdate: - open and create derived tables - detect which tables should be locked for write - lock and fill derived tables sql/table.h: place to store select_result between creation and filling tables --- mysql-test/r/lock_multi.result | 2 +- mysql-test/r/multi_update.result | 2 +- mysql-test/r/view.result | 6 +- mysql-test/t/multi_update.test | 4 +- mysql-test/t/view.test | 4 - sql/mysql_priv.h | 7 +- sql/sql_base.cc | 13 ++- sql/sql_class.h | 8 +- sql/sql_derived.cc | 236 ++++++++++++++++++++++----------------- sql/sql_lex.cc | 1 + sql/sql_load.cc | 2 +- sql/sql_parse.cc | 1 - sql/sql_prepare.cc | 6 +- sql/sql_update.cc | 73 +++++++----- sql/table.h | 6 + 15 files changed, 216 insertions(+), 155 deletions(-) diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 4eb4ff6cdc2..9eedbf50064 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -26,7 +26,7 @@ lock table t1 read; update t1,t2 set c=a where b=d; select c from t2; c -1 +2 drop table t1; drop table t2; create table t1 (a int); diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index df642b00673..5ef0ff8e82f 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -155,7 +155,6 @@ ERROR HY000: Table 't2' was locked with a READ lock and can't be updated UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; ERROR HY000: Table 't2' was locked with a READ lock and can't be updated UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; -ERROR HY000: Table 't2' was locked with a READ lock and can't be updated unlock tables; LOCK TABLES t1 write, t2 write; UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; @@ -461,6 +460,7 @@ drop table t1, t2, t3; create table t1 (col1 int); create table t2 (col1 int); update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; +ERROR HY000: You can't specify target table 't1' for update in FROM clause delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1; ERROR HY000: You can't specify target table 't1' for update in FROM clause drop table t1,t2; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 9e9ad7a6da6..3f12a582868 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1339,16 +1339,14 @@ c prepare stmt1 from "update v1,t1 set v1.s1=? where t1.s1=v1.s1"; set @arg='d'; execute stmt1 using @arg; -ERROR HY000: Table 't1' is read only select * from v1; s1 -c +d set @arg='e'; execute stmt1 using @arg; -ERROR HY000: Table 't1' is read only select * from v1; s1 -c +e deallocate prepare stmt1; drop view v1; drop table t1; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 8a213ab791c..1e1490cd3f1 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -159,8 +159,6 @@ LOCK TABLES t1 write, t2 read; DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n; --error 1099 UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; ---QQ This should not generate an error ---error 1099 UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; unlock tables; LOCK TABLES t1 write, t2 write; @@ -428,7 +426,7 @@ drop table t1, t2, t3; # create table t1 (col1 int); create table t2 (col1 int); --- QQ The following should give error 1093 +-- error 1093 update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; -- error 1093 delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 17077d1d086..8e38b5616f8 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1300,13 +1300,9 @@ update v1,t1 set v1.s1='c' where t1.s1=v1.s1; select * from v1; prepare stmt1 from "update v1,t1 set v1.s1=? where t1.s1=v1.s1"; set @arg='d'; --- QQ This should not generate an error ---error 1036 execute stmt1 using @arg; select * from v1; set @arg='e'; --- QQ This should not generate an error ---error 1036 execute stmt1 using @arg; select * from v1; deallocate prepare stmt1; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 960f054b3ac..cd2cacad910 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -565,7 +565,11 @@ int mysql_explain_select(THD *thd, SELECT_LEX *sl, char const *type, select_result *result); int mysql_union(THD *thd, LEX *lex, select_result *result, SELECT_LEX_UNIT *unit); -int mysql_handle_derived(LEX *lex); +int mysql_handle_derived(LEX *lex, int (*processor)(THD *thd, + st_lex *lex, + st_table_list *table)); +int mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *t); +int mysql_derived_filling(THD *thd, LEX *lex, TABLE_LIST *t); Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, Item ***copy_func, Field **from_field, bool group, bool modify_item, uint convert_blob_length); @@ -792,7 +796,6 @@ void wait_for_refresh(THD *thd); int open_tables(THD *thd, TABLE_LIST *tables, uint *counter); int simple_open_n_lock_tables(THD *thd,TABLE_LIST *tables); int open_and_lock_tables(THD *thd,TABLE_LIST *tables); -void relink_tables_for_derived(THD *thd); int lock_tables(THD *thd, TABLE_LIST *tables, uint counter); TABLE *open_temporary_table(THD *thd, const char *path, const char *db, const char *table_name, bool link_in_list); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index b3be1fc7338..959af0067e7 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -44,6 +44,7 @@ static my_bool open_new_frm(const char *path, const char *alias, uint db_stat, uint prgflag, uint ha_open_flags, TABLE *outparam, TABLE_LIST *table_desc, MEM_ROOT *mem_root); +static void relink_tables_for_multidelete(THD *thd); extern "C" byte *table_cache_key(const byte *record,uint *length, my_bool not_used __attribute__((unused))) @@ -1857,21 +1858,23 @@ int open_and_lock_tables(THD *thd, TABLE_LIST *tables) { DBUG_ENTER("open_and_lock_tables"); uint counter; - if (open_tables(thd, tables, &counter) || + if (open_tables(thd, tables, &counter) || lock_tables(thd, tables, counter) || - mysql_handle_derived(thd->lex)) + mysql_handle_derived(thd->lex, &mysql_derived_prepare) || + (thd->fill_derived_tables() && + mysql_handle_derived(thd->lex, &mysql_derived_filling))) DBUG_RETURN(thd->net.report_error ? -1 : 1); /* purecov: inspected */ - relink_tables_for_derived(thd); + relink_tables_for_multidelete(thd); DBUG_RETURN(0); } /* Let us propagate pointers to open tables from global table list - to table lists in particular selects if needed. + to table lists for multi-delete */ -void relink_tables_for_derived(THD *thd) +static void relink_tables_for_multidelete(THD *thd) { if (thd->lex->all_selects_list->next_select_in_list() || thd->lex->time_zone_tables_used) diff --git a/sql/sql_class.h b/sql/sql_class.h index fcaebe64b43..0da77d5636a 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1131,8 +1131,12 @@ public: { return command == COM_PREPARE; } - inline gptr trans_alloc(unsigned int size) - { + inline bool fill_derived_tables() + { + return !only_prepare() && !lex->only_view_structure(); + } + inline gptr trans_alloc(unsigned int size) + { return alloc_root(&transaction.mem_root,size); } diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 0923cf811f5..10ef6a081b6 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -25,15 +25,15 @@ #include "sql_select.h" #include "sql_acl.h" -static int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *s, - TABLE_LIST *t); + /* - Resolve derived tables in all queries + call given derived table processor (preparing or filling tables) SYNOPSIS mysql_handle_derived() lex LEX for this thread + processor procedure of derived table processing RETURN 0 ok @@ -42,7 +42,7 @@ static int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *s, */ int -mysql_handle_derived(LEX *lex) +mysql_handle_derived(LEX *lex, int (*processor)(THD*, LEX*, TABLE_LIST*)) { if (lex->derived_tables) { @@ -55,14 +55,8 @@ mysql_handle_derived(LEX *lex) cursor= cursor->next_local) { int res; - if (cursor->derived && (res= mysql_derived(lex->thd, lex, - cursor->derived, - cursor))) - { + if ((res= (*processor)(lex->thd, lex, cursor))) return res; - } - else if (cursor->ancestor) - cursor->set_ancestor(); } if (lex->describe) { @@ -80,20 +74,16 @@ mysql_handle_derived(LEX *lex) /* - Resolve derived tables in all queries + Create temporary table structure (but do not fill it) SYNOPSIS mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t) thd Thread handle lex LEX for this thread - unit node that contains all SELECT's for derived tables - t TABLE_LIST for the upper SELECT + orig_table_list TABLE_LIST for the upper SELECT IMPLEMENTATION - Derived table is resolved with temporary table. It is created based on the - queries defined. After temporary table is created, if this is not EXPLAIN, - then the entire unit / node is deleted. unit is deleted if UNION is used - for derived table and node is deleted is it is a simple SELECT. + Derived table is resolved with temporary table. After table creation, the above TABLE_LIST is updated with a new table. @@ -107,60 +97,126 @@ mysql_handle_derived(LEX *lex) 0 ok 1 Error -1 Error and error message given -*/ - + */ -static int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, - TABLE_LIST *org_table_list) +int mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *orig_table_list) { - SELECT_LEX *first_select= unit->first_select(); - TABLE *table; - int res; - select_union *derived_result; - bool is_union= first_select->next_select() && - first_select->next_select()->linkage == UNION_TYPE; - SELECT_LEX *save_current_select= lex->current_select; - DBUG_ENTER("mysql_derived"); - - if (!(derived_result= new select_union(0))) - DBUG_RETURN(1); // out of memory - - // st_select_lex_unit::prepare correctly work for single select - if ((res= unit->prepare(thd, derived_result, 0))) - goto exit; - - - derived_result->tmp_table_param.init(); - derived_result->tmp_table_param.field_count= unit->types.elements; - /* - Temp table is created so that it hounours if UNION without ALL is to be - processed - */ - if (!(table= create_tmp_table(thd, &derived_result->tmp_table_param, - unit->types, (ORDER*) 0, - is_union && unit->union_distinct, 1, - (first_select->options | thd->options | - TMP_TABLE_ALL_COLUMNS), - HA_POS_ERROR, - org_table_list->alias))) + SELECT_LEX_UNIT *unit= orig_table_list->derived; + int res= 0; + if (unit) { - res= -1; - goto exit; + SELECT_LEX *first_select= unit->first_select(); + TABLE *table= 0; + select_union *derived_result; + bool is_union= first_select->next_select() && + first_select->next_select()->linkage == UNION_TYPE; + DBUG_ENTER("mysql_derived"); + + if (!(derived_result= new select_union(0))) + DBUG_RETURN(1); // out of memory + + // st_select_lex_unit::prepare correctly work for single select + if ((res= unit->prepare(thd, derived_result, 0))) + goto exit; + + + derived_result->tmp_table_param.init(); + derived_result->tmp_table_param.field_count= unit->types.elements; + /* + Temp table is created so that it hounours if UNION without ALL is to be + processed + */ + if (!(table= create_tmp_table(thd, &derived_result->tmp_table_param, + unit->types, (ORDER*) 0, + is_union && unit->union_distinct, 1, + (first_select->options | thd->options | + TMP_TABLE_ALL_COLUMNS), + HA_POS_ERROR, + orig_table_list->alias))) + { + res= -1; + goto exit; + } + derived_result->set_table(table); + +exit: + /* + if it is preparation PS only or commands that need only VIEW structure + then we do not need real data and we can skip execution (and parameters + is not defined, too) + */ + if (res) + { + if (table) + free_tmp_table(thd, table); + delete derived_result; + } + else + { + if (!thd->fill_derived_tables()) + delete derived_result; + orig_table_list->derived_result= derived_result; + orig_table_list->table= table; + orig_table_list->real_name= table->real_name; + table->derived_select_number= first_select->select_number; + table->tmp_table= TMP_TABLE; +#ifndef NO_EMBEDDED_ACCESS_CHECKS + table->grant.privilege= SELECT_ACL; +#endif + orig_table_list->db= (char *)""; + // Force read of table stats in the optimizer + table->file->info(HA_STATUS_VARIABLE); + /* Add new temporary table to list of open derived tables */ + table->next= thd->derived_tables; + thd->derived_tables= table; + } } - derived_result->set_table(table); + else if (orig_table_list->ancestor) + orig_table_list->set_ancestor(); + return (res); +} + - /* - if it is preparation PS only or commands that need only VIEW structure - then we do not need real data and we can skip execution (and parameters - is not defined, too) +/* + fill derived table + + SYNOPSIS + mysql_derived_filling() + thd Thread handle + lex LEX for this thread + unit node that contains all SELECT's for derived tables + orig_table_list TABLE_LIST for the upper SELECT + + IMPLEMENTATION + Derived table is resolved with temporary table. It is created based on the + queries defined. After temporary table is filled, if this is not EXPLAIN, + then the entire unit / node is deleted. unit is deleted if UNION is used + for derived table and node is deleted is it is a simple SELECT. + + RETURN + 0 ok + 1 Error + -1 Error and error message given */ - if (!thd->only_prepare() && !lex->only_view_structure()) + +int mysql_derived_filling(THD *thd, LEX *lex, TABLE_LIST *orig_table_list) +{ + TABLE *table= orig_table_list->table; + SELECT_LEX_UNIT *unit= orig_table_list->derived; + int res= 0; + + /*check that table creation pass without problem and it is derived table */ + if (table && unit) { + SELECT_LEX *first_select= unit->first_select(); + select_union *derived_result= orig_table_list->derived_result; + SELECT_LEX *save_current_select= lex->current_select; + bool is_union= first_select->next_select() && + first_select->next_select()->linkage == UNION_TYPE; if (is_union) { // execute union without clean up - if (!(res= unit->prepare(thd, derived_result, SELECT_NO_UNLOCK))) - res= unit->exec(); + res= unit->exec(); } else { @@ -173,7 +229,7 @@ static int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, first_select->options&= ~OPTION_FOUND_ROWS; lex->current_select= first_select; - res= mysql_select(thd, &first_select->ref_pointer_array, + res= mysql_select(thd, &first_select->ref_pointer_array, (TABLE_LIST*) first_select->table_list.first, first_select->with_wild, first_select->item_list, first_select->where, @@ -186,49 +242,27 @@ static int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, SELECT_NO_UNLOCK), derived_result, unit, first_select); } - } - if (!res) - { - /* - Here we entirely fix both TABLE_LIST and list of SELECT's as - there were no derived tables - */ - if (derived_result->flush()) - res= 1; - else + if (!res) { - org_table_list->real_name= table->real_name; - org_table_list->table= table; - table->derived_select_number= first_select->select_number; - table->tmp_table= TMP_TABLE; -#ifndef NO_EMBEDDED_ACCESS_CHECKS - table->grant.privilege= SELECT_ACL; -#endif - org_table_list->db= (char *)""; - // Force read of table stats in the optimizer - table->file->info(HA_STATUS_VARIABLE); - } + /* + Here we entirely fix both TABLE_LIST and list of SELECT's as + there were no derived tables + */ + if (derived_result->flush()) + res= 1; - if (!lex->describe) - unit->cleanup(); - if (res) - free_tmp_table(thd, table); + if (!lex->describe) + unit->cleanup(); + } else { - /* Add new temporary table to list of open derived tables */ - table->next= thd->derived_tables; - thd->derived_tables= table; + free_tmp_table(thd, table); + unit->cleanup(); } + lex->current_select= save_current_select; + if (res) + free_tmp_table(thd, table); } - else - { - free_tmp_table(thd, table); - unit->cleanup(); - } - -exit: - delete derived_result; - lex->current_select= save_current_select; - DBUG_RETURN(res); + return res; } diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index d978cc14f64..3cbfd96b6aa 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -176,6 +176,7 @@ void lex_start(THD *thd, uchar *buf,uint length) lex->sphead= NULL; lex->spcont= NULL; lex->trg_table= NULL; + lex->proc_list.first= 0; extern byte *sp_lex_spfuns_key(const byte *ptr, uint *plen, my_bool first); hash_free(&lex->spfuns); diff --git a/sql/sql_load.cc b/sql/sql_load.cc index 1735da6b717..9c2a025e089 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -91,7 +91,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, int error; String *field_term=ex->field_term,*escaped=ex->escaped; String *enclosed=ex->enclosed; - Item *unused_conds; + Item *unused_conds= 0; bool is_fifo=0; #ifndef EMBEDDED_LIBRARY LOAD_FILE_INFO lf_info; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 60f69b578e9..5c83a746f73 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4472,7 +4472,6 @@ mysql_init_select(LEX *lex) { DBUG_ASSERT(lex->result == 0); lex->exchange= 0; - lex->proc_list.first= 0; } } diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index bb2362dd562..212375c8e44 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -1314,7 +1314,11 @@ static int mysql_test_multiupdate(Prepared_statement *stmt, int res; if ((res= multi_update_precheck(stmt->thd, tables))) return res; - return select_like_statement_test(stmt, tables, &mysql_multi_update_prepare); + /* + here we do not pass tables for opening, tables will be opened and locked + by mysql_multi_update_prepare + */ + return select_like_statement_test(stmt, 0, &mysql_multi_update_prepare); } diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 5e91e730079..46b7f0252e8 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -578,10 +578,17 @@ int mysql_multi_update_prepare(THD *thd) TABLE_LIST *table_list= lex->query_tables; List *fields= &lex->select_lex.item_list; TABLE_LIST *tl; - table_map tables_for_update= 0, readonly_tables= 0; + table_map tables_for_update; int res; bool update_view= 0; + uint table_count; + const bool using_lock_tables= thd->locked_tables != 0; DBUG_ENTER("mysql_multi_update_prepare"); + + /* open tables and create derived ones, but do not lock and fill them */ + if (open_tables(thd, table_list, & table_count) || + mysql_handle_derived(lex, &mysql_derived_prepare)) + DBUG_RETURN(thd->net.report_error ? -1 : 1); /* Ensure that we have update privilege for all tables and columns in the SET part @@ -606,9 +613,9 @@ int mysql_multi_update_prepare(THD *thd) call in setup_tables()). */ if (setup_tables(thd, table_list, &lex->select_lex.where) || - (thd->lex->select_lex.no_wrap_view_item= 1, + (lex->select_lex.no_wrap_view_item= 1, res= setup_fields(thd, 0, table_list, *fields, 1, 0, 0), - thd->lex->select_lex.no_wrap_view_item= 0, + lex->select_lex.no_wrap_view_item= 0, res)) DBUG_RETURN(-1); @@ -626,18 +633,10 @@ int mysql_multi_update_prepare(THD *thd) DBUG_RETURN(-1); } - { - // Find tables used in items - List_iterator_fast it(*fields); - Item *item; - while ((item= it++)) - { - tables_for_update|= item->used_tables(); - } - } + tables_for_update= get_table_map(fields); /* - Count tables and setup timestamp handling + Setup timestamp handling and locking mode */ for (tl= table_list; tl ; tl= tl->next_local) { @@ -651,22 +650,43 @@ int mysql_multi_update_prepare(THD *thd) table->timestamp_field->query_id == thd->query_id) table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; - if (!tl->updatable || check_key_in_view(thd, tl)) - readonly_tables|= table->map; - } - if (tables_for_update & readonly_tables) - { - // find readonly table/view which cause error - for (tl= table_list; tl ; tl= tl->next_local) + /* if table will be updated then check that it is unique */ + if (table->map & tables_for_update) { - if ((readonly_tables & tl->table->map) && - (tables_for_update & tl->table->map)) + if (!tl->updatable || check_key_in_view(thd, tl)) { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE"); - DBUG_RETURN(-1); + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE"); + DBUG_RETURN(-1); } + + /* + Multi-update can't be constructed over-union => we always have + single SELECT on top and have to check underlaying SELECTs of it + */ + if (lex->select_lex.check_updateable_in_subqueries(tl->db, + tl->real_name)) + { + my_error(ER_UPDATE_TABLE_USED, MYF(0), tl->real_name); + DBUG_RETURN(-1); + } + DBUG_PRINT("info",("setting table `%s` for update", tl->alias)); + tl->lock_type= lex->multi_lock_option; + tl->updating= 1; + } + else + { + DBUG_PRINT("info",("setting table `%s` for read-only", tl->alias)); + tl->lock_type= TL_READ; + tl->updating= 0; } + if (!using_lock_tables) + tl->table->reginfo.lock_type= tl->lock_type; } + /* now lock and fill tables */ + if (lock_tables(thd, table_list, table_count) || + (thd->fill_derived_tables() && + mysql_handle_derived(lex, &mysql_derived_filling))) + DBUG_RETURN(thd->net.report_error ? -1 : 1); DBUG_RETURN (0); } @@ -688,11 +708,6 @@ int mysql_multi_update(THD *thd, multi_update *result; DBUG_ENTER("mysql_multi_update"); - /* QQ: This should be fixed soon to get lower granularity locks */ - select_lex->set_lock_for_tables(thd->lex->multi_lock_option); - if ((res= open_and_lock_tables(thd, table_list))) - DBUG_RETURN(res); - if ((res= mysql_multi_update_prepare(thd))) DBUG_RETURN(res); diff --git a/sql/table.h b/sql/table.h index d5bbd0ac2e2..0250d713a56 100644 --- a/sql/table.h +++ b/sql/table.h @@ -222,6 +222,7 @@ struct st_table { #define VIEW_CHECK_SKIP 2 struct st_lex; +struct select_union; typedef struct st_table_list { @@ -237,6 +238,11 @@ typedef struct st_table_list /* ... join ... USE INDEX ... IGNORE INDEX */ List *use_index, *ignore_index; TABLE *table; /* opened table */ + /* + select_result for derived table to pass it from table creation to table + filling procedure + */ + select_union *derived_result; /* Reference from aux_tables to local list entry of main select of multi-delete statement: -- cgit v1.2.1 From f1b9bf699eeec11d7d619683d74c01d6172a7e5f Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 8 Nov 2004 01:54:23 +0200 Subject: postreview fixes mysql-test/r/multi_update.result: test with multiupdate reopening tables mysql-test/t/multi_update.test: test with multiupdate reopening tables sql/item.cc: processor for cleunuping items in item tree sql/item.h: processor for cleunuping items in item tree sql/sql_update.cc: fixed case when lock reopened tables sql/table.cc: methos for cleunup view itema of table if they are present sql/table.h: methos for cleunup view itema of table if they are present --- mysql-test/r/multi_update.result | 21 ++++++++++++++++++++ mysql-test/t/multi_update.test | 32 ++++++++++++++++++++++++++++++ sql/item.cc | 17 ++++++++++++++++ sql/item.h | 1 + sql/sql_update.cc | 42 +++++++++++++++++++++++++++++++++++++--- sql/table.cc | 18 +++++++++++++++++ sql/table.h | 1 + 7 files changed, 129 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 5ef0ff8e82f..b853b05f153 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -490,3 +490,24 @@ insert into t2 select * from t1; delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; set @@storage_engine=@ttype_save; drop table t1,t2; +create table t1 (a int, b int); +insert into t1 values (1, 2), (2, 3), (3, 4); +create table t2 (a int); +insert into t2 values (10), (20), (30); +create view v1 as select a as b, a/10 as a from t2; +lock table t1 write; + alter table t1 add column c int default 100 after a; + update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a; +unlock tables; +select * from t1; +a c b +1 100 13 +2 100 25 +3 100 37 +select * from t2; +a +10 +20 +30 +drop view v1; +drop table t1, t2; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 1e1490cd3f1..ac60eef6d05 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -477,3 +477,35 @@ delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; set @@storage_engine=@ttype_save; drop table t1,t2; + +create table t1 (a int, b int); +insert into t1 values (1, 2), (2, 3), (3, 4); +create table t2 (a int); +insert into t2 values (10), (20), (30); +create view v1 as select a as b, a/10 as a from t2; + +connect (locker,localhost,root,,test); +connection locker; +lock table t1 write; + +connect (changer,localhost,root,,test); +connection changer; +send alter table t1 add column c int default 100 after a; + +connect (updater,localhost,root,,test); +connection updater; +send update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a; + +connection locker; +sleep 2; +unlock tables; + +connection changer; +reap; + +connection updater; +reap; +select * from t1; +select * from t2; +drop view v1; +drop table t1, t2; diff --git a/sql/item.cc b/sql/item.cc index 6730b0dd4a2..4c738c9a4c5 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -117,6 +117,23 @@ void Item::cleanup() DBUG_VOID_RETURN; } + +/* + cleanup() item if it is 'fixed' + + SYNOPSIS + cleanup_processor() + arg - a dummy parameter, is not used here +*/ + +bool Item::cleanup_processor(byte *arg) +{ + if (fixed) + cleanup(); + return FALSE; +} + + Item_ident::Item_ident(const char *db_name_par,const char *table_name_par, const char *field_name_par) :orig_db_name(db_name_par), orig_table_name(table_name_par), diff --git a/sql/item.h b/sql/item.h index d3e53af1523..eed0b065a83 100644 --- a/sql/item.h +++ b/sql/item.h @@ -283,6 +283,7 @@ public: virtual bool remove_dependence_processor(byte * arg) { return 0; } virtual bool remove_fixed(byte * arg) { fixed= 0; return 0; } + virtual bool cleanup_processor(byte *arg); virtual bool collect_item_field_processor(byte * arg) { return 0; } virtual Item *equal_fields_propagator(byte * arg) { return this; } virtual Item *set_no_const_sub(byte *arg) { return this; } diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 46b7f0252e8..7bd445088cf 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -575,6 +575,7 @@ static table_map get_table_map(List *items) int mysql_multi_update_prepare(THD *thd) { LEX *lex= thd->lex; + ulong opened_tables; TABLE_LIST *table_list= lex->query_tables; List *fields= &lex->select_lex.item_list; TABLE_LIST *tl; @@ -682,10 +683,45 @@ int mysql_multi_update_prepare(THD *thd) if (!using_lock_tables) tl->table->reginfo.lock_type= tl->lock_type; } + + opened_tables= thd->status_var.opened_tables; /* now lock and fill tables */ - if (lock_tables(thd, table_list, table_count) || - (thd->fill_derived_tables() && - mysql_handle_derived(lex, &mysql_derived_filling))) + if (lock_tables(thd, table_list, table_count)) + DBUG_RETURN(thd->net.report_error ? -1 : 1); + + /* + we have to re-call fixfields for fixed items, because lock maybe + reopened tables + */ + if (opened_tables != thd->status_var.opened_tables) + { + /* + Fields items cleanup(). There are only Item_fields in the list, so we + do not do Item tree walking + */ + List_iterator_fast it(*fields); + Item *item; + while (item= it++) + { + item->cleanup(); + } + + /* We have to cleunup translation tables of views. */ + for (TABLE_LIST *tbl= table_list; tbl; tbl= tbl->next_global) + tbl->cleanup_items(); + + /* undone setup_tables() */ + table_list->setup_is_done= 0; + + if (setup_tables(thd, table_list, &lex->select_lex.where) || + (lex->select_lex.no_wrap_view_item= 1, + res= setup_fields(thd, 0, table_list, *fields, 1, 0, 0), + lex->select_lex.no_wrap_view_item= 0, + res)) + DBUG_RETURN(-1); + } + if (thd->fill_derived_tables() && + mysql_handle_derived(lex, &mysql_derived_filling)) DBUG_RETURN(thd->net.report_error ? -1 : 1); DBUG_RETURN (0); } diff --git a/sql/table.cc b/sql/table.cc index 0116cf180c1..8c58c5296ad 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -1735,6 +1735,24 @@ err: } +/* + cleunup items belonged to view fields translation table + + SYNOPSIS + st_table_list::cleanup_items() +*/ + +void st_table_list::cleanup_items() +{ + if (!field_translation) + return; + + Item **end= field_translation + view->select_lex.item_list.elements; + for (Item **item= field_translation; item < end; item++) + (*item)->walk(&Item::cleanup_processor, 0); +} + + /* check CHECK OPTION condition diff --git a/sql/table.h b/sql/table.h index 0250d713a56..af7d90a8291 100644 --- a/sql/table.h +++ b/sql/table.h @@ -312,6 +312,7 @@ typedef struct st_table_list void set_ancestor(); int view_check_option(THD *thd, bool ignore_failure); bool setup_ancestor(THD *thd, Item **conds, uint8 check_option); + void cleanup_items(); bool placeholder() {return derived || view; } void print(THD *thd, String *str); inline st_table_list *next_independent() -- cgit v1.2.1 From 55e08c314ae835c45b7962aed1d3e985a3bbcbc8 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 8 Nov 2004 10:06:32 +0100 Subject: decimal.c: test_ro("999999999999999999999.999", 0, CEILING); test_da("5", "-6.0"); ignore: added gdbinit BitKeeper/etc/ignore: added gdbinit strings/decimal.c: test_ro("999999999999999999999.999", 0, CEILING); test_da("5", "-6.0"); --- .bzrignore | 1 + strings/decimal.c | 10 ++++++++++ 2 files changed, 11 insertions(+) diff --git a/.bzrignore b/.bzrignore index c83b4ed4388..94dad72acee 100644 --- a/.bzrignore +++ b/.bzrignore @@ -939,3 +939,4 @@ vio/test-ssl vio/test-sslclient vio/test-sslserver vio/viotest-ssl +gdbinit diff --git a/strings/decimal.c b/strings/decimal.c index 3d3ddc36116..09f6cb6bb51 100644 --- a/strings/decimal.c +++ b/strings/decimal.c @@ -921,6 +921,8 @@ int decimal_round(decimal *from, decimal *to, int scale, decimal_round_mode mode *buf1-=DIG_BASE; while (carry && --buf1 >= to->buf) ADD(*buf1, *buf1, 0, carry); + if (to->buf[0] > from->buf[0]) + to->intg++; if (unlikely(carry)) { /* shifting the number to create space for new digit */ @@ -1150,6 +1152,8 @@ static int do_sub(decimal *from1, decimal *from2, decimal *to) buf1=start1+intg1+frac1; stop1=start1+intg1+frac2; buf2=start2+intg2+frac2; + while (frac0-- > frac1) + *--buf0=0; while (buf1 > stop1) *--buf0=*--buf1; } @@ -1158,6 +1162,8 @@ static int do_sub(decimal *from1, decimal *from2, decimal *to) buf1=start1+intg1+frac1; buf2=start2+intg2+frac2; stop2=start2+intg2+frac1; + while (frac0-- > frac2) + *--buf0=0; while (buf2 > stop2) { SUB(*--buf0, 0, *--buf2, carry); @@ -1968,6 +1974,7 @@ main() test_ds("-123.45", "-12345"); test_da("123.45", "-12345"); test_da("-123.45", "12345"); + test_da("5", "-6.0"); printf("==== decimal_mul ====\n"); test_dm("12", "10"); @@ -2059,6 +2066,9 @@ main() test_ro("-15.1",0,CEILING); test_ro("15.1",0,FLOOR); test_ro("-15.1",0,FLOOR); + test_ro("999999999999999999999.999", 0, CEILING); + test_ro("-999999999999999999999.999", 0, FLOOR); + return 0; } -- cgit v1.2.1 From 20c82f5c014e9902f234f925a218895f3b91b154 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 8 Nov 2004 14:52:15 +0200 Subject: Fixed a bug in UPDATE statement with no index column in where condition locks all rows (BUG #3300). When using innobase_locks_unsafe_for_binlog option InnoDB does not take locks for those rows which do not belong to the result set or werent changed by the query. This fix removes unnecessary locks also from SELECT and DELETE queries. innobase/include/row0mysql.h: Added prototype for row_unlock_for_mysql() function which does an unlock of a row for MySQL. innobase/include/trx0trx.h: Added a field trx_create_lock to a transaction structure. This field is TRUE if we have created a new lock for a record accessed. innobase/lock/lock0lock.c: Set lock create flag if lock is created and reset this flag before transaction requests a lock. innobase/row/row0mysql.c: Add support for unlocking a row in InnoDB. If we are using innobase_locks_unsafe_for_binlog option then all those record locks obtained by SQL-query which do not belong to result set or were not modified are unlocked i.e. we remove the lock from those records. sql/ha_innodb.cc: Added support for a unlock_row interface in InnoDB. sql/ha_innodb.h: Added prototype for a function unlock_row(). --- innobase/include/row0mysql.h | 11 ++++++++++ innobase/include/trx0trx.h | 2 ++ innobase/lock/lock0lock.c | 30 ++++++++++++++++++++++++-- innobase/row/row0mysql.c | 51 ++++++++++++++++++++++++++++++++++++++++++++ sql/ha_innodb.cc | 26 ++++++++++++++++++++++ sql/ha_innodb.h | 1 + 6 files changed, 119 insertions(+), 2 deletions(-) diff --git a/innobase/include/row0mysql.h b/innobase/include/row0mysql.h index 6e1865dae1d..581f1bcade3 100644 --- a/innobase/include/row0mysql.h +++ b/innobase/include/row0mysql.h @@ -233,6 +233,17 @@ row_update_for_mysql( the MySQL format */ row_prebuilt_t* prebuilt); /* in: prebuilt struct in MySQL handle */ + +/************************************************************************* +Does an unlock of a row for MySQL. */ + +int +row_unlock_for_mysql( +/*=================*/ + /* out: error code or DB_SUCCESS */ + row_prebuilt_t* prebuilt); /* in: prebuilt struct in MySQL + handle */ + /************************************************************************* Creates an query graph node of 'update' type to be used in the MySQL interface. */ diff --git a/innobase/include/trx0trx.h b/innobase/include/trx0trx.h index 7eb91048684..602291f946a 100644 --- a/innobase/include/trx0trx.h +++ b/innobase/include/trx0trx.h @@ -423,6 +423,8 @@ struct trx_struct{ lock_t* auto_inc_lock; /* possible auto-inc lock reserved by the transaction; note that it is also in the lock list trx_locks */ + ibool trx_create_lock;/* this is TRUE if we have created a + new lock for a record accessed */ ulint n_lock_table_exp;/* number of explicit table locks (LOCK TABLES) reserved by the transaction, stored in trx_locks */ diff --git a/innobase/lock/lock0lock.c b/innobase/lock/lock0lock.c index 68073647248..44eee53212a 100644 --- a/innobase/lock/lock0lock.c +++ b/innobase/lock/lock0lock.c @@ -1617,6 +1617,9 @@ lock_rec_create( HASH_INSERT(lock_t, hash, lock_sys->rec_hash, lock_rec_fold(space, page_no), lock); + /* Note that we have create a new lock */ + trx->trx_create_lock = TRUE; + if (type_mode & LOCK_WAIT) { lock_set_lock_and_trx_wait(lock, trx); @@ -1791,6 +1794,15 @@ lock_rec_add_to_queue( if (similar_lock && !somebody_waits && !(type_mode & LOCK_WAIT)) { + /* If the nth bit of a record lock is already set then we + do not set a new lock bit, otherwice we set */ + + if (lock_rec_get_nth_bit(similar_lock, heap_no)) { + trx->trx_create_lock = FALSE; + } else { + trx->trx_create_lock = TRUE; + } + lock_rec_set_nth_bit(similar_lock, heap_no); return(similar_lock); @@ -1822,6 +1834,7 @@ lock_rec_lock_fast( { lock_t* lock; ulint heap_no; + trx_t* trx; #ifdef UNIV_SYNC_DEBUG ut_ad(mutex_own(&kernel_mutex)); @@ -1840,9 +1853,12 @@ lock_rec_lock_fast( lock = lock_rec_get_first_on_page(rec); + trx = thr_get_trx(thr); + trx->trx_create_lock = FALSE; + if (lock == NULL) { if (!impl) { - lock_rec_create(mode, rec, index, thr_get_trx(thr)); + lock_rec_create(mode, rec, index, trx); } return(TRUE); @@ -1853,13 +1869,23 @@ lock_rec_lock_fast( return(FALSE); } - if (lock->trx != thr_get_trx(thr) + if (lock->trx != trx || lock->type_mode != (mode | LOCK_REC) || lock_rec_get_n_bits(lock) <= heap_no) { return(FALSE); } if (!impl) { + + /* If the nth bit of a record lock is already set then we + do not set a new lock bit, otherwice we set */ + + if (lock_rec_get_nth_bit(lock, heap_no)) { + trx->trx_create_lock = FALSE; + } else { + trx->trx_create_lock = TRUE; + } + lock_rec_set_nth_bit(lock, heap_no); } diff --git a/innobase/row/row0mysql.c b/innobase/row/row0mysql.c index 241ddc310e8..9ca0b7306fc 100644 --- a/innobase/row/row0mysql.c +++ b/innobase/row/row0mysql.c @@ -1186,6 +1186,57 @@ run_again: return((int) err); } +/************************************************************************* +Does an unlock of a row for MySQL. */ + +int +row_unlock_for_mysql( +/*=================*/ + /* out: error code or DB_SUCCESS */ + row_prebuilt_t* prebuilt) /* in: prebuilt struct in MySQL + handle */ +{ + rec_t* rec; + btr_pcur_t* cur = prebuilt->pcur; + trx_t* trx = prebuilt->trx; + mtr_t mtr; + + ut_ad(prebuilt && trx); + ut_ad(trx->mysql_thread_id == os_thread_get_curr_id()); + + trx->op_info = "unlock_row"; + + if (srv_locks_unsafe_for_binlog) { + if (trx->trx_create_lock == TRUE) { + + mtr_start(&mtr); + + /* Restore a cursor position and find a record */ + btr_pcur_restore_position(BTR_SEARCH_LEAF, cur, &mtr); + rec = btr_pcur_get_rec(cur); + + if (rec) { + + lock_rec_reset_and_release_wait(rec); + } else { + fputs("InnoDB: Error: " + "Record for the lock not found\n", + stderr); + mem_analyze_corruption((byte*) trx); + ut_error; + } + + trx->trx_create_lock = FALSE; + mtr_commit(&mtr); + } + + } + + trx->op_info = ""; + + return(DB_SUCCESS); +} + /************************************************************************** Does a cascaded delete or set null in a foreign key operation. */ diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc index efd74a543c2..722a6259d3d 100644 --- a/sql/ha_innodb.cc +++ b/sql/ha_innodb.cc @@ -2690,6 +2690,32 @@ ha_innobase::delete_row( DBUG_RETURN(error); } +/************************************************************************** +Deletes a lock set to a row */ + +void +ha_innobase::unlock_row(void) +/*=========================*/ +{ + row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt; + + DBUG_ENTER("ha_innobase::unlock_row"); + + ut_ad(prebuilt->trx == + (trx_t*) current_thd->transaction.all.innobase_tid); + + if (last_query_id != user_thd->query_id) { + ut_print_timestamp(stderr); + fprintf(stderr, +" InnoDB: Error: last_query_id is %lu != user_thd_query_id is %lu\n", + (ulong)last_query_id, (ulong)user_thd->query_id); + mem_analyze_corruption((byte *) prebuilt->trx); + ut_error; + } + + row_unlock_for_mysql(prebuilt); +} + /********************************************************************** Initializes a handle to use an index. */ diff --git a/sql/ha_innodb.h b/sql/ha_innodb.h index 57e136a8fba..b74af1db90b 100644 --- a/sql/ha_innodb.h +++ b/sql/ha_innodb.h @@ -120,6 +120,7 @@ class ha_innobase: public handler int write_row(byte * buf); int update_row(const byte * old_data, byte * new_data); int delete_row(const byte * buf); + void unlock_row(); int index_init(uint index); int index_end(); -- cgit v1.2.1