Description:
We mapped two objects in a one-to-many relationship to two rows (parent & child). The parent has a count column so it always knows the number of child rows it has without invoking an extra query. To accomplish this, we added triggers on the various CUD operations, so when a new row gets inserted, the count in the parent goes up by one, and when deleting one, it goes down by 1. This works as expected on an InnoDB table.
However, when deploy our application on our production MySQL cluster, the BEFORE DELETE trigger no longer seemed to fire in certain cases. Further investigation revealed that when the trigger fires on a BEFORE delete, in certain cases no further columns values were available besides the PK. This only happened during a delete on a PK, and not on another column.
Summary:
In the BEFORE DELETE trigger on an NDB table, the OLD variable does not have any accessible properties (column values) besides the PK, when a delete by PK is done.
How to repeat:
#####################################################
# INNODB - works as expected
#####################################################
create table child (id bigint (11) NOT NULL AUTO_INCREMENT , parent_id bigint (11) NOT NULL , PRIMARY KEY ( id)) ENGINE=INNODB;
create table parent (id bigint (11) NOT NULL AUTO_INCREMENT , count int (4) NOT NULL DEFAULT '0', PRIMARY KEY ( id)) ENGINE=INNODB;
################# trigger on child ##################
DELIMITER $$
DROP TRIGGER `test`.`child_DELETE`$$
CREATE TRIGGER `test`.`child_DELETE` BEFORE DELETE on `test`.`child`
FOR EACH ROW BEGIN
IF old.parent_id IS NOT NULL THEN
UPDATE parent SET count = count - 1 WHERE parent.id = OLD.parent_id;
END IF;
END$$
DELIMITER ;
######################################################
insert into parent(count) VALUES (2);
insert into child (parent_id) VALUES (1);
insert into child (parent_id) VALUES (1);
mysql> select * from parent;
+----+-------+
| id | count |
+----+-------+
| 1 | 2 |
+----+-------+
mysql> select * from child;
+----+-----------+
| id | parent_id |
+----+-----------+
| 1 | 1 |
| 2 | 1 |
+----+-----------+
# delete by PK
delete from child where id=1;
mysql> select * from parent;
+----+-------+
| id | count |
+----+-------+
| 1 | 1 |
+----+-------+
#delete by another column
delete from child where parent_id=1;
mysql> select * from parent;
+----+-------+
| id | count |
+----+-------+
| 1 | 0 |
+----+-------+
#####################################################
# NDB Cluster
#####################################################
create table child2 (id bigint (11) NOT NULL AUTO_INCREMENT , parent_id bigint (11) NOT NULL , PRIMARY KEY ( id)) ENGINE=NDBCLUSTER;
create table parent2 (id bigint (11) NOT NULL AUTO_INCREMENT , count int (4) NOT NULL DEFAULT '0', PRIMARY KEY ( id)) ENGINE=NDBCLUSTER;
################# trigger on child2 #################
DELIMITER $$
DROP TRIGGER `test`.`child2_DELETE`$$
CREATE TRIGGER `test`.`child2_DELETE` BEFORE DELETE on `test`.`child2`
FOR EACH ROW BEGIN
IF old.parent_id IS NOT NULL THEN
UPDATE parent2 SET count = count - 1 WHERE parent2.id = OLD.parent_id;
END IF;
END$$
DELIMITER ;
#####################################################
insert into parent2(count) VALUES (2);
insert into child2 (parent_id) VALUES (1);
insert into child2 (parent_id) VALUES (1);
mysql> select * from parent2;
+----+-------+
| id | count |
+----+-------+
| 1 | 2 |
+----+-------+
mysql> select * from child2;
+----+-----------+
| id | parent_id |
+----+-----------+
| 1 | 1 |
| 2 | 1 |
+----+-----------+
# delete by PK
delete from child2 where id=1; #does NOT work
mysql> select * from parent2;
+----+-------+
| id | count |
+----+-------+
| 1 | 2 |
+----+-------+
#delete by another column
delete from child2 where parent_id=1; #works fine
mysql> select * from parent2;
+----+-------+
| id | count |
+----+-------+
| 1 | 1 |
+----+-------+
The problem seems to be located in the trigger where the PK is the only column we can access (the others are 0 or max(int) or '' ..) when deleting a row by PK.
