sql_delete.cc 14.8 KB
Newer Older
1
/* Copyright (C) 2000 MySQL AB
unknown's avatar
unknown committed
2

unknown's avatar
unknown committed
3 4 5 6
   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
   the Free Software Foundation; either version 2 of the License, or
   (at your option) any later version.
unknown's avatar
unknown committed
7

unknown's avatar
unknown committed
8 9 10 11
   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.
unknown's avatar
unknown committed
12

unknown's avatar
unknown committed
13 14 15 16
   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */

17

unknown's avatar
unknown committed
18
/*
19
  Delete of records and truncate of tables.
20

21
  Multi-table deletes were introduced by Monty and Sinisa
unknown's avatar
unknown committed
22 23
*/

24 25


26
#include "mysql_priv.h"
27
#include "ha_innodb.h"
28
#include "sql_select.h"
unknown's avatar
unknown committed
29

30 31
int mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, ORDER *order,
                 ha_rows limit, thr_lock_type lock_type, ulong options)
unknown's avatar
unknown committed
32 33 34
{
  int		error;
  TABLE		*table;
35
  SQL_SELECT	*select=0;
unknown's avatar
unknown committed
36 37
  READ_RECORD	info;
  bool 		using_limit=limit != HA_POS_ERROR;
38 39
  bool	        using_transactions;
  ha_rows	deleted;
unknown's avatar
unknown committed
40 41 42 43 44 45 46 47 48 49
  DBUG_ENTER("mysql_delete");

  if (!table_list->db)
    table_list->db=thd->db;
  if ((thd->options & OPTION_SAFE_UPDATES) && !conds)
  {
    send_error(&thd->net,ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE);
    DBUG_RETURN(1);
  }

50
  if (!(table = open_ltable(thd,table_list, lock_type)))
unknown's avatar
unknown committed
51
    DBUG_RETURN(-1);
52
  table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
unknown's avatar
unknown committed
53 54
  thd->proc_info="init";
  table->map=1;
55
  if (setup_conds(thd,table_list,&conds) || setup_ftfuncs(thd))
unknown's avatar
unknown committed
56 57
    DBUG_RETURN(-1);

58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
  /* Test if the user wants to delete all rows */
  if (!using_limit && (!conds || conds->const_item()) &&
      !(specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)))
  {
    deleted= table->file->records;
    if (!(error=table->file->delete_all_rows()))
    {
      error= -1;				// ok
      goto cleanup;
    }
    if (error != HA_ERR_WRONG_COMMAND)
    {
      table->file->print_error(error,MYF(0));
      error=0;
      goto cleanup;
    }
    /* Handler didn't support fast delete; Delete rows one by one */
  }

unknown's avatar
unknown committed
77 78 79 80
  table->used_keys=table->quick_keys=0;		// Can't use 'only index'
  select=make_select(table,0,0,conds,&error);
  if (error)
    DBUG_RETURN(-1);
unknown's avatar
unknown committed
81
  if ((select && select->check_quick(test(thd->options & SQL_SAFE_UPDATES),
82
				     limit)) || 
unknown's avatar
unknown committed
83
      !limit)
unknown's avatar
unknown committed
84 85 86
  {
    delete select;
    send_ok(&thd->net,0L);
unknown's avatar
unknown committed
87
    DBUG_RETURN(0);				// Nothing to delete
unknown's avatar
unknown committed
88 89 90
  }

  /* If running in safe sql mode, don't allow updates without keys */
91
  if (!table->quick_keys)
unknown's avatar
unknown committed
92
  {
93
    thd->lex.select_lex.options|=QUERY_NO_INDEX_USED;
94 95 96 97 98 99
    if ((thd->options & OPTION_SAFE_UPDATES) && limit == HA_POS_ERROR)
    {
      delete select;
      send_error(&thd->net,ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE);
      DBUG_RETURN(1);
    }
unknown's avatar
unknown committed
100
  }
101
  if (options & OPTION_QUICK)
