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.
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.