Bug #10482 Unable to change lengths of binary columns associated with foreign key

• Submitted: 9 May 2005 15:26
• Modified: 11 May 2005 7:43
• Reporter: Bill Jones
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 4.0.17
• OS: Windows (Windows XP)
• CPU Architecture: Any

[9 May 2005 15:26] Bill Jones

Description:
I'm attempting to reduce the length of a binary column that exists in a foreign key to a non-binary column.  In attempting to do this I'm receiving the following error:

ERROR 1025: Error on rename of '.\test\#sql-a10_5d' to '.\test\pet' (errno: 150)

This is a critical error because we are planning on moving to 4.1.11 with the next release of our software.  In 4.1.11 there are restrictions regarding the length of foreign keys which precipitated the need to reduce the length of certain columns existing in foreign keys.  Without fixing this at 4.0.17 we won't be able to upgrade our existing customer sites up to 4.1.11.

How to repeat:
IThis is a simple example of the inability to reduce the lengths of columns:

CREATE TABLE `animal` (
  `id` varchar(255) binary NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB;

INSERT INTO animal VALUES ('dog');

CREATE TABLE `pet` (
  `animal_id` varchar(255) binary NOT NULL default '',
  `pet_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`animal_id`),
  CONSTRAINT `animal_id_fk` FOREIGN KEY (`animal_id`) REFERENCES `animal` (`id`)
)  TYPE=InnoDB;

INSERT INTO pet VALUES ('dog', 'Rover');

alter table `pet` modify animal_id varchar(100) not null;

[9 May 2005 17:05] MySQL Verification Team

mysql> use han;
Database changed
mysql> CREATE TABLE `animal` (
    ->   `id` varchar(255) binary NOT NULL default '',
    ->   PRIMARY KEY  (`id`)
    -> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO animal VALUES ('dog');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> CREATE TABLE `pet` (
    ->   `animal_id` varchar(255) binary NOT NULL default '',
    ->   `pet_name` varchar(255) NOT NULL,
    ->   PRIMARY KEY  (`animal_id`),
    ->   CONSTRAINT `animal_id_fk` FOREIGN KEY (`animal_id`) REFERENCES `animal`
    -> (`id`)
    -> )  TYPE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> INSERT INTO pet VALUES ('dog', 'Rover');
Query OK, 1 row affected (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table `pet` modify animal_id varchar(100) not null;
ERROR 1025: Error on rename of './han/#sql-4f5_2' to './han/pet' (errno: 150)
mysql>

[11 May 2005 7:43] Heikki Tuuri

Bill,

the collation order of the referenced and the referencing column must be the same. You cannot change VARCHAR ... BINARY to an ordinary VARCHAR because the collation order would be different.

Regards,

Heikki