MySQL Bugs: #49896: Foreign keys and deletion problems

Bug #49896	Foreign keys and deletion problems
Submitted:	23 Dec 2009 17:14	Modified:	19 Jan 2010 15:01
Reporter:	Lig Isler-Turmelle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Server	Severity:	S2 (Serious)
Version:	2.1.0.1096	OS:	Any
Assigned to:	Sloan Childers	CPU Architecture:	Any

Description:
When deleting a user with foreign key references in other tables, you are not deleting from the other tables first or using ON DELETE CASCADE.  This leads to foreign key errors and prevents you from deleting the user.

Example foreign key error found:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
091215 9:23:10 Transaction:
TRANSACTION 0 1288242405, ACTIVE 0 sec, process no 8968, OS thread id 1099123024 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
10 lock struct(s), heap size 3024, 26 row lock(s), undo log entries 12
MySQL thread id 96048, query id 201845240 localhost 127.0.0.1 service_manager updating
delete from users where user_id=3
Foreign key constraint fails for table `mem`.`rule_alarms`:
,
CONSTRAINT `FKC933A2E53FB96BE` FOREIGN KEY (`fixed_by_user_id`) REFERENCES `users` (`user_id`)
Trying to delete or update in parent table, in index `PRIMARY` tuple:
DATA TUPLE: 6 fields;
*data*

But in child table `mem`.`rule_alarms`, in index `FKC933A2E53FB96BE`, there is a record:
PHYSICAL RECORD: *data*

How to repeat:
unknown.  Found looking at the code and investigating the cause of the foreign key error in InnoDB.

Suggested fix:
handle your foreign key constraints better

Only workaround I can think of is to manually enter the MEM database and delete the relevant rows of the foreign key constraints making sure nothing else references those rows and then go through the UI for the user deletion.

workable - but very ugly and prone to the possibility of mistakes.

work around in detail:

1) log into the MEM repository (access information in the configuration_report.txt)
2) in the repository issue the following commands:

mysql> use mem
mysql> mysql> select user_id from users where name='dummy'; #replace dummy with user name
+---------+
| user_id |
+---------+
| 3       | <--- we will use this value in the next command
+---------+
1 row in set (0.00 sec)

mysql> update rule_alarms set fixed_by_user_id = NULL where fixed_by_user_id = 3;

# Again replace the fixed_by_user_id value with the user_id value given earlier

3) delete the user through the User Interface (Settings -> Manage Users).

4) restart the monitor
shell> ./mysqlmonitorctl.sh restart

The restart of the Monitor is to clear the cache for any alarms/rules or events the user was associated with.

Andy Bang writes: 
Accidentally set to "Needs Review"

Sloan Childers writes: 
fixed in 2.2, attempting to handle migration in 2.1 which didn't leave any schema revisions open

Sloan Childers writes: 
patches pushed to both 2.1.1 and 2.2 trees

Keith Russell writes: 
Patch installed in versions => 2.1.1.1139.

Marcos Palacios writes: 
Verified fixed in Monitor build 2.1.1.1140.

A note has been added to the 2.1.1 and 2.2.0 changelog.