102 103 104 105 106 107 108 109 110
    (void) table->file->extra(HA_EXTRA_QUICK);

  if (order)
  {
    uint         length;
    SORT_FIELD  *sortorder;
    TABLE_LIST   tables;
    List<Item>   fields;
    List<Item>   all_fields;
111
    ha_rows examined_rows;
112 113 114 115 116 117 118 119

    bzero((char*) &tables,sizeof(tables));
    tables.table = table;

    table->io_cache = (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
                                             MYF(MY_FAE | MY_ZEROFILL));
    if (setup_order(thd, &tables, fields, all_fields, order) ||
        !(sortorder=make_unireg_sortorder(order, &length)) ||
120
        (table->found_records = filesort(table, sortorder, length,
121 122
                                        (SQL_SELECT *) 0, 0L, HA_POS_ERROR,
					 &examined_rows))
123 124 125 126 127 128 129 130
        == HA_POS_ERROR)
    {
      delete select;
      DBUG_RETURN(-1);		// This will force out message
    }
  }

  init_read_record(&info,thd,table,select,1,1);
131
  deleted=0L;
132
  init_ftfuncs(thd,1);
unknown's avatar
unknown committed
133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153
  thd->proc_info="updating";
  while (!(error=info.read_record(&info)) && !thd->killed)
  {
    if (!(select && select->skipp_record()))
    {
      if (!(error=table->file->delete_row(table->record[0])))
      {
	deleted++;
	if (!--limit && using_limit)
	{
	  error= -1;
	  break;
	}
      }
      else
      {
	table->file->print_error(error,MYF(0));
	error=0;
	break;
      }
    }
unknown's avatar
unknown committed
154 155
    else
      table->file->unlock_row();  // Row failed selection, release lock on it
unknown's avatar
unknown committed
156 157 158
  }
  thd->proc_info="end";
  end_read_record(&info);
159
  free_io_cache(table);				// Will not do any harm
160
  if (options & OPTION_QUICK)
161
    (void) table->file->extra(HA_EXTRA_NORMAL);
162 163

cleanup:
164
  using_transactions=table->file->has_transactions();
unknown's avatar
unknown committed
165
  if (deleted && (error <= 0 || !using_transactions))
unknown's avatar
unknown committed
166
  {
167 168 169
    mysql_update_log.write(thd,thd->query, thd->query_length);
    if (mysql_bin_log.is_open())
    {
170 171 172
      Query_log_event qinfo(thd, thd->query, using_transactions);
      if (mysql_bin_log.write(&qinfo) && using_transactions)
	error=1;
173
    }
174
    if (!using_transactions)
175
      thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
unknown's avatar
unknown committed
176
  }
177
  if (using_transactions && ha_autocommit_or_rollback(thd,error >= 0))
unknown's avatar
unknown committed
178 179 180 181 182 183
    error=1;
  if (thd->lock)
  {
    mysql_unlock_tables(thd, thd->lock);
    thd->lock=0;
  }
unknown's avatar
unknown committed
184 185
  if (deleted)
    query_cache.invalidate(table_list);
unknown's avatar
unknown committed
186 187 188 189 190 191 192 193 194 195 196 197
  delete select;
  if (error >= 0)				// Fatal error
    send_error(&thd->net,thd->killed ? ER_SERVER_SHUTDOWN: 0);
  else
  {
    send_ok(&thd->net,deleted);
    DBUG_PRINT("info",("%d records deleted",deleted));
  }
  DBUG_RETURN(0);
}


198 199 200 201
/***************************************************************************
** delete multiple tables from join 
***************************************************************************/

unknown's avatar
unknown committed
202
#define MEM_STRIP_BUF_SIZE sortbuff_size
unknown's avatar
unknown committed
203

204 205
int refposcmp2(void* arg, const void *a,const void *b)
{
206
  return memcmp(a,b, *(int*) arg);
207
}
208

unknown's avatar
unknown committed
209 210 211 212 213 214
multi_delete::multi_delete(THD *thd_arg, TABLE_LIST *dt,
			   thr_lock_type lock_option_arg,
			   uint num_of_tables_arg)
  : delete_tables (dt), thd(thd_arg), deleted(0),
    num_of_tables(num_of_tables_arg), error(0), lock_option(lock_option_arg),
    do_delete(false)
215
{
unknown's avatar
unknown committed
216
  uint counter=0;
217
  not_trans_safe=false;
unknown's avatar
unknown committed
218 219 220 221 222
  tempfiles = (Unique **) sql_calloc(sizeof(Unique *) * (num_of_tables-1));

  /* Don't use key read with MULTI-TABLE-DELETE */
  dt->table->used_keys=0;
  for (dt=dt->next ; dt ; dt=dt->next,counter++)
223
  {
unknown's avatar
unknown committed
224
    TABLE *table=dt->table;
225
    table->used_keys=0;
unknown's avatar
unknown committed
226
    tempfiles[counter] = new Unique (refposcmp2,
227
				     (void *) &table->file->ref_length,
unknown's avatar
unknown committed
228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244
				     table->file->ref_length,
				     MEM_STRIP_BUF_SIZE);
  }
}


