Description:
Issue with transition of VARCHAR Oracle type to MySQL UTF-8 length for VARCHAR. Oracle used Unicode UTF-8 (AL32UTF8) as the database character set. Let us say the variable x was declared as CHAR(3), which resulted in an allocation of three bytes. When the single-byte value a was assigned to x, the other two bytes were padded with spaces (a space is also a single byte), and thus the resulting string was three characters long. When the two-byte character was assigned to x, only one byte remained to be padded with a single space. The result? A two-character-long string that happened to be using three bytes of storage. In our case (migrating from Oracle to MySQL), Oracle defines it's VARCHAR length in bytes where as MySQL defines the length in Char. When the migration script is created a one for one mapping occurs which is incorrect. Also the the keyword BINARY is unnecessarily added.
How to repeat:
Example migration error:
Oracle Table Description:
>>>
SQL> desc class_of_service_parameter_tbl;
Name Null? Type
----------------------------------------- -------- ----------------------------
CLASS_OF_SERVICE_KEY NOT NULL VARCHAR2(256)
PARAMETER_KEY NOT NULL VARCHAR2(256)
PARAMETER_VALUE VARCHAR2(256)
>>>
MySQL Migration Tool Script:
>>>
CREATE TABLE `agw`.`class_of_service_parameter_tbl` (
`class_of_service_key` VARCHAR(256) BINARY NOT NULL,
`parameter_key` VARCHAR(256) BINARY NOT NULL,
`parameter_value` VARCHAR(256) BINARY NULL,
PRIMARY KEY (`class_of_service_key`, `parameter_key`),
INDEX `i1_class_of_service_parameter` (`class_of_service_key`(256)),
INDEX `i2_class_of_service_parameter` (`parameter_key`(256))
) ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;
>>>
