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

[23 Oct 2006 17:20] Matthew Lord
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;
[24 Oct 2006 12:20] Heikki Tuuri
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
[24 Oct 2006 15:48] Calvin Sun
Change back to Server.
[2 Nov 2006 23:33] Konstantin Osipov
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.
[9 Nov 2006 22:11] Konstantin Osipov
This bug will be solved in scope of WL#2825: Triggers: enable or disable.
[10 Nov 2006 22:24] Konstantin Osipov
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.
[21 Nov 2006 8:12] Bugs System
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.
[21 Nov 2006 8:25] Bugs System
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
[25 Dec 2006 9:35] Dmitry Lenev
Bug#25264 was marked as duplicate of this bug.
[3 Feb 2007 6:05] Igor Babaev
The fix has been pushed into 5.0.36, 5.1.16-beta main trees.
[8 Feb 2007 17:28] Paul Dubois
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.