int
multi_delete::prepare(List<Item> &values)
{
  DBUG_ENTER("multi_delete::prepare");
  do_delete = true;   
  thd->proc_info="deleting from main table";

  if (thd->options & OPTION_SAFE_UPDATES)
  {
    TABLE_LIST *table_ref;
    for (table_ref=delete_tables;  table_ref; table_ref=table_ref->next)
245
    {
unknown's avatar
unknown committed
246 247
      TABLE *table=table_ref->table;
      if ((thd->options & OPTION_SAFE_UPDATES) && !table->quick_keys)
248
      {
unknown's avatar
unknown committed
249
	my_error(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,MYF(0));
250 251 252 253 254 255 256
	DBUG_RETURN(1);
      }
    }
  }
  DBUG_RETURN(0);
}

unknown's avatar
unknown committed
257 258 259 260

void
multi_delete::initialize_tables(JOIN *join)
{
261 262 263 264
  TABLE_LIST *walk;
  table_map tables_to_delete_from=0;
  for (walk= delete_tables ; walk ; walk=walk->next)
    tables_to_delete_from|= walk->table->map;
unknown's avatar
unknown committed
265

266
  walk= delete_tables;
unknown's avatar
unknown committed
267 268 269 270
  for (JOIN_TAB *tab=join->join_tab, *end=join->join_tab+join->tables;
       tab < end;
       tab++)
  {
271
    if (tab->table->map & tables_to_delete_from)
unknown's avatar
unknown committed
272
    {
273 274 275
      /* We are going to delete from this table */
      walk->table=tab->table;
      walk=walk->next;
unknown's avatar
unknown committed
276 277
      if (tab == join->join_tab)
	tab->table->no_keyread=1;
278 279
      if (!not_trans_safe && !tab->table->file->has_transactions())
	not_trans_safe=true;
unknown's avatar
unknown committed
280 281
    }
  }
282
  init_ftfuncs(thd,1);
unknown's avatar
unknown committed
283
}
unknown's avatar
unknown committed
284

285

286 287
multi_delete::~multi_delete()
{
unknown's avatar
unknown committed
288 289 290
  for (table_being_deleted=delete_tables ;
       table_being_deleted ;
       table_being_deleted=table_being_deleted->next)
291 292
  {
    TABLE *t=table_being_deleted->table;
293
    free_io_cache(t);				// Alloced by unique
294 295
    t->no_keyread=0;
  }
296

unknown's avatar
unknown committed
297 298
  for (uint counter = 0; counter < num_of_tables-1; counter++)
  {
299
    if (tempfiles[counter])
unknown's avatar
unknown committed
300 301
      delete tempfiles[counter];
  }
302 303
}

unknown's avatar
unknown committed
304

305 306
bool multi_delete::send_data(List<Item> &values)
{
unknown's avatar
unknown committed
307 308 309 310
  int secure_counter= -1;
  for (table_being_deleted=delete_tables ;
       table_being_deleted ;
       table_being_deleted=table_being_deleted->next, secure_counter++)
311 312
  {
    TABLE *table=table_being_deleted->table;
unknown's avatar
unknown committed
313 314 315 316 317 318

    /* Check if we are using outer join and we didn't find the row */
    if (table->status & (STATUS_NULL_ROW | STATUS_DELETED))
      continue;

    table->file->position(table->record[0]);
unknown's avatar
unknown committed
319

unknown's avatar
unknown committed
320
    if (secure_counter < 0)
321
    {
unknown's avatar
unknown committed
322 323 324
      table->status|= STATUS_DELETED;
      if (!(error=table->file->delete_row(table->record[0])))
	deleted++;
325 326
      else
      {
unknown's avatar
unknown committed
327 328 329 330 331 332
	table->file->print_error(error,MYF(0));
	return 1;
      }
    }
    else
    {
unknown's avatar
unknown committed
333
      error=tempfiles[secure_counter]->unique_add((char*) table->file->ref);
unknown's avatar
unknown committed
334 335 336 337 338
      if (error)
      {
	error=-1;
	return 1;
      }
339 340 341 342 343 344 345
    }
  }
  return 0;
}

