How to debug mysql foreign key constraint, ON DELETE CASCADE not deleting rows from Child table on production environment

Question

I have defined 2 tables and a foriegn key constraint between them as follows:

| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_master_id` int(11) NOT NULL,
  `user_type_id` int(11) NOT NULL,
  `user_group_id` int(11) NOT NULL,
  `user_type_code` char(1) NOT NULL,
  `membership_number` varchar(40) NOT NULL,
  `password` varchar(60) NOT NULL,
  `email` varchar(200) NOT NULL,
  `isd` varchar(10) NOT NULL,
  `mobile` varchar(20) NOT NULL,
  `passenger_id` int(11) NOT NULL,
  `added_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `added_by` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `account_master_id` (`account_master_id`),
  CONSTRAINT `acMaster_to_user` FOREIGN KEY (`account_master_id`) REFERENCES `account_master` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=189 DEFAULT CHARSET=utf8 |


user_oauth | CREATE TABLE `user_oauth` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `service` varchar(30) NOT NULL,
  `auth_id` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `auto_share` tinyint(4) NOT NULL,
  `photo` varchar(255) NOT NULL,
  `auth_token_short` varchar(255) DEFAULT NULL,
  `auth_details` text NOT NULL,
  `device_type` varchar(60) NOT NULL,
  `login_date` datetime NOT NULL,
  `login_ip` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user` (`user_id`),
  CONSTRAINT `user_to_oauth` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=latin1 |

on deleting a row from users table, deletes corresponding entries from user_oauth table in LOCAL and Staging environment. but however same thing is not working in PRODUCTION environment. I want to know how can i debug this.

update:

• both tables are innodb
• running mysql in strict mode

Answer 1

The reasons why foreign keys might not work are:

• they are not supported (e.g. on MyISAM-tables)
• they are disabled

MySQL allows you to disable foreign key contraints by setting the system variable foreign_key_checks to 0. This will allow you to violate all foreign key constraints (e.g. delete a parent or add parentless childs). But it will in turn also disable related features like cascades, which are there to automatically prevent specific constraint violations (by e.g. deleting the children) - which of course won't occur anymore if the option is disabled.

The idea is to help you with some administration tasks, e.g. importing data when the referenced data is not yet there, but should usually not be used during normal operation. If the setting reappears, you might want to check your apps if one is setting this option by accident, as it is enabled after every server start by default and has to be disabled explicitely.

You can check the current setting by e.g. using

select @@foreign_key_checks;

You can use

SET foreign_key_checks = 1;

to enable it again, but be aware that it will not check your current data:

Setting foreign_key_checks to 1 does not trigger a scan of the existing table data. Therefore, rows added to the table while foreign_key_checks=0 will not be verified for consistency.

So you will have to check and fix it yourself. You can do it either before or after you enabled the setting again, although it might be easier to do it before. To trigger a recheck, you can and should drop and recreate the foreign key, just to make sure everything is consistent now.