Bug #25816 Foreign key drop fails on 64bit linux when using innodb_file_per_table
Submitted: 24 Jan 2007 10:29 Modified: 30 Jan 2007 12:43
Reporter: Johan Idrén Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.32 OS:Linux (linux x86_64)
Assigned to: Assigned Account CPU Architecture:Any
Tags: innodb

[24 Jan 2007 10:29] Johan Idrén
Description:
Description:
When using innodb_file_per_table on 64bit linux, under some circumstances
dropping a foreign key fails.

How to repeat:
CREATE TABLE `parent_table` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
CREATE TABLE `child_table` (
  `id` int(11) NOT NULL auto_increment,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_child_category_id` (`category_id`),
  CONSTRAINT `fk_child_category_id` FOREIGN KEY (`category_id`)
REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE child_table DROP FOREIGN KEY fk_child_category_id;

Workaround:
ALTER TABLE child_table DROP KEY fk_child_category_id, DROP FOREIGN KEY
fk_child_category_id;
OR
ALTER TABLE child_table ADD COLUMN test text null, DROP FOREIGN KEY
fk_child_category_id; ALTER TABLE child_table DROP COLUMN test;

Also if the table name or foreign key name is shorter then dropping foreign key
works fine too.
[24 Jan 2007 12:30] Johan Idrén
There is an error in the recreation instruction.

Revised version.

CREATE TABLE `child_table` (
  `id` int(11) NOT NULL auto_increment,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_child_category_id` (`category_id`),
  CONSTRAINT `fk_child_category_id` FOREIGN KEY (`category_id`)
REFERENCES `parent_table` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE child_table DROP FOREIGN KEY fk_child_category_id;
[24 Jan 2007 16:49] Heikki Tuuri
Could this be the same as http://bugs.mysql.com/bug.php?id=25596?
[25 Jan 2007 6:34] Johan Idrén
I'm not sure, however the output from the InnoDB monitor is different.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
070123 16:07:58 Error in foreign key constraint of table test/#sql-45fc_c:
foreign key fk_child_category_id:
Cannot resolve column name close to:
,
------------

And also the circumstances when this happens (only on 64bit binaries with innodb_file_per_table enabled) makes me believe it's a different bug.
[25 Jan 2007 13:33] Heikki Tuuri
Johan,

I guess this is the same bug as http://bugs.mysql.com/bug.php?id=25596

Please recompile mysqld with the patch suggested there and report if you still can repeat the bug.

Regards,

Heikki
[30 Jan 2007 12:43] Johan Idrén
Tested with bugfix from bug #25596 and testcase completed successfully.

Marking this as duplicate.