void multi_delete::send_error(uint errcode,const char *err)
{
unknown's avatar
unknown committed
346
  /* First send error what ever it is ... */
347
  ::send_error(&thd->net,errcode,err);
unknown's avatar
unknown committed
348

unknown's avatar
unknown committed
349 350 351
  /* If nothing deleted return */
  if (!deleted)
    return;
352

unknown's avatar
unknown committed
353 354 355 356 357
  /* Below can happen when thread is killed early ... */
  if (!table_being_deleted)
    table_being_deleted=delete_tables;

  /*
358 359
    If rows from the first table only has been deleted and it is
    transactional, just do rollback.
unknown's avatar
unknown committed
360 361 362 363
    The same if all tables are transactional, regardless of where we are.
    In all other cases do attempt deletes ...
  */
  if ((table_being_deleted->table->file->has_transactions() &&
364
       table_being_deleted == delete_tables) || !not_trans_safe)
365
    ha_rollback_stmt(thd);
366 367 368 369
  else if (do_delete)
    VOID(do_deletes(true));
}

unknown's avatar
unknown committed
370

unknown's avatar
unknown committed
371 372 373 374 375 376 377
/*
  Do delete from other tables.
  Returns values:
	0 ok
	1 error
*/

unknown's avatar
unknown committed
378
int multi_delete::do_deletes (bool from_send_error)
379
{
unknown's avatar
unknown committed
380
  int error = 0, counter = 0;
unknown's avatar
unknown committed
381

382 383
  if (from_send_error)
  {
unknown's avatar
unknown committed
384 385 386 387
    /* Found out table number for 'table_being_deleted' */
    for (TABLE_LIST *aux=delete_tables;
	 aux != table_being_deleted;
	 aux=aux->next)
388 389 390 391
      counter++;
  }
  else
    table_being_deleted = delete_tables;
unknown's avatar
unknown committed
392

393
  do_delete = false;
unknown's avatar
unknown committed
394 395 396
  for (table_being_deleted=table_being_deleted->next;
       table_being_deleted ;
       table_being_deleted=table_being_deleted->next, counter++)
397
  { 
unknown's avatar
unknown committed
398 399 400 401 402 403 404 405 406 407 408 409
    TABLE *table = table_being_deleted->table;
    if (tempfiles[counter]->get(table))
    {
      error=1;
      break;
    }

    READ_RECORD	info;
    init_read_record(&info,thd,table,NULL,0,0);
    while (!(error=info.read_record(&info)) &&
	   (!thd->killed ||  from_send_error || not_trans_safe))
    {
unknown's avatar
unknown committed
410
      if ((error=table->file->delete_row(table->record[0])))
411
      {
unknown's avatar
unknown committed
412 413
	table->file->print_error(error,MYF(0));
	break;
414
      }
unknown's avatar
unknown committed
415
      deleted++;
416
    }
unknown's avatar
unknown committed
417
    end_read_record(&info);
unknown's avatar
unknown committed
418
    if (error == -1)				// End of file
unknown's avatar
unknown committed
419
      error = 0;
420 421 422 423
  }
  return error;
}

unknown's avatar
unknown committed
424

425 426 427 428 429
/*
  return:  0 sucess
	   1 error
*/

430 431
bool multi_delete::send_eof()
{
432
  thd->proc_info="deleting from reference tables";
433 434

  /* Does deletes for the last n - 1 tables, returns 0 if ok */
435
  int error = do_deletes(false);		// returns 0 if success
unknown's avatar
unknown committed
436

unknown's avatar
unknown committed
437
  /* reset used flags */
438
  thd->proc_info="end";
439
  if (error)
440 441 442 443
  {
    ::send_error(&thd->net);
    return 1;
  }
unknown's avatar
unknown committed
444

445 446 447 448 449 450
  /*
    Write the SQL statement to the binlog if we deleted
    rows and we succeeded, or also in an error case when there
    was a non-transaction-safe table involved, since
    modifications in it cannot be rolled back.
  */
451
  if (deleted || not_trans_safe)
452 453
  {
    mysql_update_log.write(thd,thd->query,thd->query_length);
unknown's avatar
unknown committed
454 455 456 457
    if (mysql_bin_log.is_open())
    {
      Query_log_event qinfo(thd, thd->query);
      if (mysql_bin_log.write(&qinfo) &&
458
	  !not_trans_safe)
unknown's avatar
unknown committed
459 460
	error=1;  // Log write failed: roll back the SQL statement
    }
461 462
    /* Commit or rollback the current SQL statement */ 
    VOID(ha_autocommit_or_rollback(thd,error > 0));
463
  }
464 465
  if (deleted)
    query_cache.invalidate(delete_tables);
466 467 468
  ::send_ok(&thd->net,deleted);
  return 0;
}
469 470 471 472 473 474 475 476 477 478 479 480 481 482 483


