MySQL Bugs: #23556: TRUNCATE TABLE still maps to DELETE

Bug #23556	TRUNCATE TABLE still maps to DELETE
Submitted:	23 Oct 2006 17:20	Modified:	8 Feb 2007 17:28
Reporter:	Matthew Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.26	OS:	Any (all)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	bfsm_2006_11_02, innodb, Q1, trigger, truncate

Description:
InnoDB is still mapping TRUNCATE TABLE foo; to DELETE * from FOO; although FAST TRUNCATE TABLE is supposed to be used when there are no referenced foreign keys.

How to repeat:
create table my_table (my_id int unsigned not null auto_increment primary key, my_data char(20) not null) Engine=InnoDB;

create table my_table_trigger_log (my_id int unsigned not null) Engine=InnoDB;

delimiter $

CREATE DEFINER='ecashtrigger'@'%' TRIGGER
`test_trigger_delete`
BEFORE DELETE ON
`my_table`
FOR EACH ROW
BEGIN
INSERT into my_table_trigger_log (my_id) VALUES(OLD.my_id);
END$

delimiter ;

insert into my_table (my_data) values('potato'), ('onion'), ('garlic'), ('pumpkin'), ('vampire');

select * from my_table;

select * from my_table_trigger_log;

truncate table my_table;

select * from my_table_trigger_log;

Matt,

sql_select.cc in 5.0:

"
  /*
    Test if the user wants to delete all rows and deletion doesn't have
    any side-effects (because of triggers), so we can use optimized
    handler::delete_all_rows() method.
  */
  if (!using_limit && const_cond && (!conds || conds->val_int()) &&
      !(specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)) &&
      !(table->triggers && table->triggers->has_delete_triggers()))
  {
    deleted= table->file->records;
    if (!(error=table->file->delete_all_rows()))
    {
      error= -1;                                // ok
      goto cleanup;
    }
"

"
 trunc_by_del:
  /* Probably InnoDB table */
  ulong save_options= thd->options;
  table_list->lock_type= TL_WRITE;
  thd->options&= ~(ulong) (OPTION_BEGIN | OPTION_NOT_AUTOCOMMIT);
  ha_enable_transaction(thd, FALSE);
  mysql_init_select(thd->lex);
  error= mysql_delete(thd, table_list, (COND*) 0, (SQL_LIST*) 0,
                      HA_POS_ERROR, LL(0), TRUE);
  ha_enable_transaction(thd, TRUE);
  thd->options= save_options;
  DBUG_RETURN(error);
}
"

The reason is that InnoDB uses the function mysql_delete() to do the TRUNCATE, and mysql_delete() does not use ::delete_all_rows() if there is a trigger on the table.

A possible fix is this:

  /*
    Test if the user wants to delete all rows and deletion doesn't have
    any side-effects (because of triggers), so we can use optimized
    handler::delete_all_rows() method.
    If reset_auto_increment == TRUE, then this is really a TRUNCATE, and
    we will delete all rows regardless of delete triggers on the table.
  */
  if (reset_auto_increment ||
      (!using_limit && const_cond && (!conds || conds->val_int()) &&
       !(specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)) &&
       !(table->triggers && table->triggers->has_delete_triggers())))
  {

I am reclassifying this as a MySQL Server bug, so that a MySQL employee can fix this in sql_delete.cc.

Thank you,

Heikki

Change back to Server.

We believe that TRUNCATE calls triggers rightfully and MyISAM should be fixed instead to call triggers just like InnoDB.

Not calling triggers on TRUNCATE is a security hole if triggers are used for security audit.

In order to provide users with fast TRUNCATE in presence of triggers, we shall introduce a way to disable triggers and require SUPER privilege to execute this action.

Your opinion on the subject is welcome.
Thanks.

This bug will be solved in scope of WL#2825: Triggers: enable or disable.

After discussions with the architecture team, the plan to fix this bug is the following:
 1) We implement fast TRUNCATE for InnoDB even if triggers are present. TRUNCATE should ignore triggers. This should be done in 5.0.

 2) TRUNCATE should require DROP privilege instead of DELETE privilege. This is an incompatible change and should be done in 5.1.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15602

ChangeSet@1.2315, 2006-11-21 10:11:43+02:00, gkodinov@macbook.gmz +3 -0
  Bug#23556: TRUNCATE TABLE still maps to DELETE
   This is the 5.0 part of the fix.
   Currently TRUNCATE command will not call
   delete_all_rows() in the handler (that implements
   the "fast" TRUNCATE for InnoDB) when there are
   triggers on the table.
   As decided by the architecture team TRUNCATE must
   use "fast" TRUNCATE even when there are triggers.
   Thus it must ignore the triggers. 
   Made TRUNCATE to ignore the triggers and call
   delete_all_rows() for all storage engines
   to maintain engine consistency.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15604

ChangeSet@1.2372, 2006-11-21 10:25:10+02:00, gkodinov@macbook.gmz +5 -0
  Bug #23556: TRUNCATE TABLE still maps to DELETE
   - TRUNCATE requires DROP privilege, not DELETE

Bug#25264 was marked as duplicate of this bug.

The fix has been pushed into 5.0.36, 5.1.16-beta main trees.

Noted in 5.0.36, 5.1.16 changelogs:

For an InnoDB table with any ON DELETE trigger, TRUNCATE TABLE mapped
to DELETE and activated triggers. Now a fast truncation occurs and
triggers are not activated. 

Noted in 5.1.16 changelog:

Incompatible change: TRUNCATE TABLE now requires the DROP privilege
rather than the DELETE privilege.

Also documented the 5.1 privilege change in the TRUNCATE TABLE
and privileges sections.