summaryrefslogtreecommitdiff
path: root/sql/sql_update.cc
diff options
context:
space:
mode:
Diffstat (limited to 'sql/sql_update.cc')
-rw-r--r--sql/sql_update.cc860
1 files changed, 535 insertions, 325 deletions
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 7b1d5988bde..a86d1b57190 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -22,31 +22,92 @@
#include "mysql_priv.h"
#include "sql_select.h"
+#include "sp_head.h"
+#include "sql_trigger.h"
static bool safe_update_on_fly(JOIN_TAB *join_tab, List<Item> *fields);
/* Return 0 if row hasn't changed */
-static bool compare_record(TABLE *table, ulong query_id)
+static bool compare_record(TABLE *table, query_id_t query_id)
{
- if (!table->blob_fields)
+ if (table->s->blob_fields + table->s->varchar_fields == 0)
return cmp_record(table,record[1]);
/* Compare null bits */
if (memcmp(table->null_flags,
- table->null_flags+table->rec_buff_length,
- table->null_bytes))
- return 1; // Diff in NULL value
+ table->null_flags+table->s->rec_buff_length,
+ table->s->null_bytes))
+ return TRUE; // Diff in NULL value
/* Compare updated fields */
for (Field **ptr=table->field ; *ptr ; ptr++)
{
if ((*ptr)->query_id == query_id &&
- (*ptr)->cmp_binary_offset(table->rec_buff_length))
- return 1;
+ (*ptr)->cmp_binary_offset(table->s->rec_buff_length))
+ return TRUE;
}
- return 0;
+ return FALSE;
}
+/*
+ check that all fields are real fields
+
+ SYNOPSIS
+ check_fields()
+ thd thread handler
+ items Items for check
+
+ RETURN
+ TRUE Items can't be used in UPDATE
+ FALSE Items are OK
+*/
+
+static bool check_fields(THD *thd, List<Item> &items)
+{
+ List_iterator<Item> it(items);
+ Item *item;
+ Item_field *field;
+ Name_resolution_context *context= &thd->lex->select_lex.context;
+
+ while ((item= it++))
+ {
+ if (!(field= item->filed_for_view_update()))
+ {
+ /* item has name, because it comes from VIEW SELECT list */
+ my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), item->name);
+ return TRUE;
+ }
+ /*
+ we make temporary copy of Item_field, to avoid influence of changing
+ result_field on Item_ref which refer on this field
+ */
+ thd->change_item_tree(it.ref(), new Item_field(thd, field));
+ }
+ return FALSE;
+}
+
+
+/*
+ Process usual UPDATE
+
+ SYNOPSIS
+ mysql_update()
+ thd thread handler
+ fields fields for update
+ values values of fields for update
+ conds WHERE clause expression
+ order_num number of elemen in ORDER BY clause
+ order ORDER BY clause list
+ limit limit clause
+ handle_duplicates how to handle duplicates
+
+ RETURN
+ 0 - OK
+ 2 - privilege check and openning table passed, but we need to convert to
+ multi-update because of view substitution
+ 1 - error
+*/
+
int mysql_update(THD *thd,
TABLE_LIST *table_list,
List<Item> &fields,
@@ -54,46 +115,71 @@ int mysql_update(THD *thd,
COND *conds,
uint order_num, ORDER *order,
ha_rows limit,
- enum enum_duplicates handle_duplicates,
- bool ignore)
+ enum enum_duplicates handle_duplicates, bool ignore)
{
- bool using_limit=limit != HA_POS_ERROR;
+ bool using_limit= limit != HA_POS_ERROR;
bool safe_update= thd->options & OPTION_SAFE_UPDATES;
- bool used_key_is_modified, transactional_table, log_delayed;
- int error=0;
+ bool used_key_is_modified, transactional_table;
+ int res;
+ int error;
uint used_index= MAX_KEY;
bool need_sort= TRUE;
#ifndef NO_EMBEDDED_ACCESS_CHECKS
uint want_privilege;
#endif
- ulong query_id=thd->query_id, timestamp_query_id;
+ uint table_count= 0;
+ query_id_t query_id=thd->query_id, timestamp_query_id;
ha_rows updated, found;
key_map old_used_keys;
TABLE *table;
- SQL_SELECT *select= 0;
+ SQL_SELECT *select;
READ_RECORD info;
- TABLE_LIST *update_table_list= ((TABLE_LIST*)
- thd->lex->select_lex.table_list.first);
+ SELECT_LEX *select_lex= &thd->lex->select_lex;
+ bool need_reopen;
DBUG_ENTER("mysql_update");
LINT_INIT(timestamp_query_id);
- if ((open_and_lock_tables(thd, table_list)))
- DBUG_RETURN(-1);
+ for ( ; ; )
+ {
+ if (open_tables(thd, &table_list, &table_count, 0))
+ DBUG_RETURN(1);
+
+ if (table_list->multitable_view)
+ {
+ DBUG_ASSERT(table_list->view != 0);
+ DBUG_PRINT("info", ("Switch to multi-update"));
+ /* pass counter value */
+ thd->lex->table_count= table_count;
+ /* convert to multiupdate */
+ DBUG_RETURN(2);
+ }
+ if (!lock_tables(thd, table_list, table_count, &need_reopen))
+ break;
+ if (!need_reopen)
+ DBUG_RETURN(1);
+ close_tables_for_reopen(thd, table_list);
+ }
+
+ if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
+ (thd->fill_derived_tables() &&
+ mysql_handle_derived(thd->lex, &mysql_derived_filling)))
+ DBUG_RETURN(1);
+
thd->proc_info="init";
table= table_list->table;
table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
/* Calculate "table->used_keys" based on the WHERE */
- table->used_keys=table->keys_in_use;
+ table->used_keys= table->s->keys_in_use;
table->quick_keys.clear_all();
#ifndef NO_EMBEDDED_ACCESS_CHECKS
- want_privilege= table->grant.want_privilege;
+ /* TABLE_LIST contain right privilages request */
+ want_privilege= table_list->grant.want_privilege;
#endif
- if ((error= mysql_prepare_update(thd, table_list, update_table_list,
- &conds, order_num, order)))
- DBUG_RETURN(error);
+ if (mysql_prepare_update(thd, table_list, &conds, order_num, order))
+ DBUG_RETURN(1);
old_used_keys= table->used_keys; // Keys used in WHERE
/*
@@ -108,10 +194,20 @@ int mysql_update(THD *thd,
/* Check the fields we are going to modify */
#ifndef NO_EMBEDDED_ACCESS_CHECKS
- table->grant.want_privilege=want_privilege;
+ table_list->grant.want_privilege= table->grant.want_privilege= want_privilege;
+ table_list->register_want_access(want_privilege);
#endif
- if (setup_fields(thd, 0, update_table_list, fields, 1, 0, 0))
- DBUG_RETURN(-1); /* purecov: inspected */
+ if (setup_fields_with_no_wrap(thd, 0, fields, 1, 0, 0))
+ DBUG_RETURN(1); /* purecov: inspected */
+ if (table_list->view && check_fields(thd, fields))
+ {
+ DBUG_RETURN(1);
+ }
+ if (!table_list->updatable || check_key_in_view(thd, table_list))
+ {
+ my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE");
+ DBUG_RETURN(1);
+ }
if (table->timestamp_field)
{
// Don't set timestamp column if this is modified
@@ -123,12 +219,13 @@ int mysql_update(THD *thd,
#ifndef NO_EMBEDDED_ACCESS_CHECKS
/* Check values */
- table->grant.want_privilege=(SELECT_ACL & ~table->grant.privilege);
+ table_list->grant.want_privilege= table->grant.want_privilege=
+ (SELECT_ACL & ~table->grant.privilege);
#endif
- if (setup_fields(thd, 0, update_table_list, values, 1, 0, 0))
+ if (setup_fields(thd, 0, values, 1, 0, 0))
{
- free_underlaid_joins(thd, &thd->lex->select_lex);
- DBUG_RETURN(-1); /* purecov: inspected */
+ free_underlaid_joins(thd, select_lex);
+ DBUG_RETURN(1); /* purecov: inspected */
}
if (conds)
@@ -140,16 +237,15 @@ int mysql_update(THD *thd,
}
// Don't count on usage of 'only index' when calculating which key to use
table->used_keys.clear_all();
- if (limit)
- select=make_select(table,0,0,conds,&error);
+ select= make_select(table, 0, 0, conds, 0, &error);
if (error || !limit ||
(select && select->check_quick(thd, safe_update, limit)))
{
delete select;
- free_underlaid_joins(thd, &thd->lex->select_lex);
+ free_underlaid_joins(thd, select_lex);
if (error)
{
- DBUG_RETURN(-1); // Error in where
+ DBUG_RETURN(1); // Error in where
}
send_ok(thd); // No matching records
DBUG_RETURN(0);
@@ -170,14 +266,14 @@ int mysql_update(THD *thd,
goto err;
}
}
- init_ftfuncs(thd, &thd->lex->select_lex, 1);
-
+ init_ftfuncs(thd, select_lex, 1);
/* Check if we are modifying a key that we are used to search with */
+
if (select && select->quick)
{
- used_index=select->quick->index;
+ used_index= select->quick->index;
used_key_is_modified= (!select->quick->unique_key_range() &&
- check_if_key_used(table, used_index, fields));
+ select->quick->check_if_keys_used(&fields));
}
else
{
@@ -243,8 +339,11 @@ int mysql_update(THD *thd,
if (open_cached_file(&tempfile, mysql_tmpdir,TEMP_PREFIX,
DISK_BUFFER_SIZE, MYF(MY_WME)))
goto err;
-
- if (used_index == MAX_KEY)
+
+ /* If quick select is used, initialize it before retrieving rows. */
+ if (select && select->quick && select->quick->reset())
+ goto err;
+ if (used_index == MAX_KEY || (select && select->quick))
init_read_record(&info,thd,table,select,0,1);
else
init_read_record_idx(&info, thd, table, 1, used_index);
@@ -269,6 +368,8 @@ int mysql_update(THD *thd,
break;
}
}
+ else
+ table->file->unlock_row();
}
if (thd->killed && !error)
error= 1; // Aborted
@@ -304,6 +405,9 @@ int mysql_update(THD *thd,
if (ignore)
table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
+
+ if (select && select->quick && select->quick->reset())
+ goto err;
init_read_record(&info,thd,table,select,0,1);
updated= found= 0;
@@ -312,29 +416,67 @@ int mysql_update(THD *thd,
thd->proc_info="Updating";
query_id=thd->query_id;
+ transactional_table= table->file->has_transactions();
+ thd->no_trans_update= 0;
+ thd->abort_on_warning= test(!ignore &&
+ (thd->variables.sql_mode &
+ (MODE_STRICT_TRANS_TABLES |
+ MODE_STRICT_ALL_TABLES)));
+
while (!(error=info.read_record(&info)) && !thd->killed)
{
if (!(select && select->skip_record()))
{
store_record(table,record[1]);
- if (fill_record(fields,values, 0) || thd->net.report_error)
+ if (fill_record_n_invoke_before_triggers(thd, fields, values, 0,
+ table->triggers,
+ TRG_EVENT_UPDATE))
break; /* purecov: inspected */
+
found++;
+
if (compare_record(table, query_id))
{
+ if ((res= table_list->view_check_option(thd, ignore)) !=
+ VIEW_CHECK_OK)
+ {
+ found--;
+ if (res == VIEW_CHECK_SKIP)
+ continue;
+ else if (res == VIEW_CHECK_ERROR)
+ {
+ error= 1;
+ break;
+ }
+ }
if (!(error=table->file->update_row((byte*) table->record[1],
(byte*) table->record[0])))
{
updated++;
+ thd->no_trans_update= !transactional_table;
+
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+ TRG_ACTION_AFTER, TRUE))
+ {
+ error= 1;
+ break;
+ }
}
- else if (!ignore || error != HA_ERR_FOUND_DUPP_KEY)
+ else if (!ignore || error != HA_ERR_FOUND_DUPP_KEY)
{
- thd->fatal_error(); // Force error message
+ /*
+ If (ignore && error == HA_ERR_FOUND_DUPP_KEY) we don't have to
+ do anything; otherwise...
+ */
+ if (error != HA_ERR_FOUND_DUPP_KEY)
+ thd->fatal_error(); /* Other handler errors are fatal */
table->file->print_error(error,MYF(0));
error= 1;
break;
}
}
+
if (!--limit && using_limit)
{
error= -1; // Simulate end of file
@@ -358,26 +500,34 @@ int mysql_update(THD *thd,
This must be before binlog writing and ha_autocommit_...
*/
if (updated)
+ {
query_cache_invalidate3(thd, table_list, 1);
+ }
- transactional_table= table->file->has_transactions();
- log_delayed= (transactional_table || table->tmp_table);
- if ((updated || (error < 0)) && (error <= 0 || !transactional_table))
+ /*
+ error < 0 means really no error at all: we processed all rows until the
+ last one without error. error > 0 means an error (e.g. unique key
+ violation and no IGNORE or REPLACE). error == 0 is also an error (if
+ preparing the record or invoking before triggers fails). See
+ ha_autocommit_or_rollback(error>=0) and DBUG_RETURN(error>=0) below.
+ Sometimes we want to binlog even if we updated no rows, in case user used
+ it to be sure master and slave are in same state.
+ */
+ if ((error < 0) || (updated && !transactional_table))
{
- mysql_update_log.write(thd,thd->query,thd->query_length);
if (mysql_bin_log.is_open())
{
- if (error <= 0)
+ if (error < 0)
thd->clear_error();
Query_log_event qinfo(thd, thd->query, thd->query_length,
- log_delayed, FALSE);
+ transactional_table, FALSE);
if (mysql_bin_log.write(&qinfo) && transactional_table)
error=1; // Rollback update
}
- if (!log_delayed)
+ if (!transactional_table)
thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
}
- free_underlaid_joins(thd, &thd->lex->select_lex);
+ free_underlaid_joins(thd, select_lex);
if (transactional_table)
{
if (ha_autocommit_or_rollback(thd, error >= 0))
@@ -390,31 +540,32 @@ int mysql_update(THD *thd,
thd->lock=0;
}
- if (error >= 0)
- send_error(thd,thd->killed ? ER_SERVER_SHUTDOWN : 0); /* purecov: inspected */
- else
+ if (error < 0)
{
- char buff[80];
+ char buff[STRING_BUFFER_USUAL_SIZE];
sprintf(buff, ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated,
(ulong) thd->cuted_fields);
- send_ok(thd,
- (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated,
+ thd->row_count_func=
+ (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
+ send_ok(thd, (ulong) thd->row_count_func,
thd->insert_id_used ? thd->insert_id() : 0L,buff);
DBUG_PRINT("info",("%d records updated",updated));
}
thd->count_cuted_fields= CHECK_FIELD_IGNORE; /* calc cuted fields */
+ thd->abort_on_warning= 0;
free_io_cache(table);
- DBUG_RETURN(0);
+ DBUG_RETURN((error >= 0 || thd->net.report_error) ? 1 : 0);
err:
delete select;
- free_underlaid_joins(thd, &thd->lex->select_lex);
+ free_underlaid_joins(thd, select_lex);
if (table->key_read)
{
table->key_read=0;
table->file->extra(HA_EXTRA_NO_KEYREAD);
}
- DBUG_RETURN(-1);
+ thd->abort_on_warning= 0;
+ DBUG_RETURN(1);
}
/*
@@ -423,52 +574,57 @@ err:
SYNOPSIS
mysql_prepare_update()
thd - thread handler
- table_list - global table list
- update_table_list - local table list of UPDATE SELECT_LEX
+ table_list - global/local table list
conds - conditions
order_num - number of ORDER BY list entries
order - ORDER BY clause list
RETURN VALUE
- 0 - OK
- 1 - error (message is sent to user)
- -1 - error (message is not sent to user)
+ FALSE OK
+ TRUE error
*/
-int mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
- TABLE_LIST *update_table_list,
+bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
Item **conds, uint order_num, ORDER *order)
{
TABLE *table= table_list->table;
TABLE_LIST tables;
List<Item> all_fields;
+ SELECT_LEX *select_lex= &thd->lex->select_lex;
DBUG_ENTER("mysql_prepare_update");
#ifndef NO_EMBEDDED_ACCESS_CHECKS
- table->grant.want_privilege= (SELECT_ACL & ~table->grant.privilege);
+ table_list->grant.want_privilege= table->grant.want_privilege=
+ (SELECT_ACL & ~table->grant.privilege);
+ table_list->register_want_access(SELECT_ACL);
#endif
bzero((char*) &tables,sizeof(tables)); // For ORDER BY
tables.table= table;
tables.alias= table_list->alias;
- thd->allow_sum_func= 0;
-
- if (setup_tables(update_table_list) ||
- setup_conds(thd, update_table_list, conds) ||
- thd->lex->select_lex.setup_ref_array(thd, order_num) ||
- setup_order(thd, thd->lex->select_lex.ref_pointer_array,
- update_table_list, all_fields, all_fields, order) ||
- setup_ftfuncs(&thd->lex->select_lex))
- DBUG_RETURN(-1);
+ thd->lex->allow_sum_func= 0;
+
+ if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list,
+ table_list, conds, &select_lex->leaf_tables,
+ FALSE) ||
+ setup_conds(thd, table_list, select_lex->leaf_tables, conds) ||
+ select_lex->setup_ref_array(thd, order_num) ||
+ setup_order(thd, select_lex->ref_pointer_array,
+ table_list, all_fields, all_fields, order) ||
+ setup_ftfuncs(select_lex))
+ DBUG_RETURN(TRUE);
/* Check that we are not using table that we are updating in a sub select */
- if (find_real_table_in_list(table_list->next,
- table_list->db, table_list->real_name))
{
- my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->real_name);
- DBUG_RETURN(-1);
+ TABLE_LIST *duplicate;
+ if ((duplicate= unique_table(table_list, table_list->next_global)))
+ {
+ update_non_unique_table_error(table_list, "UPDATE", duplicate);
+ my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
+ DBUG_RETURN(TRUE);
+ }
}
-
- DBUG_RETURN(0);
+ select_lex->fix_prepare_information(thd, conds);
+ DBUG_RETURN(FALSE);
}
@@ -494,248 +650,263 @@ static table_map get_table_map(List<Item> *items)
/*
- Prepare tables for multi-update
- Analyse which tables need specific privileges and perform locking
- as required
+ make update specific preparation and checks after opening tables
+
+ SYNOPSIS
+ mysql_multi_update_prepare()
+ thd thread handler
+
+ RETURN
+ FALSE OK
+ TRUE Error
*/
-int mysql_multi_update_lock(THD *thd,
- TABLE_LIST *table_list,
- List<Item> *fields,
- SELECT_LEX *select_lex)
+bool mysql_multi_update_prepare(THD *thd)
{
- int res;
- TABLE_LIST *tl;
- TABLE_LIST *update_list= (TABLE_LIST*) thd->lex->select_lex.table_list.first;
+ LEX *lex= thd->lex;
+ TABLE_LIST *table_list= lex->query_tables;
+ TABLE_LIST *tl, *leaves;
+ List<Item> *fields= &lex->select_lex.item_list;
+ table_map tables_for_update;
+ bool update_view= 0;
+ /*
+ if this multi-update was converted from usual update, here is table
+ counter else junk will be assigned here, but then replaced with real
+ count in open_tables()
+ */
+ uint table_count= lex->table_count;
const bool using_lock_tables= thd->locked_tables != 0;
- bool initialized_dervied= 0;
- DBUG_ENTER("mysql_multi_update_lock");
+ bool original_multiupdate= (thd->lex->sql_command == SQLCOM_UPDATE_MULTI);
+ bool need_reopen= FALSE;
+ DBUG_ENTER("mysql_multi_update_prepare");
+
+ /* following need for prepared statements, to run next time multi-update */
+ thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
+
+reopen_tables:
+ /* open tables and create derived ones, but do not lock and fill them */
+ if (((original_multiupdate || need_reopen) &&
+ open_tables(thd, &table_list, &table_count, 0)) ||
+ mysql_handle_derived(lex, &mysql_derived_prepare))
+ DBUG_RETURN(TRUE);
/*
- The following loop is here to to ensure that we only lock tables
- that we are going to update with a write lock
+ setup_tables() need for VIEWs. JOIN::prepare() will call setup_tables()
+ second time, but this call will do nothing (there are check for second
+ call in setup_tables()).
*/
- for (;;)
- {
- table_map update_tables, derived_tables=0;
- uint tnr, table_count;
-
- if ((res=open_tables(thd, table_list, &table_count)))
- DBUG_RETURN(res);
- /* Only need to call lock_tables if we are not using LOCK TABLES */
- if (!using_lock_tables &&
- ((res= lock_tables(thd, table_list, table_count))))
- DBUG_RETURN(res);
+ if (setup_tables(thd, &lex->select_lex.context,
+ &lex->select_lex.top_join_list,
+ table_list, &lex->select_lex.where,
+ &lex->select_lex.leaf_tables, FALSE))
+ DBUG_RETURN(TRUE);
- if (!initialized_dervied)
- {
- initialized_dervied= 1;
- relink_tables_for_derived(thd);
- if ((res= mysql_handle_derived(thd->lex)))
- DBUG_RETURN(res);
- }
+ if (setup_fields_with_no_wrap(thd, 0, *fields, 1, 0, 0))
+ DBUG_RETURN(TRUE);
- /*
- Ensure that we have update privilege for all tables and columns in the
- SET part
- While we are here, initialize the table->map field to check which
- tables are updated and updatability of derived tables
- */
- for (tl= update_list, tnr=0 ; tl ; tl=tl->next)
+ for (tl= table_list; tl ; tl= tl->next_local)
+ {
+ if (tl->view)
{
- TABLE *table= tl->table;
- /*
- Update of derived tables is checked later
- We don't check privileges here, becasue then we would get error
- "UPDATE command denided .. for column N" instead of
- "Target table ... is not updatable"
- */
- if (!tl->derived)
- table->grant.want_privilege= (UPDATE_ACL & ~table->grant.privilege);
- table->map= (table_map) 1 << (tnr++);
+ update_view= 1;
+ break;
}
+ }
- if (setup_fields(thd, 0, update_list, *fields, 1, 0, 0))
- DBUG_RETURN(-1);
+ if (update_view && check_fields(thd, *fields))
+ {
+ DBUG_RETURN(TRUE);
+ }
- update_tables= get_table_map(fields);
+ tables_for_update= get_table_map(fields);
- /* Unlock the tables in preparation for relocking */
- if (!using_lock_tables)
- {
- mysql_unlock_tables(thd, thd->lock);
- thd->lock= 0;
- }
+ /*
+ Setup timestamp handling and locking mode
+ */
+ leaves= lex->select_lex.leaf_tables;
+ for (tl= leaves; tl; tl= tl->next_leaf)
+ {
+ TABLE *table= tl->table;
+ /* Only set timestamp column if this is not modified */
+ if (table->timestamp_field &&
+ table->timestamp_field->query_id == thd->query_id)
+ table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
- /*
- Count tables and setup timestamp handling
- Set also the table locking strategy according to the update map
- */
- for (tl= update_list; tl; tl= tl->next)
+ /* if table will be updated then check that it is unique */
+ if (table->map & tables_for_update)
{
- TABLE_LIST *save= tl->next;
- TABLE *table= tl->table;
- uint wants;
- /* if table will be updated then check that it is unique */
- if (table->map & update_tables)
- {
- /*
- Multi-update can't be constructed over-union => we always have
- single SELECT on top and have to check underlaying SELECTs of it
- */
- if (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= thd->lex->multi_lock_option;
- tl->updating= 1; // loacal or only list
- if (tl->table_list)
- tl->table_list->updating= 1; // global list (if we have 2 lists)
- wants= UPDATE_ACL;
- }
- else
+ if (!tl->updatable || check_key_in_view(thd, tl))
{
- DBUG_PRINT("info",("setting table `%s` for read-only", tl->alias));
- // If we are using the binary log, we need TL_READ_NO_INSERT to get
- // correct order of statements. Otherwise, we use a TL_READ lock to
- // improve performance.
- tl->lock_type= using_update_log ? TL_READ_NO_INSERT : TL_READ;
- tl->updating= 0; // loacal or only list
- if (tl->table_list)
- tl->table_list->updating= 0; // global list (if we have 2 lists)
- wants= SELECT_ACL;
+ my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE");
+ DBUG_RETURN(TRUE);
}
- if (tl->derived)
- derived_tables|= table->map;
- else
- {
- tl->next= 0;
- if (!using_lock_tables)
- tl->table->reginfo.lock_type= tl->lock_type;
- if (check_access(thd, wants, tl->db, &tl->grant.privilege, 0, 0) ||
- (grant_option && check_grant(thd, wants, tl, 0, 0, 0)))
- {
- tl->next= save;
- DBUG_RETURN(1);
- }
- tl->next= save;
- }
+ DBUG_PRINT("info",("setting table `%s` for update", tl->alias));
+ /*
+ If table will be updated we should not downgrade lock for it and
+ leave it as is.
+ */
}
+ else
+ {
+ DBUG_PRINT("info",("setting table `%s` for read-only", tl->alias));
+ /*
+ If we are using the binary log, we need TL_READ_NO_INSERT to get
+ correct order of statements. Otherwise, we use a TL_READ lock to
+ improve performance.
+ */
+ tl->lock_type= using_update_log ? TL_READ_NO_INSERT : TL_READ;
+ tl->updating= 0;
+ /* Update TABLE::lock_type accordingly. */
+ if (!tl->placeholder() && !tl->schema_table && !using_lock_tables)
+ tl->table->reginfo.lock_type= tl->lock_type;
+ }
+ }
+ for (tl= table_list; tl; tl= tl->next_local)
+ {
+ /* Check access privileges for table */
+ if (!tl->derived)
+ {
+ uint want_privilege= tl->updating ? UPDATE_ACL : SELECT_ACL;
+ if (check_access(thd, want_privilege,
+ tl->db, &tl->grant.privilege, 0, 0,
+ test(tl->schema_table)) ||
+ (grant_option && check_grant(thd, want_privilege, tl, 0, 1, 0)))
+ DBUG_RETURN(TRUE);
+ }
+ }
- if (thd->lex->derived_tables && (update_tables & derived_tables))
+ /* check single table update for view compound from several tables */
+ for (tl= table_list; tl; tl= tl->next_local)
+ {
+ if (tl->effective_algorithm == VIEW_ALGORITHM_MERGE)
{
- // find derived table which cause error
- for (tl= update_list; tl; tl= tl->next)
+ TABLE_LIST *for_update= 0;
+ if (tl->check_single_table(&for_update, tables_for_update, tl))
{
- if (tl->derived && (update_tables & tl->table->map))
- {
- my_printf_error(ER_NON_UPDATABLE_TABLE, ER(ER_NON_UPDATABLE_TABLE),
- MYF(0), tl->alias, "UPDATE");
- DBUG_RETURN(-1);
- }
+ my_error(ER_VIEW_MULTIUPDATE, MYF(0),
+ tl->view_db.str, tl->view_name.str);
+ DBUG_RETURN(-1);
}
}
+ }
- /* Relock the tables with the correct modes */
- res= lock_tables(thd, table_list, table_count);
- if (using_lock_tables)
- break; // Don't have to do setup_field()
+ /* now lock and fill tables */
+ if (lock_tables(thd, table_list, table_count, &need_reopen))
+ {
+ if (!need_reopen)
+ DBUG_RETURN(TRUE);
/*
- We must setup fields again as the file may have been reopened
- during lock_tables
+ We have to reopen tables since some of them were altered or dropped
+ during lock_tables() or something was done with their triggers.
+ Let us do some cleanups to be able do setup_table() and setup_fields()
+ once again.
*/
- {
- List_iterator_fast<Item> field_it(*fields);
- Item_field *item;
+ List_iterator_fast<Item> it(*fields);
+ Item *item;
+ while ((item= it++))
+ item->cleanup();
+
+ /* We have to cleanup translation tables of views. */
+ for (TABLE_LIST *tbl= table_list; tbl; tbl= tbl->next_global)
+ tbl->cleanup_items();
+
+ close_tables_for_reopen(thd, table_list);
+ goto reopen_tables;
+ }
- while ((item= (Item_field *) field_it++))
+ /*
+ Check that we are not using table that we are updating, but we should
+ skip all tables of UPDATE SELECT itself
+ */
+ lex->select_lex.exclude_from_table_unique_test= TRUE;
+ /* We only need SELECT privilege for columns in the values list */
+ for (tl= leaves; tl; tl= tl->next_leaf)
+ {
+ TABLE *table= tl->table;
+ TABLE_LIST *tlist;
+ if (!(tlist= tl->top_table())->derived)
+ {
+ tlist->grant.want_privilege=
+ (SELECT_ACL & ~tlist->grant.privilege);
+ table->grant.want_privilege= (SELECT_ACL & ~table->grant.privilege);
+ }
+ DBUG_PRINT("info", ("table: %s want_privilege: %u", tl->alias,
+ (uint) table->grant.want_privilege));
+ if (tl->lock_type != TL_READ &&
+ tl->lock_type != TL_READ_NO_INSERT)
+ {
+ TABLE_LIST *duplicate;
+ if ((duplicate= unique_table(tl, table_list)))
{
- item->field->query_id= 0;
- item->cleanup();
+ update_non_unique_table_error(table_list, "UPDATE", duplicate);
+ DBUG_RETURN(TRUE);
}
}
- if (setup_fields(thd, 0, update_list, *fields, 1, 0, 0))
- DBUG_RETURN(-1);
- /*
- If lock succeded and the table map didn't change since the above lock
- we can continue.
- */
- if (!res && update_tables == get_table_map(fields))
- break;
-
- /*
- There was some very unexpected changes in the table definition between
- open tables and lock tables. Close tables and try again.
- */
- close_thread_tables(thd);
}
-
- DBUG_RETURN(res);
+
+ if (thd->fill_derived_tables() &&
+ mysql_handle_derived(lex, &mysql_derived_filling))
+ DBUG_RETURN(TRUE);
+
+ DBUG_RETURN (FALSE);
}
+
/*
Setup multi-update handling and call SELECT to do the join
*/
-int mysql_multi_update(THD *thd,
- TABLE_LIST *table_list,
- List<Item> *fields,
- List<Item> *values,
- COND *conds,
- ulong options,
- enum enum_duplicates handle_duplicates, bool ignore,
- SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex)
+bool mysql_multi_update(THD *thd,
+ TABLE_LIST *table_list,
+ List<Item> *fields,
+ List<Item> *values,
+ COND *conds,
+ ulonglong options,
+ enum enum_duplicates handle_duplicates, bool ignore,
+ SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex)
{
- int res;
- TABLE_LIST *tl;
- TABLE_LIST *update_list= (TABLE_LIST*) thd->lex->select_lex.table_list.first;
- List<Item> total_list;
multi_update *result;
DBUG_ENTER("mysql_multi_update");
- /* Setup timestamp handling */
- for (tl= update_list; tl; tl= tl->next)
- {
- TABLE *table= tl->table;
- /* Only set timestamp column if this is not modified */
- if (table->timestamp_field &&
- table->timestamp_field->query_id == thd->query_id)
- table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
+ if (!(result= new multi_update(table_list,
+ thd->lex->select_lex.leaf_tables,
+ fields, values,
+ handle_duplicates, ignore)))
+ DBUG_RETURN(TRUE);
- /* We only need SELECT privilege for columns in the values list */
- table->grant.want_privilege= (SELECT_ACL & ~table->grant.privilege);
- }
+ thd->no_trans_update= 0;
+ thd->abort_on_warning= test(thd->variables.sql_mode &
+ (MODE_STRICT_TRANS_TABLES |
+ MODE_STRICT_ALL_TABLES));
- if (!(result=new multi_update(thd, update_list, fields, values,
- handle_duplicates, ignore)))
- DBUG_RETURN(-1);
-
- res= mysql_select(thd, &select_lex->ref_pointer_array,
- select_lex->get_table_list(), select_lex->with_wild,
- total_list,
- conds, 0, (ORDER *) NULL, (ORDER *)NULL, (Item *) NULL,
- (ORDER *)NULL,
- options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK,
- result, unit, select_lex);
+ List<Item> total_list;
+ (void) mysql_select(thd, &select_lex->ref_pointer_array,
+ table_list, select_lex->with_wild,
+ total_list,
+ conds, 0, (ORDER *) NULL, (ORDER *)NULL, (Item *) NULL,
+ (ORDER *)NULL,
+ options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
+ OPTION_SETUP_TABLES_DONE,
+ result, unit, select_lex);
delete result;
- DBUG_RETURN(res);
+ thd->abort_on_warning= 0;
+ DBUG_RETURN(FALSE);
}
-multi_update::multi_update(THD *thd_arg, TABLE_LIST *table_list,
+multi_update::multi_update(TABLE_LIST *table_list,
+ TABLE_LIST *leaves_list,
List<Item> *field_list, List<Item> *value_list,
- enum enum_duplicates handle_duplicates_arg, bool ignore_arg)
- :all_tables(table_list), update_tables(0), thd(thd_arg), tmp_tables(0),
- updated(0), found(0), fields(field_list), values(value_list),
- table_count(0), copy_field(0), handle_duplicates(handle_duplicates_arg),
- do_update(1), trans_safe(0), transactional_tables(1), ignore(ignore_arg)
+ enum enum_duplicates handle_duplicates_arg,
+ bool ignore_arg)
+ :all_tables(table_list), leaves(leaves_list), update_tables(0),
+ tmp_tables(0), updated(0), found(0), fields(field_list),
+ values(value_list), table_count(0), copy_field(0),
+ handle_duplicates(handle_duplicates_arg), do_update(1), trans_safe(0),
+ transactional_tables(1), ignore(ignore_arg)
{}
@@ -763,7 +934,7 @@ int multi_update::prepare(List<Item> &not_used_values,
if (!tables_to_update)
{
- my_error(ER_NO_TABLES_USED, MYF(0));
+ my_message(ER_NO_TABLES_USED, ER(ER_NO_TABLES_USED), MYF(0));
DBUG_RETURN(1);
}
@@ -772,7 +943,7 @@ int multi_update::prepare(List<Item> &not_used_values,
reference tables
*/
- if (setup_fields(thd, 0, all_tables, *values, 1, 0, 0))
+ if (setup_fields(thd, 0, *values, 1, 0, 0))
DBUG_RETURN(1);
/*
@@ -782,8 +953,9 @@ int multi_update::prepare(List<Item> &not_used_values,
*/
update.empty();
- for (table_ref= all_tables; table_ref; table_ref=table_ref->next)
+ for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf)
{
+ /* TODO: add support of view of join support */
TABLE *table=table_ref->table;
if (tables_to_update & table->map)
{
@@ -791,7 +963,7 @@ int multi_update::prepare(List<Item> &not_used_values,
sizeof(*tl));
if (!tl)
DBUG_RETURN(1);
- update.link_in_list((byte*) tl, (byte**) &tl->next);
+ update.link_in_list((byte*) tl, (byte**) &tl->next_local);
tl->shared= table_count++;
table->no_keyread=1;
table->used_keys.clear_all();
@@ -803,7 +975,7 @@ int multi_update::prepare(List<Item> &not_used_values,
table_count= update.elements;
update_tables= (TABLE_LIST*) update.first;
- tmp_tables = (TABLE **) thd->calloc(sizeof(TABLE *) * table_count);
+ tmp_tables = (TABLE**) thd->calloc(sizeof(TABLE *) * table_count);
tmp_table_param = (TMP_TABLE_PARAM*) thd->calloc(sizeof(TMP_TABLE_PARAM) *
table_count);
fields_for_table= (List_item **) thd->alloc(sizeof(List_item *) *
@@ -850,12 +1022,12 @@ int multi_update::prepare(List<Item> &not_used_values,
which will cause an error when reading a row.
(This issue is mostly relevent for MyISAM tables)
*/
- for (table_ref= all_tables; table_ref; table_ref=table_ref->next)
+ for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf)
{
TABLE *table=table_ref->table;
- if (!(tables_to_update & table->map) &&
- find_real_table_in_list(update_tables, table_ref->db,
- table_ref->real_name))
+ if (!(tables_to_update & table->map) &&
+ find_table_in_local_list(update_tables, table_ref->db,
+ table_ref->table_name))
table->no_cache= 1; // Disable row cache
}
DBUG_RETURN(thd->is_fatal_error != 0);
@@ -881,11 +1053,10 @@ multi_update::initialize_tables(JOIN *join)
DBUG_RETURN(1);
main_table=join->join_tab->table;
trans_safe= transactional_tables= main_table->file->has_transactions();
- log_delayed= trans_safe || main_table->tmp_table != NO_TMP_TABLE;
table_to_update= 0;
/* Create a temporary table for keys to all tables, except main table */
- for (table_ref= update_tables; table_ref; table_ref=table_ref->next)
+ for (table_ref= update_tables; table_ref; table_ref= table_ref->next_local)
{
TABLE *table=table_ref->table;
uint cnt= table_ref->shared;
@@ -951,8 +1122,8 @@ multi_update::initialize_tables(JOIN *join)
NOTES
We can update the first table in join on the fly if we know that
- a row in this tabel will never be read twice. This is true under
- the folloing conditions:
+ a row in this table will never be read twice. This is true under
+ the following conditions:
- We are doing a table scan and the data is in a separate file (MyISAM) or
if we don't update a clustered key.
@@ -960,6 +1131,10 @@ multi_update::initialize_tables(JOIN *join)
- We are doing a range scan and we don't update the scan key or
the primary key for a clustered table handler.
+ When checking for above cases we also should take into account that
+ BEFORE UPDATE trigger potentially may change value of any field in row
+ being updated.
+
WARNING
This code is a bit dependent of how make_join_readinfo() works.
@@ -975,30 +1150,36 @@ static bool safe_update_on_fly(JOIN_TAB *join_tab, List<Item> *fields)
case JT_SYSTEM:
case JT_CONST:
case JT_EQ_REF:
- return 1; // At most one matching row
+ return TRUE; // At most one matching row
case JT_REF:
- return !check_if_key_used(table, join_tab->ref.key, *fields);
+ case JT_REF_OR_NULL:
+ return !check_if_key_used(table, join_tab->ref.key, *fields) &&
+ !(table->triggers &&
+ table->triggers->has_before_update_triggers());
case JT_ALL:
/* If range search on index */
if (join_tab->quick)
- return !check_if_key_used(table, join_tab->quick->index,
- *fields);
+ return !join_tab->quick->check_if_keys_used(fields) &&
+ !(table->triggers &&
+ table->triggers->has_before_update_triggers());
/* If scanning in clustered key */
if ((table->file->table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) &&
- table->primary_key < MAX_KEY)
- return !check_if_key_used(table, table->primary_key, *fields);
- return 1;
+ table->s->primary_key < MAX_KEY)
+ return !check_if_key_used(table, table->s->primary_key, *fields) &&
+ !(table->triggers &&
+ table->triggers->has_before_update_triggers());
+ return TRUE;
default:
break; // Avoid compler warning
}
- return 0;
+ return FALSE;
}
multi_update::~multi_update()
{
TABLE_LIST *table;
- for (table= update_tables ; table; table= table->next)
+ for (table= update_tables ; table; table= table->next_local)
table->table->no_keyread= table->table->no_cache= 0;
if (tmp_tables)
@@ -1025,7 +1206,7 @@ bool multi_update::send_data(List<Item> &not_used_values)
TABLE_LIST *cur_table;
DBUG_ENTER("multi_update::send_data");
- for (cur_table= update_tables; cur_table ; cur_table= cur_table->next)
+ for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local)
{
TABLE *table= cur_table->table;
/*
@@ -1049,12 +1230,25 @@ bool multi_update::send_data(List<Item> &not_used_values)
{
table->status|= STATUS_UPDATED;
store_record(table,record[1]);
- if (fill_record(*fields_for_table[offset], *values_for_table[offset], 0))
+ if (fill_record_n_invoke_before_triggers(thd, *fields_for_table[offset],
+ *values_for_table[offset], 0,
+ table->triggers,
+ TRG_EVENT_UPDATE))
DBUG_RETURN(1);
+
found++;
if (compare_record(table, thd->query_id))
{
int error;
+ if ((error= cur_table->view_check_option(thd, ignore)) !=
+ VIEW_CHECK_OK)
+ {
+ found--;
+ if (error == VIEW_CHECK_SKIP)
+ continue;
+ else if (error == VIEW_CHECK_ERROR)
+ DBUG_RETURN(1);
+ }
if (!updated++)
{
/*
@@ -1068,20 +1262,34 @@ bool multi_update::send_data(List<Item> &not_used_values)
table->record[0])))
{
updated--;
- if (!ignore || error != HA_ERR_FOUND_DUPP_KEY)
+ if (!ignore || error != HA_ERR_FOUND_DUPP_KEY)
{
- thd->fatal_error(); // Force error message
+ /*
+ If (ignore && error == HA_ERR_FOUND_DUPP_KEY) we don't have to
+ do anything; otherwise...
+ */
+ if (error != HA_ERR_FOUND_DUPP_KEY)
+ thd->fatal_error(); /* Other handler errors are fatal */
table->file->print_error(error,MYF(0));
DBUG_RETURN(1);
}
}
+ else
+ {
+ if (!table->file->has_transactions())
+ thd->no_trans_update= 1;
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+ TRG_ACTION_AFTER, TRUE))
+ DBUG_RETURN(1);
+ }
}
}
else
{
int error;
TABLE *tmp_table= tmp_tables[offset];
- fill_record(tmp_table->field+1, *values_for_table[offset], 1);
+ fill_record(thd, tmp_table->field+1, *values_for_table[offset], 1);
/* Store pointer to row */
memcpy((char*) tmp_table->field[0]->ptr,
(char*) table->file->ref, table->file->ref_length);
@@ -1108,7 +1316,7 @@ bool multi_update::send_data(List<Item> &not_used_values)
void multi_update::send_error(uint errcode,const char *err)
{
/* First send error what ever it is ... */
- ::send_error(thd,errcode,err);
+ my_error(errcode, MYF(0), err);
/* If nothing updated return */
if (!updated)
@@ -1143,7 +1351,7 @@ int multi_update::do_updates(bool from_send_error)
do_update= 0; // Don't retry this function
if (!found)
DBUG_RETURN(0);
- for (cur_table= update_tables; cur_table ; cur_table= cur_table->next)
+ for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local)
{
byte *ref_pos;
@@ -1196,6 +1404,11 @@ int multi_update::do_updates(bool from_send_error)
copy_field_ptr++)
(*copy_field_ptr->do_copy)(copy_field_ptr);
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+ TRG_ACTION_BEFORE, TRUE))
+ goto err2;
+
if (compare_record(table, thd->query_id))
{
if ((local_error=table->file->update_row(table->record[1],
@@ -1205,17 +1418,18 @@ int multi_update::do_updates(bool from_send_error)
goto err;
}
updated++;
- if (table->tmp_table != NO_TMP_TABLE)
- log_delayed= 1;
+
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+ TRG_ACTION_AFTER, TRUE))
+ goto err2;
}
}
if (updated != org_updated)
{
- if (table->tmp_table != NO_TMP_TABLE)
- log_delayed= 1; // Tmp tables forces delay log
if (table->file->has_transactions())
- log_delayed= transactional_tables= 1;
+ transactional_tables= 1;
else
trans_safe= 0; // Can't do safe rollback
}
@@ -1231,15 +1445,14 @@ err:
table->file->print_error(local_error,MYF(0));
}
+err2:
(void) table->file->ha_rnd_end();
(void) tmp_table->file->ha_rnd_end();
if (updated != org_updated)
{
- if (table->tmp_table != NO_TMP_TABLE)
- log_delayed= 1;
if (table->file->has_transactions())
- log_delayed= transactional_tables= 1;
+ transactional_tables= 1;
else
trans_safe= 0;
}
@@ -1251,7 +1464,7 @@ err:
bool multi_update::send_eof()
{
- char buff[80];
+ char buff[STRING_BUFFER_USUAL_SIZE];
thd->proc_info="updating reference tables";
/* Does updates for the last n - 1 tables, returns 0 if ok */
@@ -1269,24 +1482,21 @@ bool multi_update::send_eof()
/*
Write the SQL statement to the binlog if we updated
rows and we succeeded or if we updated some non
- transacational tables.
- Note that if we updated nothing we don't write to the binlog (TODO:
- fix this).
+ transactional tables.
*/
- if (updated && (local_error <= 0 || !trans_safe))
+ if ((local_error == 0) || (updated && !trans_safe))
{
- mysql_update_log.write(thd,thd->query,thd->query_length);
if (mysql_bin_log.is_open())
{
- if (local_error <= 0)
+ if (local_error == 0)
thd->clear_error();
Query_log_event qinfo(thd, thd->query, thd->query_length,
- log_delayed, FALSE);
+ transactional_tables, FALSE);
if (mysql_bin_log.write(&qinfo) && trans_safe)
local_error= 1; // Rollback update
}
- if (!log_delayed)
+ if (!transactional_tables)
thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
}
@@ -1301,15 +1511,15 @@ bool multi_update::send_eof()
/* Safety: If we haven't got an error before (should not happen) */
my_message(ER_UNKNOWN_ERROR, "An error occured in multi-table update",
MYF(0));
- ::send_error(thd);
- return 1;
+ return TRUE;
}
sprintf(buff, ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated,
(ulong) thd->cuted_fields);
- ::send_ok(thd,
- (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated,
+ thd->row_count_func=
+ (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
+ ::send_ok(thd, (ulong) thd->row_count_func,
thd->insert_id_used ? thd->insert_id() : 0L,buff);
- return 0;
+ return FALSE;
}
View Lorry Controller Status