MySQL Bugs: #25816: Foreign key drop fails on 64bit linux when using innodb_file_per

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

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.

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;

Could this be the same as http://bugs.mysql.com/bug.php?id=25596?

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.

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

Tested with bugfix from bug #25596 and testcase completed successfully.

Marking this as duplicate.