From 1fa7c69d3119e9da4c0afdb57684c7f0973b4838 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 24 May 2005 22:19:33 +0400 Subject: Fix for bugs: #5860 "Multi-table UPDATE does not activate update triggers" #6812 "Triggers are not activated for INSERT ... SELECT" #8755 "Trigger is not activated by LOAD DATA". This patch also implements proper handling of triggers for special forms of insert like REPLACE or INSERT ... ON DUPLICATE KEY UPDATE. Also now we don't call after trigger in case when we have failed to inserted/update or delete row. Trigger failure should stop statement execution. I have not properly tested handling of errors which happen inside of triggers in this patch, since it is simplier to do this once we will be able to access tables from triggers. mysql-test/r/trigger.result: Added tests for triggers behavior for various non-standard forms of INSERT such as REPLACE and INSERT ... ON DUPLICATE KEY UPDATE. Also added tests for bugs #5860 "Multi-table UPDATE does not activate update triggers", #6812 "Triggers are not activated for INSERT ... SELECT" and #8755 "Trigger is not activated by LOAD DATA". mysql-test/t/trigger.test: Added tests for triggers behavior for various non-standard forms of INSERT such as REPLACE and INSERT ... ON DUPLICATE KEY UPDATE. Also added tests for bugs #5860 "Multi-table UPDATE does not activate update triggers", #6812 "Triggers are not activated for INSERT ... SELECT" and #8755 "Trigger is not activated by LOAD DATA". sql/item.cc: Since it turned out that at trigger loading time we can't say in which buffer TABLE::record[0] or record[1] old version of row will be stored we have to change our approach to binding of Item_trigger_field to Field instances. Now after trigger parsing (in Item_trigger_field::setup_table()) we only find index of proper Field in the TABLE::field array. Then before trigger is invoked we set Table_triggers_list::old_field/new_field so they point to arrays holding Field instances bound to buffers with proper row versions. And as last step in Item_trigger_field::fix_fields() we get pointer to Field from those arrays using saved field index. Item_trigger_field::setup_field()/fix_fields() were changed to implement this approach. sql/item.h: Since it turned out that at trigger loading time we can't say in which buffer TABLE::record[0] or record[1] old version of row will be stored we have to change our approach to binding of Item_trigger_field to Field instances. Now after trigger parsing (in Item_trigger_field::setup_table()) we only find index of proper Field in the TABLE::field array. Then before trigger is invoked we set Table_triggers_list::old_field/new_field so they point to arrays holding Field instances bound to buffers with proper row versions. And as last step in Item_trigger_field::fix_fields() we get pointer to Field from those arrays using saved field index. Item_trigger_field: - Added field_idx member to store index of Field object corresponding to this Item in TABLE::field array. - Added triggers member to be able to access to parent Table_trigger_list object from fix_fields() method. - setup_field() no longer needs to know for which type of event this trigger is, since it does not make decision Field for which buffer (record[0] or record[1] is appropriate for this Item_trigger_field) sql/mysql_priv.h: Added fill_record_n_invoke_before_triggers() methods. They are simple wrappers around fill_record() which invoke proper before trigger right after filling record with values. sql/sql_base.cc: Added fill_record_n_invoke_before_triggers() methods. They are simple wrappers around fill_record() which invoke proper before trigger right after filling record with values. sql/sql_delete.cc: mysql_delete(): Now we stop statement execution if one of triggers failed, we also don't execute after delete trigger if we failed to delete row from the table (We also pass information about which buffer contains old version of row to process_triggers()). multi_delete::send_data()/do_deletes(): Now we also invoke triggers in case of multi-delete. sql/sql_insert.cc: mysql_insert(): Moved invocation of before triggers to fill_record_n_invoke_before_triggers() method. After triggers are now executed as part of write_record(). (as nice side effect now we also stop statement execution if one of triggers fail). write_record(): Invoke after insert trigger after performing insert. Also invoke proper triggers if insert is converted to update or conflicting row is deleted. Cleaned up error handling a bit - no sense to report error via handler::print_error if it was not generated by handler method and was reported before. Also now we will execute after trigger only if we really have written row to the table. select_insert::send_data()/store_values(): We should also execute INSERT triggers for INSERT ... SELECT statement. sql/sql_load.cc: read_fixed_length()/read_sep_field(): We should execute INSERT triggers when processing LOAD DATA statement. Small cleanup in auto-increment related code. Also moved check for thd->killed which is used to abort LOAD DATA in case of problems in 'traditional' mode to better place.. sql/sql_trigger.cc: Since it turned out that at trigger loading time we can't say in which buffer TABLE::record[0] or record[1] old version of row will be stored we have to change our approach to binding of Item_trigger_field to Field instances. Now after trigger parsing (in Item_trigger_field::setup_table()) we only find index of proper Field in the TABLE::field array. Then before trigger is invoked we set Table_triggers_list::old_field/new_field so they point to arrays holding Field instances bound to buffers with proper row versions. And as last step in Item_trigger_field::fix_fields() we get pointer to Field from those arrays using saved field index. Table_triggers_list methods were changed to implement this approach (see also comments for sql_trigger.h). sql/sql_trigger.h: Since it turned out that at trigger loading time we can't say in which buffer TABLE::record[0] or record[1] old version of row will be stored we have to change our approach to binding of Item_trigger_field to Field instances. Now after trigger parsing (in Item_trigger_field::setup_table()) we only find index of proper Field in the TABLE::field array. Then before trigger is invoked we set Table_triggers_list::old_field/new_field so they point to arrays holding Field instances bound to buffers with proper row versions. And as last step in Item_trigger_field::fix_fields() we get pointer to Field from those arrays using saved field index. Changed Table_triggers_list to implement this new approach: - Added record1_field member to store array of Field objects bound to TABLE::record[1] buffer (instead of existing old_field member) - Added new_field member and changed meaning of old_field member. During trigger execution they should point to arrays of Field objects bound to buffers holding new and old versions of row respectively. - Added 'table' member to be able to get access to TABLE instance (for which this trigger list object was created) from process_triggers() method. - Now process_triggers() method sets old_field and new_field members properly before executing triggers body (basing on new old_row_is_record1 parameter value). - Renamed prepare_old_row_accessors_method() to prepare_record1_accessors() Also added has_before_update_triggers() method which allows to check whenever any before update triggers exist for table. sql/sql_update.cc: mysql_update(): Now we invoke before triggers in fill_record_n_invoke_before_triggers() method. Also now we abort statement execution when one of triggers fail. safe_update_on_fly(): When we are trying to understand if we can update first table in multi update on the fly we should take into account that BEFORE UPDATE trigger can change field values. multi_update::send_data()/do_updates() We should execute proper triggers when doing multi-update (in both cases when we do it on the fly and using temporary tables). --- sql/sql_insert.cc | 163 ++++++++++++++++++++++++++++++++++++++++-------------- 1 file changed, 123 insertions(+), 40 deletions(-) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index adb33af05b9..ce90b4ad3e0 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -398,7 +398,9 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, if (fields.elements || !value_count) { restore_record(table,s->default_values); // Get empty record - if (fill_record(thd, fields, *values, 0)) + if (fill_record_n_invoke_before_triggers(thd, fields, *values, 0, + table->triggers, + TRG_EVENT_INSERT)) { if (values_list.elements != 1 && !thd->net.report_error) { @@ -419,8 +421,17 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, if (thd->used_tables) // Column used in values() restore_record(table,s->default_values); // Get empty record else - table->record[0][0]= table->s->default_values[0]; // Fix delete marker - if (fill_record(thd, table->field, *values, 0)) + { + /* + Fix delete marker. No need to restore rest of record since it will + be overwritten by fill_record() anyway (and fill_record() does not + use default values in this case). + */ + table->record[0][0]= table->s->default_values[0]; + } + if (fill_record_n_invoke_before_triggers(thd, table->field, *values, 0, + table->triggers, + TRG_EVENT_INSERT)) { if (values_list.elements != 1 && ! thd->net.report_error) { @@ -432,14 +443,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, } } - /* - FIXME: Actually we should do this before - check_that_all_fields_are_given_values Or even go into write_record ? - */ - if (table->triggers) - table->triggers->process_triggers(thd, TRG_EVENT_INSERT, - TRG_ACTION_BEFORE); - if ((res= table_list->view_check_option(thd, (values_list.elements == 1 ? 0 : @@ -473,9 +476,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, if (error) break; thd->row_count++; - - if (table->triggers) - table->triggers->process_triggers(thd, TRG_EVENT_INSERT, TRG_ACTION_AFTER); } /* @@ -802,15 +802,35 @@ static int last_uniq_key(TABLE *table,uint keynr) /* - Write a record to table with optional deleting of conflicting records + Write a record to table with optional deleting of conflicting records, + invoke proper triggers if needed. + + SYNOPSIS + write_record() + thd - thread context + table - table to which record should be written + info - COPY_INFO structure describing handling of duplicates + and which is used for counting number of records inserted + and deleted. + + NOTE + Once this record will be written to table after insert trigger will + be invoked. If instead of inserting new record we will update old one + then both on update triggers will work instead. Similarly both on + delete triggers will be invoked if we will delete conflicting records. - Sets thd->no_trans_update if table which is updated didn't have transactions + Sets thd->no_trans_update if table which is updated didn't have + transactions. + + RETURN VALUE + 0 - success + non-0 - error */ int write_record(THD *thd, TABLE *table,COPY_INFO *info) { - int error; + int error, trg_error= 0; char *key=0; DBUG_ENTER("write_record"); @@ -881,25 +901,33 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) restore_record(table,record[1]); DBUG_ASSERT(info->update_fields->elements == info->update_values->elements); - if (fill_record(thd, *info->update_fields, *info->update_values, 0)) - goto err; + if (fill_record_n_invoke_before_triggers(thd, *info->update_fields, + *info->update_values, 0, + table->triggers, + TRG_EVENT_UPDATE)) + goto before_trg_err; /* CHECK OPTION for VIEW ... ON DUPLICATE KEY UPDATE ... */ if (info->view && (res= info->view->view_check_option(current_thd, info->ignore)) == VIEW_CHECK_SKIP) - break; + goto ok_or_after_trg_err; if (res == VIEW_CHECK_ERROR) - goto err; + goto before_trg_err; if ((error=table->file->update_row(table->record[1],table->record[0]))) { if ((error == HA_ERR_FOUND_DUPP_KEY) && info->ignore) - break; + goto ok_or_after_trg_err; goto err; } info->updated++; - break; + + trg_error= (table->triggers && + table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, + TRG_ACTION_AFTER, TRUE)); + info->copied++; + goto ok_or_after_trg_err; } else /* DUP_REPLACE */ { @@ -916,20 +944,48 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) (table->timestamp_field_type == TIMESTAMP_NO_AUTO_SET || table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH)) { + if (table->triggers && + table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, + TRG_ACTION_BEFORE, TRUE)) + goto before_trg_err; if ((error=table->file->update_row(table->record[1], table->record[0]))) goto err; info->deleted++; - break; /* Update logfile and count */ + trg_error= (table->triggers && + table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, + TRG_ACTION_AFTER, + TRUE)); + /* Update logfile and count */ + info->copied++; + goto ok_or_after_trg_err; + } + else + { + if (table->triggers && + table->triggers->process_triggers(thd, TRG_EVENT_DELETE, + TRG_ACTION_BEFORE, TRUE)) + goto before_trg_err; + if ((error=table->file->delete_row(table->record[1]))) + goto err; + info->deleted++; + if (!table->file->has_transactions()) + thd->no_trans_update= 1; + if (table->triggers && + table->triggers->process_triggers(thd, TRG_EVENT_DELETE, + TRG_ACTION_AFTER, TRUE)) + { + trg_error= 1; + goto ok_or_after_trg_err; + } + /* Let us attempt do write_row() once more */ } - else if ((error=table->file->delete_row(table->record[1]))) - goto err; - info->deleted++; - if (!table->file->has_transactions()) - thd->no_trans_update= 1; } } info->copied++; + trg_error= (table->triggers && + table->triggers->process_triggers(thd, TRG_EVENT_INSERT, + TRG_ACTION_AFTER, TRUE)); } else if ((error=table->file->write_row(table->record[0]))) { @@ -939,18 +995,27 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) table->file->restore_auto_increment(); } else + { info->copied++; + trg_error= (table->triggers && + table->triggers->process_triggers(thd, TRG_EVENT_INSERT, + TRG_ACTION_AFTER, TRUE)); + } + +ok_or_after_trg_err: if (key) my_safe_afree(key,table->s->max_unique_length,MAX_KEY_LENGTH); if (!table->file->has_transactions()) thd->no_trans_update= 1; - DBUG_RETURN(0); + DBUG_RETURN(trg_error); err: - if (key) - my_afree(key); info->last_errno= error; table->file->print_error(error,MYF(0)); + +before_trg_err: + if (key) + my_safe_afree(key, table->s->max_unique_length, MAX_KEY_LENGTH); DBUG_RETURN(1); } @@ -2013,12 +2078,27 @@ bool select_insert::send_data(List &values) DBUG_RETURN(1); } } - if (!(error= write_record(thd, table,&info)) && table->next_number_field) + if (!(error= write_record(thd, table, &info))) { - /* Clear for next record */ - table->next_number_field->reset(); - if (! last_insert_id && thd->insert_id_used) - last_insert_id=thd->insert_id(); + if (table->triggers) + { + /* + If triggers exist then whey can modify some fields which were not + originally touched by INSERT ... SELECT, so we have to restore + their original values for the next row. + */ + restore_record(table, s->default_values); + } + if (table->next_number_field) + { + /* + Clear auto-increment field for the next record, if triggers are used + we will clear it twice, but this should be cheap. + */ + table->next_number_field->reset(); + if (!last_insert_id && thd->insert_id_used) + last_insert_id= thd->insert_id(); + } } DBUG_RETURN(error); } @@ -2027,9 +2107,11 @@ bool select_insert::send_data(List &values) void select_insert::store_values(List &values) { if (fields->elements) - fill_record(thd, *fields, values, 1); + fill_record_n_invoke_before_triggers(thd, *fields, values, 1, + table->triggers, TRG_EVENT_INSERT); else - fill_record(thd, table->field, values, 1); + fill_record_n_invoke_before_triggers(thd, table->field, values, 1, + table->triggers, TRG_EVENT_INSERT); } void select_insert::send_error(uint errcode,const char *err) @@ -2172,7 +2254,8 @@ select_create::prepare(List &values, SELECT_LEX_UNIT *u) void select_create::store_values(List &values) { - fill_record(thd, field, values, 1); + fill_record_n_invoke_before_triggers(thd, field, values, 1, + table->triggers, TRG_EVENT_INSERT); } -- cgit v1.2.1 From c6283dbba9e0ec066e9fd8211e0d1629507f193e Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 25 May 2005 18:33:32 +0300 Subject: Cleanup's during review Added ASSERT() to detect wrongly packed fields sql/field.h: Fixed comments to right format sql/opt_range.cc: Merged code sql/sql_base.cc: Fixed indentation sql/sql_insert.cc: Fixed comments to right format sql/sql_select.cc: Simplify code sql/unireg.cc: Simply code for calculating key_buff_length Added ASSERT() to detect wrongly packed fields --- sql/sql_insert.cc | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 0bd9099ede1..ff07588c468 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -872,9 +872,10 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) if (info->handle_duplicates == DUP_UPDATE) { int res= 0; - /* we don't check for other UNIQUE keys - the first row - that matches, is updated. If update causes a conflict again, - an error is returned + /* + We don't check for other UNIQUE keys - the first row + that matches, is updated. If update causes a conflict again, + an error is returned */ DBUG_ASSERT(table->insert_values != NULL); store_record(table,insert_values); -- cgit v1.2.1 From 78422442df581b736862bd090ab3c18d92e7d324 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 30 May 2005 20:54:37 +0400 Subject: Preparatory (and the most problematic) patch for Bug#7306 "the server side preparedStatement error for LIMIT placeholder", which moves all uses of LIMIT clause from PREPARE to OPTIMIZE and later steps. After-review fixes. mysql-test/r/group_min_max.result: Test results fixed for EXPLAINs when using GROUP_MIN_MAX access plan. sql/item_subselect.cc: Move setting of the internal LIMIT used for IN/ALL/ANY/EXISTS subqueries to one place: Item_exists_subselect::fix_length_and_dec(). This implies that unit->select_limit_cnt is not set until the item is fixed. This is OK, as now LIMIT values are not used until JOIN::optimize. sql/mysql_priv.h: setup_tables no longer needs a special flag for the case when it's called from JOIN::reinit() (we don't need to call setup_tables between two executions of a correlated subquery). sql/opt_range.cc: Fix a glitch in GROUP_MIN_MAX access plan: we should use table metadata, not field data, to evaluate max_used_key_length, which is then used for explain. sql/sp.cc: - setup_tables signature changed. sql/sql_base.cc: - setup_tables no longer needs a special mode for subqueries. Unused checks were removed. sql/sql_delete.cc: - setup_tables signature changed sql/sql_help.cc: - setup_tables signature changed sql/sql_insert.cc: - setup_tables signature changed sql/sql_lex.cc: Consolidate setting of internal LIMIT for IN/ALL/ANY/EXISTS subqeries in one place, and hence remove it from st_select_lex::test_limit(). sql/sql_lex.h: Cleanup signature of st_select_lex_unit::init_prepare_fake_select_lex(). sql/sql_load.cc: - setup_tables signature changed sql/sql_olap.cc: - setup_tables signature changed sql/sql_parse.cc: - st_select_lex_unit::set_limit() signature changed sql/sql_select.cc: Move setting of JOIN::select_limit from JOIN::prepare to JOIN::optimize. At prepare, limit is unknown yet. Remove excessive cleanups from JOIN::reinit which were overwriting join->join_tab[i]->table->used_keys. This fixes the bug which was triggered by the change in item_subselect.cc. sql/sql_union.cc: Class st_select_lex_unit was changed to avoid calls to st_select_lex_unit::set_limit from places where it may be unknown. Now unit->select_limit_cnt is set at ::exec(). st_select_lex_unit::init_prepare_fake_select_lex(): - move out set_limit functionality - remove a few lines of dead code. st_select_lex_unit::prepare(): - now we don't call set_limit at the time of prepare, so the value of unit->select_limit_cnt may be unknown here. Use sl->select_limit instead. st_select_lex_unit::exec(): - cleanup - call set_limit explicitly as it has been moved out of init_prepare_fake_select_lex. sql/sql_update.cc: - setup_tables signature changed --- sql/sql_insert.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_insert.cc') diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 6db7e6a6b18..4727f071c39 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -699,7 +699,7 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list, DBUG_ENTER("mysql_prepare_insert_check_table"); if (setup_tables(thd, table_list, where, &thd->lex->select_lex.leaf_tables, - FALSE, select_insert)) + select_insert)) DBUG_RETURN(TRUE); if (insert_into_view && !fields.elements) -- cgit v1.2.1