1
/* Copyright (C) 2000-2006 MySQL AB
3
This program is free software; you can redistribute it and/or modify
4
it under the terms of the GNU General Public License as published by
5
the Free Software Foundation; version 2 of the License.
7
This program is distributed in the hope that it will be useful,
8
but WITHOUT ANY WARRANTY; without even the implied warranty of
9
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10
GNU General Public License for more details.
12
You should have received a copy of the GNU General Public License
13
along with this program; if not, write to the Free Software
14
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
18
Single table and multi table updates of tables.
19
Multi-table updates were introduced by Sinisa & Monty
22
#include "drizzled/sql_select.h"
23
#include "drizzled/error.h"
24
#include "drizzled/probes.h"
25
#include "drizzled/sql_base.h"
26
#include "drizzled/field/timestamp.h"
27
#include "drizzled/sql_parse.h"
28
#include "drizzled/optimizer/range.h"
29
#include "drizzled/records.h"
30
#include "drizzled/internal/my_sys.h"
31
#include "drizzled/internal/iocache.h"
41
Re-read record if more columns are needed for error message.
43
If we got a duplicate key error, we want to write an error
44
message containing the value of the duplicate key. If we do not have
45
all fields of the key value in record[0], we need to re-read the
46
record with a proper read_set.
48
@param[in] error error number
49
@param[in] table table
52
static void prepare_record_for_error_message(int error, Table *table)
57
MyBitmap unique_map; /* Fields in offended unique. */
58
my_bitmap_map unique_map_buf[bitmap_buffer_size(MAX_FIELDS)];
61
Only duplicate key errors print the key value.
62
If storage engine does always read all columns, we have the value alraedy.
64
if ((error != HA_ERR_FOUND_DUPP_KEY) ||
65
!(table->cursor->getEngine()->check_flag(HTON_BIT_PARTIAL_COLUMN_READ)))
69
Get the number of the offended index.
70
We will see MAX_KEY if the engine cannot determine the affected index.
72
if ((keynr= table->get_dup_key(error)) >= MAX_KEY)
75
/* Create unique_map with all fields used by that index. */
76
unique_map.init(unique_map_buf, table->s->fields);
77
table->mark_columns_used_by_index_no_reset(keynr, &unique_map);
79
/* Subtract read_set and write_set. */
80
bitmap_subtract(&unique_map, table->read_set);
81
bitmap_subtract(&unique_map, table->write_set);
84
If the unique index uses columns that are neither in read_set
85
nor in write_set, we must re-read the record.
86
Otherwise no need to do anything.
88
if (unique_map.isClearAll())
91
/* Get identifier of last read record into table->cursor->ref. */
92
table->cursor->position(table->record[0]);
93
/* Add all fields used by unique index to read_set. */
94
bitmap_union(table->read_set, &unique_map);
95
/* Read record that is identified by table->cursor->ref. */
96
(void) table->cursor->rnd_pos(table->record[1], table->cursor->ref);
97
/* Copy the newly read columns into the new record. */
98
for (field_p= table->field; (field= *field_p); field_p++)
99
if (unique_map.isBitSet(field->field_index))
100
field->copy_from_tmp(table->s->rec_buff_length);
111
session thread handler
112
fields fields for update
113
values values of fields for update
114
conds WHERE clause expression
115
order_num number of elemen in ORDER BY clause
116
order order_st BY clause list
118
handle_duplicates how to handle duplicates
125
int mysql_update(Session *session, TableList *table_list,
126
List<Item> &fields, List<Item> &values, COND *conds,
127
uint32_t order_num, order_st *order,
128
ha_rows limit, enum enum_duplicates,
131
bool using_limit= limit != HA_POS_ERROR;
132
bool used_key_is_modified;
133
bool transactional_table;
134
bool can_compare_record;
136
uint used_index= MAX_KEY, dup_key_found;
137
bool need_sort= true;
138
ha_rows updated, found;
139
key_map old_covering_keys;
141
optimizer::SqlSelect *select= NULL;
143
Select_Lex *select_lex= &session->lex->select_lex;
145
List<Item> all_fields;
146
Session::killed_state killed_status= Session::NOT_KILLED;
148
DRIZZLE_UPDATE_START(session->query.c_str());
149
if (session->openTablesLock(table_list))
151
DRIZZLE_UPDATE_DONE(1, 0, 0);
155
session->set_proc_info("init");
156
table= table_list->table;
158
/* Calculate "table->covering_keys" based on the WHERE */
159
table->covering_keys= table->s->keys_in_use;
160
table->quick_keys.reset();
162
if (mysql_prepare_update(session, table_list, &conds, order_num, order))
165
old_covering_keys= table->covering_keys; // Keys used in WHERE
166
/* Check the fields we are going to modify */
167
if (setup_fields_with_no_wrap(session, 0, fields, MARK_COLUMNS_WRITE, 0, 0))
169
if (table->timestamp_field)
171
// Don't set timestamp column if this is modified
172
if (table->timestamp_field->isWriteSet())
173
table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
176
if (table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_UPDATE ||
177
table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH)
178
table->setWriteSet(table->timestamp_field->field_index);
182
if (setup_fields(session, 0, values, MARK_COLUMNS_READ, 0, 0))
184
free_underlaid_joins(session, select_lex);
188
if (select_lex->inner_refs_list.elements &&
189
fix_inner_refs(session, all_fields, select_lex, select_lex->ref_pointer_array))
191
DRIZZLE_UPDATE_DONE(1, 0, 0);
197
Item::cond_result cond_value;
198
conds= remove_eq_conds(session, conds, &cond_value);
199
if (cond_value == Item::COND_FALSE)
200
limit= 0; // Impossible WHERE
204
If a timestamp field settable on UPDATE is present then to avoid wrong
205
update force the table handler to retrieve write-only fields to be able
206
to compare records and detect data change.
208
if (table->cursor->getEngine()->check_flag(HTON_BIT_PARTIAL_COLUMN_READ) &&
209
table->timestamp_field &&
210
(table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_UPDATE ||
211
table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH))
212
bitmap_union(table->read_set, table->write_set);
213
// Don't count on usage of 'only index' when calculating which key to use
214
table->covering_keys.reset();
216
/* Update the table->cursor->stats.records number */
217
table->cursor->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
219
select= optimizer::make_select(table, 0, 0, conds, 0, &error);
220
if (error || !limit ||
221
(select && select->check_quick(session, false, limit)))
225
* Resetting the Diagnostic area to prevent
228
session->main_da.reset_diagnostics_area();
229
free_underlaid_joins(session, select_lex);
231
goto abort; // Error in where
232
DRIZZLE_UPDATE_DONE(0, 0, 0);
233
session->my_ok(); // No matching records
236
if (!select && limit != HA_POS_ERROR)
238
if ((used_index= optimizer::get_index_for_order(table, order, limit)) != MAX_KEY)
241
/* If running in safe sql mode, don't allow updates without keys */
242
if (table->quick_keys.none())
244
session->server_status|=SERVER_QUERY_NO_INDEX_USED;
247
table->mark_columns_needed_for_update();
249
/* Check if we are modifying a key that we are used to search with */
251
if (select && select->quick)
253
used_index= select->quick->index;
254
used_key_is_modified= (!select->quick->unique_key_range() &&
255
select->quick->is_keys_used(table->write_set));
259
used_key_is_modified= 0;
260
if (used_index == MAX_KEY) // no index for sort order
261
used_index= table->cursor->key_used_on_scan;
262
if (used_index != MAX_KEY)
263
used_key_is_modified= is_key_used(table, used_index, table->write_set);
267
if (used_key_is_modified || order)
270
We can't update table directly; We must first search after all
271
matching rows before updating the table!
273
if (used_index < MAX_KEY && old_covering_keys.test(used_index))
276
table->mark_columns_used_by_index(used_index);
280
table->use_all_columns();
283
/* note: We avoid sorting avoid if we sort on the used index */
284
if (order && (need_sort || used_key_is_modified))
287
Doing an order_st BY; Let filesort find and sort the rows we are going
289
NOTE: filesort will call table->prepare_for_position()
292
SORT_FIELD *sortorder;
293
ha_rows examined_rows;
295
table->sort.io_cache = new internal::IO_CACHE;
296
memset(table->sort.io_cache, 0, sizeof(internal::IO_CACHE));
298
if (!(sortorder=make_unireg_sortorder(order, &length, NULL)) ||
299
(table->sort.found_records= filesort(session, table, sortorder, length,
307
Filesort has already found and selected the rows we want to update,
308
so we don't need the where clause
316
We are doing a search on a key that is updated. In this case
317
we go trough the matching rows, save a pointer to them and
318
update these in a separate loop based on the pointer.
321
internal::IO_CACHE tempfile;
322
if (open_cached_file(&tempfile, drizzle_tmpdir,TEMP_PREFIX,
323
DISK_BUFFER_SIZE, MYF(MY_WME)))
326
/* If quick select is used, initialize it before retrieving rows. */
327
if (select && select->quick && select->quick->reset())
329
table->cursor->try_semi_consistent_read(1);
332
When we get here, we have one of the following options:
333
A. used_index == MAX_KEY
334
This means we should use full table scan, and start it with
335
init_read_record call
336
B. used_index != MAX_KEY
337
B.1 quick select is used, start the scan with init_read_record
338
B.2 quick select is not used, this is full index scan (with LIMIT)
339
Full index scan must be started with init_read_record_idx
342
if (used_index == MAX_KEY || (select && select->quick))
343
init_read_record(&info,session,table,select,0,1);
345
init_read_record_idx(&info, session, table, 1, used_index);
347
session->set_proc_info("Searching rows for update");
348
ha_rows tmp_limit= limit;
350
while (!(error=info.read_record(&info)) && !session->killed)
352
if (!(select && select->skip_record()))
354
if (table->cursor->was_semi_consistent_read())
355
continue; /* repeat the read of the same row if it still exists */
357
table->cursor->position(table->record[0]);
358
if (my_b_write(&tempfile,table->cursor->ref,
359
table->cursor->ref_length))
364
if (!--limit && using_limit)
371
table->cursor->unlock_row();
373
if (session->killed && !error)
376
table->cursor->try_semi_consistent_read(0);
377
end_read_record(&info);
379
/* Change select to use tempfile */
382
delete select->quick;
383
if (select->free_cond)
390
select= new optimizer::SqlSelect;
393
if (reinit_io_cache(&tempfile,internal::READ_CACHE,0L,0,0))
395
// Read row ptrs from this cursor
396
memcpy(select->file, &tempfile, sizeof(tempfile));
401
table->restore_column_maps_after_mark_index();
405
table->cursor->extra(HA_EXTRA_IGNORE_DUP_KEY);
407
if (select && select->quick && select->quick->reset())
409
table->cursor->try_semi_consistent_read(1);
410
init_read_record(&info,session,table,select,0,1);
414
* Per the SQL standard, inserting NULL into a NOT NULL
415
* field requires an error to be thrown.
419
* NULL check and handling occurs in field_conv.cc
421
session->count_cuted_fields= CHECK_FIELD_ERROR_FOR_NULL;
422
session->cuted_fields= 0L;
423
session->set_proc_info("Updating");
425
transactional_table= table->cursor->has_transactions();
426
session->abort_on_warning= test(!ignore);
429
Assure that we can use position()
430
if we need to create an error message.
432
if (table->cursor->getEngine()->check_flag(HTON_BIT_PARTIAL_COLUMN_READ))
433
table->prepare_for_position();
436
We can use compare_record() to optimize away updates if
437
the table handler is returning all columns OR if
438
if all updated columns are read
440
can_compare_record= (!(table->cursor->getEngine()->check_flag(HTON_BIT_PARTIAL_COLUMN_READ)) ||
441
bitmap_is_subset(table->write_set, table->read_set));
443
while (!(error=info.read_record(&info)) && !session->killed)
445
if (!(select && select->skip_record()))
447
if (table->cursor->was_semi_consistent_read())
448
continue; /* repeat the read of the same row if it still exists */
450
table->storeRecord();
451
if (fill_record(session, fields, values))
456
if (!can_compare_record || table->compare_record())
458
/* Non-batched update */
459
error= table->cursor->ha_update_row(table->record[1],
461
if (!error || error == HA_ERR_RECORD_IS_THE_SAME)
463
if (error != HA_ERR_RECORD_IS_THE_SAME)
469
table->cursor->is_fatal_error(error, HA_CHECK_DUP_KEY))
472
If (ignore && error is ignorable) we don't have to
473
do anything; otherwise...
477
if (table->cursor->is_fatal_error(error, HA_CHECK_DUP_KEY))
478
flags|= ME_FATALERROR; /* Other handler errors are fatal */
480
prepare_record_for_error_message(error, table);
481
table->print_error(error,MYF(flags));
487
if (!--limit && using_limit)
489
error= -1; // Simulate end of cursor
494
table->cursor->unlock_row();
495
session->row_count++;
499
Caching the killed status to pass as the arg to query event constuctor;
500
The cached value can not change whereas the killed status can
501
(externally) since this point and change of the latter won't affect
503
It's assumed that if an error was set in combination with an effective
504
killed status then the error is due to killing.
506
killed_status= session->killed; // get the status of the volatile
507
// simulated killing after the loop must be ineffective for binlogging
508
error= (killed_status == Session::NOT_KILLED)? error : 1;
510
updated-= dup_key_found;
511
table->cursor->try_semi_consistent_read(0);
513
if (!transactional_table && updated > 0)
514
session->transaction.stmt.markModifiedNonTransData();
516
end_read_record(&info);
518
session->set_proc_info("end");
519
table->cursor->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
522
error < 0 means really no error at all: we processed all rows until the
523
last one without error. error > 0 means an error (e.g. unique key
524
violation and no IGNORE or REPLACE). error == 0 is also an error (if
525
preparing the record or invoking before triggers fails). See
526
ha_autocommit_or_rollback(error>=0) and return(error>=0) below.
527
Sometimes we want to binlog even if we updated no rows, in case user used
528
it to be sure master and slave are in same state.
530
if ((error < 0) || session->transaction.stmt.hasModifiedNonTransData())
532
if (session->transaction.stmt.hasModifiedNonTransData())
533
session->transaction.all.markModifiedNonTransData();
535
assert(transactional_table || !updated || session->transaction.stmt.hasModifiedNonTransData());
536
free_underlaid_joins(session, select_lex);
538
/* If LAST_INSERT_ID(X) was used, report X */
539
id= session->arg_of_last_insert_id_function ?
540
session->first_successful_insert_id_in_prev_stmt : 0;
544
char buff[STRING_BUFFER_USUAL_SIZE];
545
sprintf(buff, ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated,
546
(ulong) session->cuted_fields);
547
session->row_count_func= updated;
549
* Resetting the Diagnostic area to prevent
552
session->main_da.reset_diagnostics_area();
553
session->my_ok((ulong) session->row_count_func, found, id, buff);
555
session->count_cuted_fields= CHECK_FIELD_IGNORE; /* calc cuted fields */
556
session->abort_on_warning= 0;
557
DRIZZLE_UPDATE_DONE((error >= 0 || session->is_error()), found, updated);
558
return ((error >= 0 || session->is_error()) ? 1 : 0);
562
free_underlaid_joins(session, select_lex);
566
table->cursor->extra(HA_EXTRA_NO_KEYREAD);
568
session->abort_on_warning= 0;
571
DRIZZLE_UPDATE_DONE(1, 0, 0);
576
Prepare items in UPDATE statement
579
mysql_prepare_update()
580
session - thread handler
581
table_list - global/local table list
583
order_num - number of ORDER BY list entries
584
order - ORDER BY clause list
590
bool mysql_prepare_update(Session *session, TableList *table_list,
591
Item **conds, uint32_t order_num, order_st *order)
593
List<Item> all_fields;
594
Select_Lex *select_lex= &session->lex->select_lex;
596
session->lex->allow_sum_func= 0;
598
if (setup_tables_and_check_access(session, &select_lex->context,
599
&select_lex->top_join_list,
601
&select_lex->leaf_tables,
603
session->setup_conds(table_list, conds) ||
604
select_lex->setup_ref_array(session, order_num) ||
605
setup_order(session, select_lex->ref_pointer_array,
606
table_list, all_fields, all_fields, order))
609
/* Check that we are not using table that we are updating in a sub select */
611
TableList *duplicate;
612
if ((duplicate= unique_table(table_list, table_list->next_global)))
614
my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
622
} /* namespace drizzled */