/***************************************************************************
* TRUNCATE TABLE
****************************************************************************/

/*
  Optimize delete of all rows by doing a full generate of the table
  This will work even if the .ISM and .ISD tables are destroyed

  dont_send_ok should be set if:
  - We should always wants to generate the table (even if the table type
    normally can't safely do this.
  - We don't want an ok to be sent to the end user.
  - We don't want to log the truncate command
unknown's avatar
unknown committed
484
  - If we want to have a name lock on the table on exit without errors.
485 486 487 488 489 490 491 492 493 494 495
*/

int mysql_truncate(THD *thd, TABLE_LIST *table_list, bool dont_send_ok)
{
  HA_CREATE_INFO create_info;
  char path[FN_REFLEN];
  TABLE **table_ptr;
  int error;
  DBUG_ENTER("mysql_truncate");

  /* If it is a temporary table, close and regenerate it */
unknown's avatar
unknown committed
496 497
  if (!dont_send_ok && (table_ptr=find_temporary_table(thd,table_list->db,
						       table_list->real_name)))
498 499 500 501 502 503 504 505 506 507 508 509 510
  {
    TABLE *table= *table_ptr;
    HA_CREATE_INFO create_info;
    table->file->info(HA_STATUS_AUTO | HA_STATUS_NO_LOCK);
    bzero((char*) &create_info,sizeof(create_info));
    create_info.auto_increment_value= table->file->auto_increment_value;
    db_type table_type=table->db_type;

    strmov(path,table->path);
    *table_ptr= table->next;			// Unlink table from list
    close_temporary(table,0);
    *fn_ext(path)=0;				// Remove the .frm extension
    ha_create_table(path, &create_info,1);
unknown's avatar
unknown committed
511
    // We don't need to call invalidate() because this table is not in cache
512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533
    if ((error= (int) !(open_temporary_table(thd, path, table_list->db,
					     table_list->real_name, 1))))
      (void) rm_temporary_table(table_type, path);
    DBUG_RETURN(error ? -1 : 0);
  }

  (void) sprintf(path,"%s/%s/%s%s",mysql_data_home,table_list->db,
		 table_list->real_name,reg_ext);
  fn_format(path,path,"","",4);

  if (!dont_send_ok)
  {
    db_type table_type;
    if ((table_type=get_table_type(path)) == DB_TYPE_UNKNOWN)
    {
      my_error(ER_NO_SUCH_TABLE, MYF(0), table_list->real_name);
      DBUG_RETURN(-1);
    }
    if (!ha_supports_generate(table_type))
    {
      /* Probably InnoDB table */
      DBUG_RETURN(mysql_delete(thd,table_list, (COND*) 0, (ORDER*) 0,
unknown's avatar
unknown committed
534
			       HA_POS_ERROR, TL_WRITE, 0));
535 536 537 538 539 540 541 542
    }
    if (lock_and_wait_for_table_name(thd, table_list))
      DBUG_RETURN(-1);
  }

  bzero((char*) &create_info,sizeof(create_info));
  *fn_ext(path)=0;				// Remove the .frm extension
  error= ha_create_table(path,&create_info,1) ? -1 : 0;
unknown's avatar
unknown committed
543
  query_cache.invalidate(table_list); 
544

unknown's avatar
unknown committed
545
  if (!dont_send_ok)
546
  {
unknown's avatar
unknown committed
547
    if (!error)
548
    {
unknown's avatar
unknown committed
549 550 551 552 553 554 555
      mysql_update_log.write(thd,thd->query,thd->query_length);
      if (mysql_bin_log.is_open())
      {
	Query_log_event qinfo(thd, thd->query);
	mysql_bin_log.write(&qinfo);
      }
      send_ok(&thd->net);		// This should return record count
556
    }
unknown's avatar
unknown committed
557
    unlock_table_name(thd, table_list);
558
  }
unknown's avatar
unknown committed
559 560
  else if (error)
    unlock_table_name(thd, table_list);
561 562
  DBUG_RETURN(error ? -1 : 0);
}