MySQL Bugs: #45290: Cant convert innodb tables from latin1 to utf8 if there is a varchar foreign key

Bug #45290	Cant convert innodb tables from latin1 to utf8 if there is a varchar foreign key
Submitted:	3 Jun 2009 9:35	Modified:	3 Jun 2009 12:54
Reporter:	James b	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.75-0ubuntu10.2, 4.1, 5.0, 5.1, 6.0 bzr	OS:	Other (Ubuntu)
Assigned to:		CPU Architecture:	Any

Description:
Cant convert innodb tables from latin1 to utf8 if there is a varchar foreign key

How to repeat:
This Works:

CREATE TABLE IF NOT EXISTS `CollectionInfo` (
  `CollectionName` varchar(255) NOT NULL,
  PRIMARY KEY  (`CollectionName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `CollectionInfo` VALUES('Product');

CREATE TABLE IF NOT EXISTS `CollectionFieldInfo` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `CollectionName` varchar(255) NOT NULL,
  `FieldName` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `CollectionName` (`CollectionName`,`FieldName`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=71 ;

INSERT INTO `CollectionFieldInfo` VALUES(3, 'Product', 'Title');
INSERT INTO `CollectionFieldInfo` VALUES(4, 'Product', 'Price');
INSERT INTO `CollectionFieldInfo` VALUES(5, 'Product', 'Status');

ALTER TABLE `CollectionFieldInfo`
  ADD CONSTRAINT `cfi_cn` FOREIGN KEY (`CollectionName`) REFERENCES `CollectionInfo` (`CollectionName`) ON DELETE CASCADE;

And This Works:

CREATE TABLE IF NOT EXISTS `CollectionInfo` (
  `CollectionName` varchar(255) NOT NULL,
  PRIMARY KEY  (`CollectionName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `CollectionInfo` VALUES('Product');

CREATE TABLE IF NOT EXISTS `CollectionFieldInfo` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `CollectionName` varchar(255) NOT NULL,
  `FieldName` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `CollectionName` (`CollectionName`,`FieldName`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=71 ;

INSERT INTO `CollectionFieldInfo` VALUES(3, 'Product', 'Title');
INSERT INTO `CollectionFieldInfo` VALUES(4, 'Product', 'Price');
INSERT INTO `CollectionFieldInfo` VALUES(5, 'Product', 'Status');

ALTER TABLE CollectionInfo CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

But this one:

CREATE TABLE IF NOT EXISTS `CollectionInfo` (
  `CollectionName` varchar(255) NOT NULL,
  PRIMARY KEY  (`CollectionName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `CollectionInfo` VALUES('Product');

CREATE TABLE IF NOT EXISTS `CollectionFieldInfo` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `CollectionName` varchar(255) NOT NULL,
  `FieldName` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `CollectionName` (`CollectionName`,`FieldName`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=71 ;

INSERT INTO `CollectionFieldInfo` VALUES(3, 'Product', 'Title');
INSERT INTO `CollectionFieldInfo` VALUES(4, 'Product', 'Price');
INSERT INTO `CollectionFieldInfo` VALUES(5, 'Product', 'Status');

ALTER TABLE `CollectionFieldInfo`
  ADD CONSTRAINT `cfi_cn` FOREIGN KEY (`CollectionName`) REFERENCES `CollectionInfo` (`CollectionName`) ON DELETE CASCADE;

ALTER TABLE CollectionInfo CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

Says NOOOO!

#1025 - Error on rename of './testing/#sql-1d5f_200' to './testing/CollectionInfo' (errno: 150)

Oh yes, and can I put in a general request for more informative innodb error messages please? Thank you!

Thank you for the report.

This is expected, because "parent" and "child" columns have different collation, so they can not be linked as foreign keys.

Current workaround is:

SET foreign_key_checks = 0;
convert
SET foreign_key_checks = 1;

But you are right, it is not very convenient. So this problem can have potential resolution: change ALTER command, so it allows change both columns.

Regarding to the error message I believe we have such a feature request already.

Bug #24773 is feature request about error messages which I mentioned in previous comment.

Ok, that makes sense and the work around is OK for us. Thanks.

Also affects 5.6

Posted by developer:
 
The following content has been added to the ALTER TABLE documentation:
https://dev.mysql.com/doc/refman/5.6/en/alter-table.html

When foreign_key_checks is enabled, which is the default setting,
character set conversion is not permitted on tables that include a
character string column used in a foreign key constraint. The workaround
is to disable foreign_key_checks before performing the character set
conversion. You must perform the conversion on both tables involved in the
foreign key constraint before re-enabling foreign_key_checks. If you
re-enable foreign_key_checks after converting only one of the tables, an
ON DELETE CASCADE or ON UPDATE CASCADE operation could corrupt data in the
referencing table due to implicit conversion that occurs during these
operations (Bug #45290, Bug #74816).