MySQL foreign key cascade delete does not work, completely deadlock

Question

I read a bunch of other threads, but I'm still at a dead end. I created two very simple tables as a health check and could not get them to cascade delete, so we need help at this point.

CREATE TABLE `test1` ( `test1_ID` int(11) NOT NULL AUTO_INCREMENT, `test1_name` char(15) DEFAULT NULL, PRIMARY KEY (`test1_ID`), UNIQUE KEY `test1_ID_UNIQUE` (`test1_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `test2` ( `test2_ID` int(11) NOT NULL AUTO_INCREMENT, `test2_FK_test1` int(11) NOT NULL, `test2_name` char(15) DEFAULT NULL, PRIMARY KEY (`test2_ID`), UNIQUE KEY `test2_ID_UNIQUE` (`test2_ID`), KEY `IDX_test2_FK_test1` (`test2_FK_test1`), CONSTRAINT `FK_test2__test1` FOREIGN KEY (`test2_FK_test1`) REFERENCES `test1` (`test1_ID`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

Table data used for testing

 Test 1 Table Data: +----------+------------+ | test1_ID | test1_name | +----------+------------+ | 1 | test1 r1 | | 2 | test1 r2 | +----------+------------+ Test 2 Table Data: +----------+----------------+----------------+ | test2_ID | test2_FK_test1 | test2_name | +----------+----------------+----------------+ | 1 | 1 | Test2 R1 - FK1 | | 2 | 1 | Test2 R2 - FK1 | | 3 | 1 | Test2 R3 - FK1 | +----------+----------------+----------------+ Insert statements: INSERT INTO `test1` VALUES (1,'test1 r2'),(2,'test1 r2'); INSERT INTO `test2` VALUES (1,1,'Test2 R1 - FK1'),(2,1,'Test2 R2 - FK1'),(3,1,'Test2 R3 - FK1'); 

If I delete the first row from table test1, nothing happens with the data in table test2.

 DELETE FROM test1 WHERE test1_ID = 1; 

Answer 1

This turned out to be a bug in the version of MySQL I used, 5.5.28. I just upgraded to 5.6.13 and now everything works. Thanks for the help.

Answer 2

Make sure mysql "foreign key check" is enabled:

 select @@FOREIGN_KEY_CHECKS; set FOREIGN_KEY_CHECKS=1; 

Answer 3

I found that calling PDO::exec('SET FOREIGN_KEY_CHECKS=1') necessary for the PDO to follow the ON DELETE and ON UPDATE actions. For some reason, foreign key restrictions are disabled by default on my system.