2 Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
4 This program is free software; you can redistribute it and/or modify
5 it under the terms of the GNU General Public License as published by
6 the Free Software Foundation; version 2 of the License.
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., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
19 Delete of records and truncate of tables.
21 Multi-table deletes were introduced by Monty and Sinisa
24 #include "mysql_priv.h"
25 #include "sql_select.h"
27 #include "sql_trigger.h"
30 Implement DELETE SQL word.
32 @note Like implementations of other DDL/DML in MySQL, this function
33 relies on the caller to close the thread tables. This is done in the
34 end of dispatch_command().
37 bool mysql_delete(THD
*thd
, TABLE_LIST
*table_list
, COND
*conds
,
38 SQL_I_List
<ORDER
> *order
, ha_rows limit
, ulonglong options
,
39 bool reset_auto_increment
)
46 bool using_limit
=limit
!= HA_POS_ERROR
;
47 bool transactional_table
, safe_update
, const_cond
;
48 bool const_cond_result
;
50 bool triggers_applicable
;
51 uint usable_index
= MAX_KEY
;
52 SELECT_LEX
*select_lex
= &thd
->lex
->select_lex
;
53 THD::killed_state killed_status
= THD::NOT_KILLED
;
54 DBUG_ENTER("mysql_delete");
55 bool save_binlog_row_based
;
58 THD::enum_binlog_query_type query_type
=
59 thd
->lex
->sql_command
== SQLCOM_TRUNCATE
?
60 THD::STMT_QUERY_TYPE
:
63 if (open_and_lock_tables(thd
, table_list
))
65 if (!(table
= table_list
->table
))
67 my_error(ER_VIEW_DELETE_MERGE_VIEW
, MYF(0),
68 table_list
->view_db
.str
, table_list
->view_name
.str
);
71 thd_proc_info(thd
, "init");
74 if (mysql_prepare_delete(thd
, table_list
, &conds
))
77 /* check ORDER BY even if it can be ignored */
78 if (order
&& order
->elements
)
82 List
<Item
> all_fields
;
84 bzero((char*) &tables
,sizeof(tables
));
86 tables
.alias
= table_list
->alias
;
88 if (select_lex
->setup_ref_array(thd
, order
->elements
) ||
89 setup_order(thd
, select_lex
->ref_pointer_array
, &tables
,
90 fields
, all_fields
, order
->first
))
93 free_underlaid_joins(thd
, &thd
->lex
->select_lex
);
98 const_cond
= (!conds
|| conds
->const_item());
99 safe_update
=test(thd
->options
& OPTION_SAFE_UPDATES
);
100 if (safe_update
&& const_cond
)
102 my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
,
103 ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
), MYF(0));
107 select_lex
->no_error
= thd
->lex
->ignore
;
109 const_cond_result
= const_cond
&& (!conds
|| conds
->val_int());
112 /* Error evaluating val_int(). */
117 Test if the user wants to delete all rows and deletion doesn't have
118 any side-effects (because of triggers), so we can use optimized
119 handler::delete_all_rows() method.
121 We implement fast TRUNCATE for InnoDB even if triggers are
122 present. TRUNCATE ignores triggers.
124 We can use delete_all_rows() if and only if:
125 - We allow new functions (not using option --skip-new), and are
126 not in safe mode (not using option --safe-mode)
127 - There is no limit clause
128 - The condition is constant
129 - If there is a condition, then it it produces a non-zero value
130 - If the current command is DELETE FROM with no where clause
131 (i.e., not TRUNCATE) then:
132 - We should not be binlogging this statement row-based, and
133 - there should be no delete triggers associated with the table.
135 if (!using_limit
&& const_cond_result
&&
136 !(specialflag
& (SPECIAL_NO_NEW_FUNC
| SPECIAL_SAFE_MODE
)) &&
137 (thd
->lex
->sql_command
== SQLCOM_TRUNCATE
||
138 (!thd
->current_stmt_binlog_row_based
&&
139 !(table
->triggers
&& table
->triggers
->has_delete_triggers()))))
141 /* Update the table->file->stats.records number */
142 table
->file
->info(HA_STATUS_VARIABLE
| HA_STATUS_NO_LOCK
);
143 ha_rows
const maybe_deleted
= table
->file
->stats
.records
;
144 DBUG_PRINT("debug", ("Trying to use delete_all_rows()"));
145 if (!(error
=table
->file
->ha_delete_all_rows()))
148 If delete_all_rows() is used, it is not possible to log the
149 query in row format, so we have to log it in statement format.
151 query_type
= THD::STMT_QUERY_TYPE
;
153 deleted
= maybe_deleted
;
154 save_binlog_row_based
= thd
->current_stmt_binlog_row_based
;
157 if (error
!= HA_ERR_WRONG_COMMAND
)
159 table
->file
->print_error(error
,MYF(0));
161 save_binlog_row_based
= thd
->current_stmt_binlog_row_based
;
164 /* Handler didn't support fast delete; Delete rows one by one */
168 Item::cond_result result
;
169 conds
= remove_eq_conds(thd
, conds
, &result
);
170 if (result
== Item::COND_FALSE
) // Impossible where
174 #ifdef WITH_PARTITION_STORAGE_ENGINE
175 if (prune_partitions(thd
, table
, conds
))
177 free_underlaid_joins(thd
, select_lex
);
178 thd
->row_count_func
= 0;
179 my_ok(thd
, (ha_rows
) thd
->row_count_func
); // No matching records
183 /* Update the table->file->stats.records number */
184 table
->file
->info(HA_STATUS_VARIABLE
| HA_STATUS_NO_LOCK
);
186 table
->covering_keys
.clear_all();
187 table
->quick_keys
.clear_all(); // Can't use 'only index'
188 select
=make_select(table
, 0, 0, conds
, 0, &error
);
191 if ((select
&& select
->check_quick(thd
, safe_update
, limit
)) || !limit
)
194 free_underlaid_joins(thd
, select_lex
);
195 thd
->row_count_func
= 0;
197 Error was already created by quick select evaluation (check_quick()).
198 TODO: Add error code output parameter to Item::val_xxx() methods.
199 Currently they rely on the user checking DA for
200 errors when unwinding the stack after calling Item::val_xxx().
204 my_ok(thd
, (ha_rows
) thd
->row_count_func
);
206 We don't need to call reset_auto_increment in this case, because
207 mysql_truncate always gives a NULL conds argument, hence we never
210 DBUG_RETURN(0); // Nothing to delete
213 /* If running in safe sql mode, don't allow updates without keys */
214 if (table
->quick_keys
.is_clear_all())
216 thd
->server_status
|=SERVER_QUERY_NO_INDEX_USED
;
217 if (safe_update
&& !using_limit
)
220 free_underlaid_joins(thd
, select_lex
);
221 my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
,
222 ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
), MYF(0));
226 if (options
& OPTION_QUICK
)
227 (void) table
->file
->extra(HA_EXTRA_QUICK
);
229 if (order
&& order
->elements
)
232 SORT_FIELD
*sortorder
;
233 ha_rows examined_rows
;
235 if ((!select
|| table
->quick_keys
.is_clear_all()) && limit
!= HA_POS_ERROR
)
236 usable_index
= get_index_for_order(table
, order
->first
, limit
);
238 if (usable_index
== MAX_KEY
)
240 table
->sort
.io_cache
= (IO_CACHE
*) my_malloc(sizeof(IO_CACHE
),
241 MYF(MY_FAE
| MY_ZEROFILL
));
243 if (!(sortorder
= make_unireg_sortorder(order
->first
,
245 (table
->sort
.found_records
= filesort(thd
, table
, sortorder
, length
,
246 select
, HA_POS_ERROR
, 1,
251 free_underlaid_joins(thd
, &thd
->lex
->select_lex
);
254 thd
->examined_row_count
+= examined_rows
;
256 Filesort has already found and selected the rows we want to delete,
257 so we don't need the where clause
260 free_underlaid_joins(thd
, select_lex
);
265 /* If quick select is used, initialize it before retrieving rows. */
266 if (select
&& select
->quick
&& select
->quick
->reset())
269 free_underlaid_joins(thd
, select_lex
);
272 if (usable_index
==MAX_KEY
|| (select
&& select
->quick
))
273 init_read_record(&info
, thd
, table
, select
, 1, 1, FALSE
);
275 init_read_record_idx(&info
, thd
, table
, 1, usable_index
);
277 init_ftfuncs(thd
, select_lex
, 1);
278 thd_proc_info(thd
, "updating");
280 /* NOTE: TRUNCATE must not invoke triggers. */
282 triggers_applicable
= table
->triggers
&&
283 thd
->lex
->sql_command
!= SQLCOM_TRUNCATE
;
285 if (triggers_applicable
&&
286 table
->triggers
->has_triggers(TRG_EVENT_DELETE
,
290 The table has AFTER DELETE triggers that might access to subject table
291 and therefore might need delete to be done immediately. So we turn-off
294 (void) table
->file
->extra(HA_EXTRA_DELETE_CANNOT_BATCH
);
298 will_batch
= !table
->file
->start_bulk_delete();
301 table
->mark_columns_needed_for_delete();
303 save_binlog_row_based
= thd
->current_stmt_binlog_row_based
;
304 if (thd
->lex
->sql_command
== SQLCOM_TRUNCATE
&&
305 thd
->current_stmt_binlog_row_based
)
306 thd
->clear_current_stmt_binlog_row_based();
308 while (!(error
=info
.read_record(&info
)) && !thd
->killed
&&
311 thd
->examined_row_count
++;
312 // thd->is_error() is tested to disallow delete row on error
313 if (!select
|| (!select
->skip_record(thd
, &skip_record
) && !skip_record
))
316 if (triggers_applicable
&&
317 table
->triggers
->process_triggers(thd
, TRG_EVENT_DELETE
,
318 TRG_ACTION_BEFORE
, FALSE
))
324 if (!(error
= table
->file
->ha_delete_row(table
->record
[0])))
327 if (triggers_applicable
&&
328 table
->triggers
->process_triggers(thd
, TRG_EVENT_DELETE
,
329 TRG_ACTION_AFTER
, FALSE
))
334 if (!--limit
&& using_limit
)
342 table
->file
->print_error(error
,MYF(0));
344 In < 4.0.14 we set the error number to 0 here, but that
345 was not sensible, because then MySQL would not roll back the
346 failed DELETE, and also wrote it to the binlog. For MyISAM
347 tables a DELETE probably never should fail (?), but for
348 InnoDB it can fail in a FOREIGN KEY error or an
349 out-of-tablespace error.
356 table
->file
->unlock_row(); // Row failed selection, release lock on it
358 killed_status
= thd
->killed
;
359 if (killed_status
!= THD::NOT_KILLED
|| thd
->is_error())
361 if (will_batch
&& (loc_error
= table
->file
->end_bulk_delete()))
364 table
->file
->print_error(loc_error
,MYF(0));
367 thd_proc_info(thd
, "end");
368 end_read_record(&info
);
369 if (options
& OPTION_QUICK
)
370 (void) table
->file
->extra(HA_EXTRA_NORMAL
);
372 if (reset_auto_increment
&& (error
< 0))
375 We're really doing a truncate and need to reset the table's
376 auto-increment counter.
378 int error2
= table
->file
->ha_reset_auto_increment(0);
380 if (error2
&& (error2
!= HA_ERR_WRONG_COMMAND
))
382 table
->file
->print_error(error2
, MYF(0));
389 Invalidate the table in the query cache if something changed. This must
390 be before binlog writing and ha_autocommit_...
394 query_cache_invalidate3(thd
, table_list
, 1);
398 transactional_table
= table
->file
->has_transactions();
400 if (!transactional_table
&& deleted
> 0)
401 thd
->transaction
.stmt
.modified_non_trans_table
= TRUE
;
403 /* See similar binlogging code in sql_update.cc, for comments */
404 if ((error
< 0) || thd
->transaction
.stmt
.modified_non_trans_table
)
406 if (mysql_bin_log
.is_open() &&
407 !(thd
->lex
->sql_command
== SQLCOM_TRUNCATE
&&
408 thd
->current_stmt_binlog_row_based
&&
409 find_temporary_table(thd
, table_list
)))
412 thd
->lex
->sql_command
== SQLCOM_TRUNCATE
?
420 errcode
= query_error_code(thd
, killed_status
== THD::NOT_KILLED
);
423 [binlog]: If 'handler::delete_all_rows()' was called and the
424 storage engine does not inject the rows itself, we replicate
425 statement-based; otherwise, 'ha_delete_row()' was used to
426 delete specific rows which we might log row-based.
428 Note that TRUNCATE TABLE is not transactional and should
429 therefore be treated as a DDL.
431 int log_result
= thd
->binlog_query(query_type
,
432 thd
->query(), thd
->query_length(),
433 is_trans
, FALSE
, errcode
);
440 if (thd
->transaction
.stmt
.modified_non_trans_table
)
441 thd
->transaction
.all
.modified_non_trans_table
= TRUE
;
443 thd
->current_stmt_binlog_row_based
= save_binlog_row_based
;
444 DBUG_ASSERT(transactional_table
|| !deleted
|| thd
->transaction
.stmt
.modified_non_trans_table
);
445 free_underlaid_joins(thd
, select_lex
);
447 (thd
->lex
->ignore
&& !thd
->is_error() && !thd
->is_fatal_error
))
450 If a TRUNCATE TABLE was issued, the number of rows should be reported as
451 zero since the exact number is unknown.
453 thd
->row_count_func
= reset_auto_increment
? 0 : deleted
;
454 my_ok(thd
, (ha_rows
) thd
->row_count_func
);
455 DBUG_PRINT("info",("%ld records deleted",(long) deleted
));
457 DBUG_RETURN(error
>= 0 || thd
->is_error());
462 Prepare items in DELETE statement
465 mysql_prepare_delete()
467 table_list - global/local table list
474 int mysql_prepare_delete(THD
*thd
, TABLE_LIST
*table_list
, Item
**conds
)
477 SELECT_LEX
*select_lex
= &thd
->lex
->select_lex
;
478 DBUG_ENTER("mysql_prepare_delete");
479 List
<Item
> all_fields
;
482 Statement-based replication of DELETE ... LIMIT is not safe as order of
483 rows is not defined, so in mixed mode we go to row-based.
485 Note that we may consider a statement as safe if ORDER BY primary_key
486 is present. However it may confuse users to see very similiar statements
487 replicated differently.
489 if (thd
->lex
->current_select
->select_limit
)
491 thd
->lex
->set_stmt_unsafe();
492 thd
->set_current_stmt_binlog_row_based_if_mixed();
494 thd
->lex
->allow_sum_func
= 0;
495 if (setup_tables_and_check_access(thd
, &thd
->lex
->select_lex
.context
,
496 &thd
->lex
->select_lex
.top_join_list
,
498 &select_lex
->leaf_tables
, FALSE
,
499 DELETE_ACL
, SELECT_ACL
) ||
500 setup_conds(thd
, table_list
, select_lex
->leaf_tables
, conds
) ||
501 setup_ftfuncs(select_lex
))
503 if (!table_list
->updatable
|| check_key_in_view(thd
, table_list
))
505 my_error(ER_NON_UPDATABLE_TABLE
, MYF(0), table_list
->alias
, "DELETE");
509 TABLE_LIST
*duplicate
;
510 if ((duplicate
= unique_table(thd
, table_list
, table_list
->next_global
, 0)))
512 update_non_unique_table_error(table_list
, "DELETE", duplicate
);
517 if (select_lex
->inner_refs_list
.elements
&&
518 fix_inner_refs(thd
, all_fields
, select_lex
, select_lex
->ref_pointer_array
))
521 select_lex
->fix_prepare_information(thd
, conds
, &fake_conds
);
526 /***************************************************************************
527 Delete multiple tables from join
528 ***************************************************************************/
530 #define MEM_STRIP_BUF_SIZE current_thd->variables.sortbuff_size
532 extern "C" int refpos_order_cmp(void* arg
, const void *a
,const void *b
)
534 handler
*file
= (handler
*)arg
;
535 return file
->cmp_ref((const uchar
*)a
, (const uchar
*)b
);
539 make delete specific preparation and checks after opening tables
542 mysql_multi_delete_prepare()
550 int mysql_multi_delete_prepare(THD
*thd
)
553 TABLE_LIST
*aux_tables
= lex
->auxiliary_table_list
.first
;
554 TABLE_LIST
*target_tbl
;
555 DBUG_ENTER("mysql_multi_delete_prepare");
558 setup_tables() need for VIEWs. JOIN::prepare() will not do it second
561 lex->query_tables also point on local list of DELETE SELECT_LEX
563 if (setup_tables_and_check_access(thd
, &thd
->lex
->select_lex
.context
,
564 &thd
->lex
->select_lex
.top_join_list
,
566 &lex
->select_lex
.leaf_tables
, FALSE
,
567 DELETE_ACL
, SELECT_ACL
))
572 Multi-delete can't be constructed over-union => we always have
573 single SELECT on top and have to check underlying SELECTs of it
575 lex
->select_lex
.exclude_from_table_unique_test
= TRUE
;
576 /* Fix tables-to-be-deleted-from list to point at opened tables */
577 for (target_tbl
= (TABLE_LIST
*) aux_tables
;
579 target_tbl
= target_tbl
->next_local
)
581 if (!(target_tbl
->table
= target_tbl
->correspondent_table
->table
))
583 DBUG_ASSERT(target_tbl
->correspondent_table
->view
&&
584 target_tbl
->correspondent_table
->merge_underlying_list
&&
585 target_tbl
->correspondent_table
->merge_underlying_list
->
587 my_error(ER_VIEW_DELETE_MERGE_VIEW
, MYF(0),
588 target_tbl
->correspondent_table
->view_db
.str
,
589 target_tbl
->correspondent_table
->view_name
.str
);
593 if (!target_tbl
->correspondent_table
->updatable
||
594 check_key_in_view(thd
, target_tbl
->correspondent_table
))
596 my_error(ER_NON_UPDATABLE_TABLE
, MYF(0),
597 target_tbl
->table_name
, "DELETE");
601 Check that table from which we delete is not used somewhere
602 inside subqueries/view.
605 TABLE_LIST
*duplicate
;
606 if ((duplicate
= unique_table(thd
, target_tbl
->correspondent_table
,
607 lex
->query_tables
, 0)))
609 update_non_unique_table_error(target_tbl
->correspondent_table
,
610 "DELETE", duplicate
);
616 Reset the exclude flag to false so it doesn't interfare
617 with further calls to unique_table
619 lex
->select_lex
.exclude_from_table_unique_test
= FALSE
;
624 multi_delete::multi_delete(TABLE_LIST
*dt
, uint num_of_tables_arg
)
625 : delete_tables(dt
), deleted(0), found(0),
626 num_of_tables(num_of_tables_arg
), error(0),
627 do_delete(0), transactional_tables(0), normal_tables(0), error_handled(0)
629 tempfiles
= (Unique
**) sql_calloc(sizeof(Unique
*) * num_of_tables
);
634 multi_delete::prepare(List
<Item
> &values
, SELECT_LEX_UNIT
*u
)
636 DBUG_ENTER("multi_delete::prepare");
639 thd_proc_info(thd
, "deleting from main table");
645 multi_delete::initialize_tables(JOIN
*join
)
648 Unique
**tempfiles_ptr
;
649 DBUG_ENTER("initialize_tables");
651 if ((thd
->options
& OPTION_SAFE_UPDATES
) && error_if_full_join(join
))
654 table_map tables_to_delete_from
=0;
655 delete_while_scanning
= 1;
656 for (walk
= delete_tables
; walk
; walk
= walk
->next_local
)
658 tables_to_delete_from
|= walk
->table
->map
;
659 if (delete_while_scanning
&&
660 unique_table(thd
, walk
, join
->tables_list
, false))
663 If the table we are going to delete from appears
664 in join, we need to defer delete. So the delete
665 doesn't interfers with the scaning of results.
667 delete_while_scanning
= 0;
673 for (JOIN_TAB
*tab
=join
->join_tab
, *end
=join
->join_tab
+join
->tables
;
677 if (tab
->table
->map
& tables_to_delete_from
)
679 /* We are going to delete from this table */
680 TABLE
*tbl
=walk
->table
=tab
->table
;
681 walk
= walk
->next_local
;
682 /* Don't use KEYREAD optimization on this table */
684 /* Don't use record cache */
686 tbl
->covering_keys
.clear_all();
687 if (tbl
->file
->has_transactions())
688 transactional_tables
= 1;
692 tbl
->triggers
->has_triggers(TRG_EVENT_DELETE
,
696 The table has AFTER DELETE triggers that might access to subject
697 table and therefore might need delete to be done immediately.
698 So we turn-off the batching.
700 (void) tbl
->file
->extra(HA_EXTRA_DELETE_CANNOT_BATCH
);
702 tbl
->prepare_for_position();
703 tbl
->mark_columns_needed_for_delete();
705 else if ((tab
->type
!= JT_SYSTEM
&& tab
->type
!= JT_CONST
) &&
706 walk
== delete_tables
)
709 We are not deleting from the table we are scanning. In this
710 case send_data() shouldn't delete any rows a we may touch
711 the rows in the deleted table many times
713 delete_while_scanning
= 0;
717 tempfiles_ptr
= tempfiles
;
718 if (delete_while_scanning
)
720 table_being_deleted
= delete_tables
;
721 walk
= walk
->next_local
;
723 for (;walk
;walk
= walk
->next_local
)
725 TABLE
*table
=walk
->table
;
726 *tempfiles_ptr
++= new Unique (refpos_order_cmp
,
727 (void *) table
->file
,
728 table
->file
->ref_length
,
731 init_ftfuncs(thd
, thd
->lex
->current_select
, 1);
732 DBUG_RETURN(thd
->is_fatal_error
!= 0);
736 multi_delete::~multi_delete()
738 for (table_being_deleted
= delete_tables
;
740 table_being_deleted
= table_being_deleted
->next_local
)
742 TABLE
*table
= table_being_deleted
->table
;
746 for (uint counter
= 0; counter
< num_of_tables
; counter
++)
748 if (tempfiles
[counter
])
749 delete tempfiles
[counter
];
754 bool multi_delete::send_data(List
<Item
> &values
)
756 int secure_counter
= delete_while_scanning
? -1 : 0;
757 TABLE_LIST
*del_table
;
758 DBUG_ENTER("multi_delete::send_data");
760 bool ignore
= thd
->lex
->current_select
->no_error
;
762 for (del_table
= delete_tables
;
764 del_table
= del_table
->next_local
, secure_counter
++)
766 TABLE
*table
= del_table
->table
;
768 /* Check if we are using outer join and we didn't find the row */
769 if (table
->status
& (STATUS_NULL_ROW
| STATUS_DELETED
))
772 table
->file
->position(table
->record
[0]);
775 if (secure_counter
< 0)
777 /* We are scanning the current table */
778 DBUG_ASSERT(del_table
== table_being_deleted
);
779 if (table
->triggers
&&
780 table
->triggers
->process_triggers(thd
, TRG_EVENT_DELETE
,
781 TRG_ACTION_BEFORE
, FALSE
))
783 table
->status
|= STATUS_DELETED
;
784 if (!(error
=table
->file
->ha_delete_row(table
->record
[0])))
787 if (!table
->file
->has_transactions())
788 thd
->transaction
.stmt
.modified_non_trans_table
= TRUE
;
789 if (table
->triggers
&&
790 table
->triggers
->process_triggers(thd
, TRG_EVENT_DELETE
,
791 TRG_ACTION_AFTER
, FALSE
))
797 If the IGNORE option is used errors caused by ha_delete_row don't
798 have to stop the iteration.
800 table
->file
->print_error(error
,MYF(0));
806 error
=tempfiles
[secure_counter
]->unique_add((char*) table
->file
->ref
);
809 error
= 1; // Fatal error
818 void multi_delete::send_error(uint errcode
,const char *err
)
820 DBUG_ENTER("multi_delete::send_error");
822 /* First send error what ever it is ... */
823 my_message(errcode
, err
, MYF(0));
829 void multi_delete::abort()
831 DBUG_ENTER("multi_delete::abort");
833 /* the error was handled or nothing deleted and no side effects return */
835 (!thd
->transaction
.stmt
.modified_non_trans_table
&& !deleted
))
838 /* Something already deleted so we have to invalidate cache */
840 query_cache_invalidate3(thd
, delete_tables
, 1);
843 If rows from the first table only has been deleted and it is
844 transactional, just do rollback.
845 The same if all tables are transactional, regardless of where we are.
846 In all other cases do attempt deletes ...
848 if (do_delete
&& normal_tables
&&
849 (table_being_deleted
!= delete_tables
||
850 !table_being_deleted
->table
->file
->has_transactions()))
853 We have to execute the recorded do_deletes() and write info into the
858 DBUG_ASSERT(error_handled
);
862 if (thd
->transaction
.stmt
.modified_non_trans_table
)
865 there is only side effects; to binlog with the error
867 if (mysql_bin_log
.is_open())
869 int errcode
= query_error_code(thd
, thd
->killed
== THD::NOT_KILLED
);
870 /* possible error of writing binary log is ignored deliberately */
871 (void) thd
->binlog_query(THD::ROW_QUERY_TYPE
,
872 thd
->query(), thd
->query_length(),
873 transactional_tables
, FALSE
, errcode
);
875 thd
->transaction
.all
.modified_non_trans_table
= true;
883 Do delete from other tables.
888 @todo Is there any reason not use the normal nested-loops join? If not, and
889 there is no documentation supporting it, this method and callee should be
890 removed and there should be hooks within normal execution.
893 int multi_delete::do_deletes()
895 DBUG_ENTER("do_deletes");
896 DBUG_ASSERT(do_delete
);
898 do_delete
= 0; // Mark called
902 table_being_deleted
= (delete_while_scanning
? delete_tables
->next_local
:
905 for (uint counter
= 0; table_being_deleted
;
906 table_being_deleted
= table_being_deleted
->next_local
, counter
++)
908 TABLE
*table
= table_being_deleted
->table
;
909 if (tempfiles
[counter
]->get(table
))
913 do_table_deletes(table
, thd
->lex
->current_select
->no_error
);
915 if (thd
->killed
&& !local_error
)
918 if (local_error
== -1) // End of file
922 DBUG_RETURN(local_error
);
929 Implements the inner loop of nested-loops join within multi-DELETE
932 @param table The table from which to delete.
934 @param ignore If used, all non fatal errors will be translated
935 to warnings and we should not break the row-by-row iteration.
940 @retval 1 Triggers or handler reported error.
941 @retval -1 End of file from handler.
943 int multi_delete::do_table_deletes(TABLE
*table
, bool ignore
)
947 ha_rows last_deleted
= deleted
;
948 DBUG_ENTER("do_deletes_for_table");
949 init_read_record(&info
, thd
, table
, NULL
, 0, 1, FALSE
);
951 Ignore any rows not found in reference tables as they may already have
952 been deleted by foreign key handling
954 info
.ignore_not_found_rows
= 1;
955 bool will_batch
= !table
->file
->start_bulk_delete();
956 while (!(local_error
= info
.read_record(&info
)) && !thd
->killed
)
958 if (table
->triggers
&&
959 table
->triggers
->process_triggers(thd
, TRG_EVENT_DELETE
,
960 TRG_ACTION_BEFORE
, FALSE
))
966 local_error
= table
->file
->ha_delete_row(table
->record
[0]);
967 if (local_error
&& !ignore
)
969 table
->file
->print_error(local_error
, MYF(0));
974 Increase the reported number of deleted rows only if no error occurred
975 during ha_delete_row.
976 Also, don't execute the AFTER trigger if the row operation failed.
981 if (table
->triggers
&&
982 table
->triggers
->process_triggers(thd
, TRG_EVENT_DELETE
,
983 TRG_ACTION_AFTER
, FALSE
))
992 int tmp_error
= table
->file
->end_bulk_delete();
993 if (tmp_error
&& !local_error
)
995 local_error
= tmp_error
;
996 table
->file
->print_error(local_error
, MYF(0));
999 if (last_deleted
!= deleted
&& !table
->file
->has_transactions())
1000 thd
->transaction
.stmt
.modified_non_trans_table
= TRUE
;
1002 end_read_record(&info
);
1004 DBUG_RETURN(local_error
);
1008 Send ok to the client
1014 bool multi_delete::send_eof()
1016 THD::killed_state killed_status
= THD::NOT_KILLED
;
1017 thd_proc_info(thd
, "deleting from reference tables");
1019 /* Does deletes for the last n - 1 tables, returns 0 if ok */
1020 int local_error
= do_deletes(); // returns 0 if success
1022 /* compute a total error to know if something failed */
1023 local_error
= local_error
|| error
;
1024 killed_status
= (local_error
== 0)? THD::NOT_KILLED
: thd
->killed
;
1025 /* reset used flags */
1026 thd_proc_info(thd
, "end");
1029 We must invalidate the query cache before binlog writing and
1034 query_cache_invalidate3(thd
, delete_tables
, 1);
1036 if ((local_error
== 0) || thd
->transaction
.stmt
.modified_non_trans_table
)
1038 if (mysql_bin_log
.is_open())
1041 if (local_error
== 0)
1044 errcode
= query_error_code(thd
, killed_status
== THD::NOT_KILLED
);
1045 if (thd
->binlog_query(THD::ROW_QUERY_TYPE
,
1046 thd
->query(), thd
->query_length(),
1047 transactional_tables
, FALSE
, errcode
) &&
1050 local_error
=1; // Log write failed: roll back the SQL statement
1053 if (thd
->transaction
.stmt
.modified_non_trans_table
)
1054 thd
->transaction
.all
.modified_non_trans_table
= TRUE
;
1056 if (local_error
!= 0)
1057 error_handled
= TRUE
; // to force early leave from ::send_error()
1061 thd
->row_count_func
= deleted
;
1062 ::my_ok(thd
, (ha_rows
) thd
->row_count_func
);
1068 /***************************************************************************
1070 ****************************************************************************/
1073 Row-by-row truncation if the engine does not support table recreation.
1074 Probably a InnoDB table.
1077 static bool mysql_truncate_by_delete(THD
*thd
, TABLE_LIST
*table_list
)
1080 DBUG_ENTER("mysql_truncate_by_delete");
1081 table_list
->lock_type
= TL_WRITE
;
1082 mysql_init_select(thd
->lex
);
1083 error
= mysql_delete(thd
, table_list
, NULL
, NULL
, HA_POS_ERROR
, LL(0), TRUE
);
1084 ha_autocommit_or_rollback(thd
, error
);
1085 end_trans(thd
, error
? ROLLBACK
: COMMIT
);
1091 Optimize delete of all rows by doing a full generate of the table
1092 This will work even if the .ISM and .ISD tables are destroyed
1094 dont_send_ok should be set if:
1095 - We should always wants to generate the table (even if the table type
1096 normally can't safely do this.
1097 - We don't want an ok to be sent to the end user.
1098 - We don't want to log the truncate command
1099 - If we want to have a name lock on the table on exit without errors.
1102 bool mysql_truncate(THD
*thd
, TABLE_LIST
*table_list
, bool dont_send_ok
)
1104 HA_CREATE_INFO create_info
;
1105 char path
[FN_REFLEN
+ 1];
1109 bool is_temporary_table
= false;
1110 DBUG_ENTER("mysql_truncate");
1112 bzero((char*) &create_info
,sizeof(create_info
));
1114 /* Remove tables from the HANDLER's hash. */
1115 mysql_ha_rm_tables(thd
, table_list
, FALSE
);
1117 /* If it is a temporary table, close and regenerate it */
1118 if (!dont_send_ok
&& (table
= find_temporary_table(thd
, table_list
)))
1120 is_temporary_table
= true;
1121 handlerton
*table_type
= table
->s
->db_type();
1122 TABLE_SHARE
*share
= table
->s
;
1123 if (!ha_check_storage_engine_flag(table_type
, HTON_CAN_RECREATE
))
1126 table
->file
->info(HA_STATUS_AUTO
| HA_STATUS_NO_LOCK
);
1128 close_temporary_table(thd
, table
, 0, 0); // Don't free share
1129 ha_create_table(thd
, share
->normalized_path
.str
,
1130 share
->db
.str
, share
->table_name
.str
, &create_info
, 1);
1131 // We don't need to call invalidate() because this table is not in cache
1132 if ((error
= (int) !(open_temporary_table(thd
, share
->path
.str
,
1134 share
->table_name
.str
, 1))))
1135 (void) rm_temporary_table(table_type
, path
);
1137 thd
->thread_specific_used
= TRUE
;
1139 free_table_share(share
);
1140 my_free((char*) table
,MYF(0));
1142 If we return here we will not have logged the truncation to the bin log
1143 and we will not my_ok() to the client.
1148 path_length
= build_table_filename(path
, sizeof(path
) - 1, table_list
->db
,
1149 table_list
->table_name
, reg_ext
, 0);
1153 enum legacy_db_type table_type
;
1154 mysql_frm_type(thd
, path
, &table_type
);
1155 if (table_type
== DB_TYPE_UNKNOWN
)
1157 my_error(ER_NO_SUCH_TABLE
, MYF(0),
1158 table_list
->db
, table_list
->table_name
);
1161 if (!ha_check_storage_engine_flag(ha_resolve_by_legacy_type(thd
, table_type
),
1165 if (lock_and_wait_for_table_name(thd
, table_list
))
1169 // Remove the .frm extension AIX 5.2 64-bit compiler bug (BUG#16155): this
1170 // crashes, replacement works. *(path + path_length - reg_ext_length)=
1172 path
[path_length
- reg_ext_length
] = 0;
1173 VOID(pthread_mutex_lock(&LOCK_open
));
1174 error
= ha_create_table(thd
, path
, table_list
->db
, table_list
->table_name
,
1176 VOID(pthread_mutex_unlock(&LOCK_open
));
1177 query_cache_invalidate3(thd
, table_list
, 0);
1184 /* In RBR, the statement is not binlogged if the table is temporary. */
1185 if (!is_temporary_table
|| !thd
->current_stmt_binlog_row_based
)
1186 error
= write_bin_log(thd
, TRUE
, thd
->query(), thd
->query_length());
1188 my_ok(thd
); // This should return record count
1190 VOID(pthread_mutex_lock(&LOCK_open
));
1191 unlock_table_name(thd
, table_list
);
1192 VOID(pthread_mutex_unlock(&LOCK_open
));
1196 VOID(pthread_mutex_lock(&LOCK_open
));
1197 unlock_table_name(thd
, table_list
);
1198 VOID(pthread_mutex_unlock(&LOCK_open
));
1203 error
= mysql_truncate_by_delete(thd
, table_list
);