Bug #25264 ON DELETE triggers fire on TRUNCATE of InnoDB tables
Submitted: 24 Dec 2006 12:06 Modified: 25 Dec 2006 9:34
Reporter: Mark Leith Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.30 OS:Any (Any)
Assigned to: CPU Architecture:Any
Tags: innodb, triggers, truncate

[24 Dec 2006 12:06] Mark Leith
Description:
ON DELETE triggers fire on a TRUNCATE of InnoDB tables, whether they have foreign keys on them or not. 

The manual states thus:

"For InnoDB before version 5.0.3, TRUNCATE TABLE is mapped to DELETE, so there is no difference. Starting with MySQL 5.0.3, fast TRUNCATE TABLE is available. However, the operation is still mapped to DELETE if there are foreign key constraints that reference the table."

So, one might expect them to fire if the tables had foreign keys relating to them, however, they fire whether foreign keys are present or not. The manual also states:

"Since truncation of a table does not make any use of DELETE, the TRUNCATE  statement does not invoke ON DELETE triggers."

So ON DELETE triggers should not fire at all.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  i INT, 
  j INT,
  PRIMARY KEY (i),
  KEY (j)
) ENGINE = InnoDB;

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
  i INT, 
  j INT,
  PRIMARY KEY (i),
  FOREIGN KEY (j) REFERENCES t1 (j)
) ENGINE = InnoDB;  

DROP TABLE IF EXISTS t3;
CREATE TABLE t3 (
  i INT, 
  j INT
) ENGINE = InnoDB;
  
INSERT INTO t1 
  VALUES (1,2),(2,4),(3,6),(4,8),(5,10);
  
INSERT INTO t2
  VALUES (1,2),(2,4),(3,6),(4,8),(5,10);
  
INSERT INTO t3
  VALUES (1,2),(2,4),(3,6),(4,8),(5,10);

DROP TABLE IF EXISTS delete_log;
CREATE TABLE delete_log (
  tablename CHAR(2),
  i INT,
  j INT,
  before_after CHAR(6)
);

DELIMITER //
CREATE TRIGGER t1_before_delete BEFORE DELETE ON t1
  FOR EACH ROW
BEGIN
  INSERT INTO delete_log 
    VALUES ('t1', OLD.i, OLD.j, 'BEFORE');
END//

CREATE TRIGGER t1_after_delete AFTER DELETE ON t1
  FOR EACH ROW
BEGIN
  INSERT INTO delete_log 
    VALUES ('t1', OLD.i, OLD.j, 'AFTER');
END//

CREATE TRIGGER t2_before_delete BEFORE DELETE ON t2
  FOR EACH ROW
BEGIN
  INSERT INTO delete_log 
    VALUES ('t2', OLD.i, OLD.j, 'BEFORE');
END//

CREATE TRIGGER t2_after_delete AFTER DELETE ON t2
  FOR EACH ROW
BEGIN
  INSERT INTO delete_log 
    VALUES ('t2', OLD.i, OLD.j, 'AFTER');
END//

CREATE TRIGGER t3_before_delete BEFORE DELETE ON t3
  FOR EACH ROW
BEGIN
  INSERT INTO delete_log 
    VALUES ('t3', OLD.i, OLD.j, 'BEFORE');
END//

CREATE TRIGGER t3_after_delete AFTER DELETE ON t3
  FOR EACH ROW
BEGIN
  INSERT INTO delete_log 
    VALUES ('t3', OLD.i, OLD.j, 'AFTER');
END//

DELIMITER ;

TRUNCATE TABLE t2;

SELECT * FROM delete_log;

TRUNCATE TABLE t1;

SELECT * FROM delete_log;

TRUNCATE TABLE t3;

SELECT * FROM delete_log;

Suggested fix:
Make sure that triggers do not fire at all for TRUNCATE on an InnoDB table, in any circumstance.
[25 Dec 2006 9:34] Dmitry Lenev
Hi, Mark!

This bug is duplicate of bug #23556.