Bug #8825 Fail to remove foreign key constraint
Submitted: 26 Feb 2005 22:39 Modified: 27 Feb 2005 6:59
Reporter: Fritjof Andersson Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0.22 OS:Linux (Linux)
Assigned to: Jani Tolonen CPU Architecture:Any

[26 Feb 2005 22:39] Fritjof Andersson 
Description:
When trying to remove a foreign key constraint from a InnoDB table I get a "Lock wait timeout exceeded"

How to repeat:
#The create of the table:

CREATE TABLE `CORE_designArea` (
  `CORE_designArea_areaName` varchar(100) default '',
  `CORE_design_id` int(11) NOT NULL default '0',
  `CORE_module_id` int(11) NOT NULL default '0',
  `CORE_moduleModes_modeId` int(11) NOT NULL default '0',
  `CORE_designArea_isMain` tinyint(1) NOT NULL default '0',
  `CORE_designArea_areaId` int(11) NOT NULL auto_increment,
  `CORE_designArea_isProtected` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`CORE_designArea_areaId`),
  UNIQUE KEY `designId_areaId_UNIQUE` (`CORE_design_id`,`CORE_designArea_areaId`),
  UNIQUE KEY `UNIQUE_areaName` (`CORE_designArea_areaName`),
  KEY `designId_INDEX` (`CORE_design_id`),
  KEY `areaId_INDEX` (`CORE_designArea_areaId`),
  KEY `moduleId_modeId_INDEX` (`CORE_module_id`,`CORE_moduleModes_modeId`),
  CONSTRAINT `CORE_designArea_ibfk_16` FOREIGN KEY (`CORE_design_id`) REFERENCES `CORE_design` (`CORE_design_id`) ON DELETE CASCADE,
  CONSTRAINT `CORE_designArea_ibfk_17` FOREIGN KEY (`CORE_module_id`, `CORE_moduleModes_modeId`) REFERENCES `CORE_moduleModes` (`CORE_moduleModes_moduleId`, `CORE_moduleModes_modeId`) ON DELETE CASCADE
) TYPE=InnoDB COMMENT='The default module in page/area; InnoDB free: 3072 kB;'; 

# The altering query that is supposed to drop the foreign key but fails

ALTER TABLE CORE_designArea DROP FOREIGN KEY CORE_designArea_ibfk_17

Suggested fix:
To quote what David Axmark said to me a few months ago "Just because someone is good at using a database system, doesn't mean that person is good at writing one", so I leave this up to you. ;-)
[27 Feb 2005 6:59] Heikki Tuuri 
Fritjof,

MySQL does a full recreate of the table in ALTER. There is probably some other user who has locked rows in the tables that this one references. You can use SHOW INNODB STATUS or innodb_lock_monitor to find out.

Workaround: do not allow other users to have uncommitted transactions on relevant tables.

Jani Tolonen is working on an intelligent ALTER TABLE. That will remove this problem in 5.1.

Regards,

Heikki