1
/* Copyright (C) 2000 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; either version 2 of the License, or
6
(at your option) any later version.
8
This program is distributed in the hope that it will be useful,
9
but WITHOUT ANY WARRANTY; without even the implied warranty of
10
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11
GNU General Public License for more details.
13
You should have received a copy of the GNU General Public License
14
along with this program; if not, write to the Free Software
15
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
18
Delete of records and truncate of tables.
20
Multi-table deletes were introduced by Monty and Sinisa
23
#include "mysql_priv.h"
24
#include "ha_innodb.h"
25
#include "sql_select.h"
27
#include "sql_trigger.h"
29
bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
30
SQL_LIST *order, ha_rows limit, ulonglong options,
31
bool reset_auto_increment)
37
bool using_limit=limit != HA_POS_ERROR;
38
bool transactional_table, safe_update, const_cond;
40
uint usable_index= MAX_KEY;
41
SELECT_LEX *select_lex= &thd->lex->select_lex;
42
DBUG_ENTER("mysql_delete");
44
if (open_and_lock_tables(thd, table_list))
46
if (!(table= table_list->table))
48
my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0),
49
table_list->view_db.str, table_list->view_name.str);
52
error= table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
55
table->file->print_error(error, MYF(0));
58
thd->proc_info="init";
61
if (mysql_prepare_delete(thd, table_list, &conds))
64
const_cond= (!conds || conds->const_item());
65
safe_update=test(thd->options & OPTION_SAFE_UPDATES);
66
if (safe_update && const_cond)
68
my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
69
ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
73
select_lex->no_error= thd->lex->ignore;
76
Test if the user wants to delete all rows and deletion doesn't have
77
any side-effects (because of triggers), so we can use optimized
78
handler::delete_all_rows() method.
80
if (!using_limit && const_cond && (!conds || conds->val_int()) &&
81
!(specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)) &&
82
!(table->triggers && table->triggers->has_delete_triggers()))
84
deleted= table->file->records;
85
if (!(error=table->file->delete_all_rows()))
90
if (error != HA_ERR_WRONG_COMMAND)
92
table->file->print_error(error,MYF(0));
96
/* Handler didn't support fast delete; Delete rows one by one */
101
Item::cond_result result;
102
conds= remove_eq_conds(thd, conds, &result);
103
if (result == Item::COND_FALSE) // Impossible where
107
table->used_keys.clear_all();
108
table->quick_keys.clear_all(); // Can't use 'only index'
109
select=make_select(table, 0, 0, conds, 0, &error);
112
if ((select && select->check_quick(thd, safe_update, limit)) || !limit)
115
free_underlaid_joins(thd, select_lex);
116
thd->row_count_func= 0;
120
We don't need to call reset_auto_increment in this case, because
121
mysql_truncate always gives a NULL conds argument, hence we never
125
DBUG_RETURN(0); // Nothing to delete
128
/* If running in safe sql mode, don't allow updates without keys */
129
if (table->quick_keys.is_clear_all())
131
thd->server_status|=SERVER_QUERY_NO_INDEX_USED;
132
if (safe_update && !using_limit)
135
free_underlaid_joins(thd, select_lex);
136
my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
137
ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
141
if (options & OPTION_QUICK)
142
(void) table->file->extra(HA_EXTRA_QUICK);
144
if (order && order->elements)
147
SORT_FIELD *sortorder;
150
List<Item> all_fields;
151
ha_rows examined_rows;
153
bzero((char*) &tables,sizeof(tables));
154
tables.table = table;
155
tables.alias = table_list->alias;
157
if (select_lex->setup_ref_array(thd, order->elements) ||
158
setup_order(thd, select_lex->ref_pointer_array, &tables,
159
fields, all_fields, (ORDER*) order->first))
162
free_underlaid_joins(thd, &thd->lex->select_lex);
166
if (!select && limit != HA_POS_ERROR)
167
usable_index= get_index_for_order(table, (ORDER*)(order->first), limit);
169
if (usable_index == MAX_KEY)
171
table->sort.io_cache= (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
172
MYF(MY_FAE | MY_ZEROFILL));
174
if (!(sortorder= make_unireg_sortorder((ORDER*) order->first,
176
(table->sort.found_records = filesort(thd, table, sortorder, length,
177
select, HA_POS_ERROR,
182
free_underlaid_joins(thd, &thd->lex->select_lex);
186
Filesort has already found and selected the rows we want to delete,
187
so we don't need the where clause
190
free_underlaid_joins(thd, select_lex);
195
/* If quick select is used, initialize it before retrieving rows. */
196
if (select && select->quick && select->quick->reset())
199
free_underlaid_joins(thd, select_lex);
202
if (usable_index==MAX_KEY)
203
init_read_record(&info,thd,table,select,1,1);
205
init_read_record_idx(&info, thd, table, 1, usable_index);
208
init_ftfuncs(thd, select_lex, 1);
209
thd->proc_info="updating";
212
table->triggers->mark_fields_used(thd, TRG_EVENT_DELETE);
214
while (!(error=info.read_record(&info)) && !thd->killed &&
215
!thd->net.report_error)
217
// thd->net.report_error is tested to disallow delete row on error
218
if (!(select && select->skip_record())&& !thd->net.report_error )
221
if (table->triggers &&
222
table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
223
TRG_ACTION_BEFORE, FALSE))
229
if (!(error=table->file->delete_row(table->record[0])))
232
if (table->triggers &&
233
table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
234
TRG_ACTION_AFTER, FALSE))
239
if (!--limit && using_limit)
247
table->file->print_error(error,MYF(0));
249
In < 4.0.14 we set the error number to 0 here, but that
250
was not sensible, because then MySQL would not roll back the
251
failed DELETE, and also wrote it to the binlog. For MyISAM
252
tables a DELETE probably never should fail (?), but for
253
InnoDB it can fail in a FOREIGN KEY error or an
254
out-of-tablespace error.
261
table->file->unlock_row(); // Row failed selection, release lock on it
263
if (thd->killed && !error)
265
thd->proc_info="end";
266
end_read_record(&info);
267
free_io_cache(table); // Will not do any harm
268
if (options & OPTION_QUICK)
269
(void) table->file->extra(HA_EXTRA_NORMAL);
271
if (reset_auto_increment && (error < 0))
274
We're really doing a truncate and need to reset the table's
275
auto-increment counter.
277
int error2= table->file->reset_auto_increment(0);
279
if (error2 && (error2 != HA_ERR_WRONG_COMMAND))
281
table->file->print_error(error2, MYF(0));
288
Invalidate the table in the query cache if something changed. This must
289
be before binlog writing and ha_autocommit_...
293
query_cache_invalidate3(thd, table_list, 1);
297
transactional_table= table->file->has_transactions();
298
/* See similar binlogging code in sql_update.cc, for comments */
299
if ((error < 0) || (deleted && !transactional_table))
301
if (mysql_bin_log.is_open())
305
Query_log_event qinfo(thd, thd->query, thd->query_length,
306
transactional_table, FALSE);
307
if (mysql_bin_log.write(&qinfo) && transactional_table)
310
if (!transactional_table)
311
thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
313
free_underlaid_joins(thd, select_lex);
314
if (transactional_table)
316
if (ha_autocommit_or_rollback(thd,error >= 0))
322
mysql_unlock_tables(thd, thd->lock);
325
if (error < 0 || (thd->lex->ignore && !thd->is_fatal_error))
327
thd->row_count_func= deleted;
328
send_ok(thd,deleted);
329
DBUG_PRINT("info",("%ld records deleted",(long) deleted));
331
DBUG_RETURN(error >= 0 || thd->net.report_error);
336
Prepare items in DELETE statement
339
mysql_prepare_delete()
341
table_list - global/local table list
348
bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds)
351
SELECT_LEX *select_lex= &thd->lex->select_lex;
352
DBUG_ENTER("mysql_prepare_delete");
354
thd->lex->allow_sum_func= 0;
355
if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context,
356
&thd->lex->select_lex.top_join_list,
358
&select_lex->leaf_tables, FALSE,
359
DELETE_ACL, SELECT_ACL) ||
360
setup_conds(thd, table_list, select_lex->leaf_tables, conds) ||
361
setup_ftfuncs(select_lex))
363
if (!table_list->updatable || check_key_in_view(thd, table_list))
365
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "DELETE");
369
TABLE_LIST *duplicate;
370
if ((duplicate= unique_table(thd, table_list, table_list->next_global)))
372
update_non_unique_table_error(table_list, "DELETE", duplicate);
376
select_lex->fix_prepare_information(thd, conds, &fake_conds);
381
/***************************************************************************
382
Delete multiple tables from join
383
***************************************************************************/
385
#define MEM_STRIP_BUF_SIZE current_thd->variables.sortbuff_size
387
extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b)
389
handler *file= (handler*)arg;
390
return file->cmp_ref((const byte*)a, (const byte*)b);
394
make delete specific preparation and checks after opening tables
397
mysql_multi_delete_prepare()
405
bool mysql_multi_delete_prepare(THD *thd)
408
TABLE_LIST *aux_tables= (TABLE_LIST *)lex->auxiliary_table_list.first;
409
TABLE_LIST *target_tbl;
410
DBUG_ENTER("mysql_multi_delete_prepare");
413
setup_tables() need for VIEWs. JOIN::prepare() will not do it second
416
lex->query_tables also point on local list of DELETE SELECT_LEX
418
if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context,
419
&thd->lex->select_lex.top_join_list,
420
lex->query_tables, &lex->select_lex.where,
421
&lex->select_lex.leaf_tables, FALSE,
422
DELETE_ACL, SELECT_ACL))
427
Multi-delete can't be constructed over-union => we always have
428
single SELECT on top and have to check underlying SELECTs of it
430
lex->select_lex.exclude_from_table_unique_test= TRUE;
431
/* Fix tables-to-be-deleted-from list to point at opened tables */
432
for (target_tbl= (TABLE_LIST*) aux_tables;
434
target_tbl= target_tbl->next_local)
436
if (!(target_tbl->table= target_tbl->correspondent_table->table))
438
DBUG_ASSERT(target_tbl->correspondent_table->view &&
439
target_tbl->correspondent_table->merge_underlying_list &&
440
target_tbl->correspondent_table->merge_underlying_list->
442
my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0),
443
target_tbl->correspondent_table->view_db.str,
444
target_tbl->correspondent_table->view_name.str);
448
if (!target_tbl->correspondent_table->updatable ||
449
check_key_in_view(thd, target_tbl->correspondent_table))
451
my_error(ER_NON_UPDATABLE_TABLE, MYF(0),
452
target_tbl->table_name, "DELETE");
456
Check that table from which we delete is not used somewhere
457
inside subqueries/view.
460
TABLE_LIST *duplicate;
461
if ((duplicate= unique_table(thd, target_tbl->correspondent_table,
464
update_non_unique_table_error(target_tbl->correspondent_table,
465
"DELETE", duplicate);
474
multi_delete::multi_delete(TABLE_LIST *dt, uint num_of_tables_arg)
475
: delete_tables(dt), deleted(0), found(0),
476
num_of_tables(num_of_tables_arg), error(0),
477
do_delete(0), transactional_tables(0), normal_tables(0)
479
tempfiles= (Unique **) sql_calloc(sizeof(Unique *) * num_of_tables);
484
multi_delete::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
486
DBUG_ENTER("multi_delete::prepare");
489
thd->proc_info="deleting from main table";
495
multi_delete::initialize_tables(JOIN *join)
498
Unique **tempfiles_ptr;
499
DBUG_ENTER("initialize_tables");
501
if ((thd->options & OPTION_SAFE_UPDATES) && error_if_full_join(join))
504
table_map tables_to_delete_from=0;
505
for (walk= delete_tables; walk; walk= walk->next_local)
506
tables_to_delete_from|= walk->table->map;
509
delete_while_scanning= 1;
510
for (JOIN_TAB *tab=join->join_tab, *end=join->join_tab+join->tables;
514
if (tab->table->map & tables_to_delete_from)
516
/* We are going to delete from this table */
517
TABLE *tbl=walk->table=tab->table;
518
walk= walk->next_local;
519
/* Don't use KEYREAD optimization on this table */
521
/* Don't use record cache */
523
tbl->used_keys.clear_all();
524
if (tbl->file->has_transactions())
525
transactional_tables= 1;
529
tbl->triggers->mark_fields_used(thd, TRG_EVENT_DELETE);
531
else if ((tab->type != JT_SYSTEM && tab->type != JT_CONST) &&
532
walk == delete_tables)
535
We are not deleting from the table we are scanning. In this
536
case send_data() shouldn't delete any rows a we may touch
537
the rows in the deleted table many times
539
delete_while_scanning= 0;
543
tempfiles_ptr= tempfiles;
544
if (delete_while_scanning)
546
table_being_deleted= delete_tables;
547
walk= walk->next_local;
549
for (;walk ;walk= walk->next_local)
551
TABLE *table=walk->table;
552
*tempfiles_ptr++= new Unique (refpos_order_cmp,
553
(void *) table->file,
554
table->file->ref_length,
557
init_ftfuncs(thd, thd->lex->current_select, 1);
558
DBUG_RETURN(thd->is_fatal_error != 0);
562
multi_delete::~multi_delete()
564
for (table_being_deleted= delete_tables;
566
table_being_deleted= table_being_deleted->next_local)
568
TABLE *table= table_being_deleted->table;
569
free_io_cache(table); // Alloced by unique
573
for (uint counter= 0; counter < num_of_tables; counter++)
575
if (tempfiles[counter])
576
delete tempfiles[counter];
581
bool multi_delete::send_data(List<Item> &values)
583
int secure_counter= delete_while_scanning ? -1 : 0;
584
TABLE_LIST *del_table;
585
DBUG_ENTER("multi_delete::send_data");
587
for (del_table= delete_tables;
589
del_table= del_table->next_local, secure_counter++)
591
TABLE *table= del_table->table;
593
/* Check if we are using outer join and we didn't find the row */
594
if (table->status & (STATUS_NULL_ROW | STATUS_DELETED))
597
table->file->position(table->record[0]);
600
if (secure_counter < 0)
602
/* We are scanning the current table */
603
DBUG_ASSERT(del_table == table_being_deleted);
604
if (table->triggers &&
605
table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
606
TRG_ACTION_BEFORE, FALSE))
608
table->status|= STATUS_DELETED;
609
if (!(error=table->file->delete_row(table->record[0])))
612
if (table->triggers &&
613
table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
614
TRG_ACTION_AFTER, FALSE))
619
table->file->print_error(error,MYF(0));
625
error=tempfiles[secure_counter]->unique_add((char*) table->file->ref);
628
error= 1; // Fatal error
637
void multi_delete::send_error(uint errcode,const char *err)
639
DBUG_ENTER("multi_delete::send_error");
641
/* First send error what ever it is ... */
642
my_message(errcode, err, MYF(0));
644
/* If nothing deleted return */
648
/* Something already deleted so we have to invalidate cache */
649
query_cache_invalidate3(thd, delete_tables, 1);
652
If rows from the first table only has been deleted and it is
653
transactional, just do rollback.
654
The same if all tables are transactional, regardless of where we are.
655
In all other cases do attempt deletes ...
657
if ((table_being_deleted == delete_tables &&
658
table_being_deleted->table->file->has_transactions()) ||
660
ha_rollback_stmt(thd);
664
We have to execute the recorded do_deletes() and write info into the
675
Do delete from other tables.
681
int multi_delete::do_deletes()
683
int local_error= 0, counter= 0;
684
DBUG_ENTER("do_deletes");
685
DBUG_ASSERT(do_delete);
687
do_delete= 0; // Mark called
691
table_being_deleted= (delete_while_scanning ? delete_tables->next_local :
694
for (; table_being_deleted;
695
table_being_deleted= table_being_deleted->next_local, counter++)
697
TABLE *table = table_being_deleted->table;
698
if (tempfiles[counter]->get(table))
705
init_read_record(&info,thd,table,NULL,0,1);
707
Ignore any rows not found in reference tables as they may already have
708
been deleted by foreign key handling
710
info.ignore_not_found_rows= 1;
711
while (!(local_error=info.read_record(&info)) && !thd->killed)
713
if (table->triggers &&
714
table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
715
TRG_ACTION_BEFORE, FALSE))
720
if ((local_error=table->file->delete_row(table->record[0])))
722
table->file->print_error(local_error,MYF(0));
726
if (table->triggers &&
727
table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
728
TRG_ACTION_AFTER, FALSE))
734
end_read_record(&info);
735
if (thd->killed && !local_error)
737
if (local_error == -1) // End of file
740
DBUG_RETURN(local_error);
745
Send ok to the client
751
bool multi_delete::send_eof()
753
thd->proc_info="deleting from reference tables";
755
/* Does deletes for the last n - 1 tables, returns 0 if ok */
756
int local_error= do_deletes(); // returns 0 if success
758
/* compute a total error to know if something failed */
759
local_error= local_error || error;
761
/* reset used flags */
762
thd->proc_info="end";
765
We must invalidate the query cache before binlog writing and
770
query_cache_invalidate3(thd, delete_tables, 1);
773
if ((local_error == 0) || (deleted && normal_tables))
775
if (mysql_bin_log.is_open())
777
if (local_error == 0)
779
Query_log_event qinfo(thd, thd->query, thd->query_length,
780
transactional_tables, FALSE);
781
if (mysql_bin_log.write(&qinfo) && !normal_tables)
782
local_error=1; // Log write failed: roll back the SQL statement
784
if (!transactional_tables)
785
thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
787
/* Commit or rollback the current SQL statement */
788
if (transactional_tables)
789
if (ha_autocommit_or_rollback(thd,local_error > 0))
794
thd->row_count_func= deleted;
795
::send_ok(thd, deleted);
801
/***************************************************************************
803
****************************************************************************/
806
Optimize delete of all rows by doing a full generate of the table
807
This will work even if the .ISM and .ISD tables are destroyed
809
dont_send_ok should be set if:
810
- We should always wants to generate the table (even if the table type
811
normally can't safely do this.
812
- We don't want an ok to be sent to the end user.
813
- We don't want to log the truncate command
814
- If we want to have a name lock on the table on exit without errors.
817
bool mysql_truncate(THD *thd, TABLE_LIST *table_list, bool dont_send_ok)
819
HA_CREATE_INFO create_info;
820
char path[FN_REFLEN];
823
DBUG_ENTER("mysql_truncate");
825
bzero((char*) &create_info,sizeof(create_info));
826
/* If it is a temporary table, close and regenerate it */
827
if (!dont_send_ok && (table_ptr=find_temporary_table(thd,table_list->db,
828
table_list->table_name)))
830
TABLE *table= *table_ptr;
831
table->file->info(HA_STATUS_AUTO | HA_STATUS_NO_LOCK);
832
db_type table_type= table->s->db_type;
833
if (!ha_check_storage_engine_flag(table_type, HTON_CAN_RECREATE))
835
strmov(path, table->s->path);
836
*table_ptr= table->next; // Unlink table from list
837
close_temporary(table,0);
838
if (thd->slave_thread)
839
--slave_open_temp_tables;
840
*fn_ext(path)=0; // Remove the .frm extension
841
ha_create_table(path, &create_info,1);
842
// We don't need to call invalidate() because this table is not in cache
843
if ((error= (int) !(open_temporary_table(thd, path, table_list->db,
844
table_list->table_name, 1))))
845
(void) rm_temporary_table(table_type, path);
847
If we return here we will not have logged the truncation to the bin log
848
and we will not send_ok() to the client.
853
(void) sprintf(path,"%s/%s/%s%s",mysql_data_home,table_list->db,
854
table_list->table_name,reg_ext);
855
fn_format(path, path, "", "", MY_UNPACK_FILENAME);
860
mysql_frm_type(thd, path, &table_type);
861
if (table_type == DB_TYPE_UNKNOWN)
863
my_error(ER_NO_SUCH_TABLE, MYF(0),
864
table_list->db, table_list->table_name);
867
if (!ha_check_storage_engine_flag(table_type, HTON_CAN_RECREATE))
869
if (lock_and_wait_for_table_name(thd, table_list))
873
*fn_ext(path)=0; // Remove the .frm extension
874
error= ha_create_table(path,&create_info,1);
875
query_cache_invalidate3(thd, table_list, 0);
882
if (mysql_bin_log.is_open())
885
Query_log_event qinfo(thd, thd->query, thd->query_length,
887
mysql_bin_log.write(&qinfo);
889
send_ok(thd); // This should return record count
891
VOID(pthread_mutex_lock(&LOCK_open));
892
unlock_table_name(thd, table_list);
893
VOID(pthread_mutex_unlock(&LOCK_open));
897
VOID(pthread_mutex_lock(&LOCK_open));
898
unlock_table_name(thd, table_list);
899
VOID(pthread_mutex_unlock(&LOCK_open));
904
/* Probably InnoDB table */
905
ulonglong save_options= thd->options;
906
table_list->lock_type= TL_WRITE;
907
thd->options&= ~(ulong) (OPTION_BEGIN | OPTION_NOT_AUTOCOMMIT);
908
ha_enable_transaction(thd, FALSE);
909
mysql_init_select(thd->lex);
910
error= mysql_delete(thd, table_list, (COND*) 0, (SQL_LIST*) 0,
911
HA_POS_ERROR, LL(0), TRUE);
912
ha_enable_transaction(thd, TRUE);
913
thd->options= save_options;