MySQL utf8mb4 foreign key error

Question

A few days ago we moved the database from utf8 to utf8mb4. Due to migration, most of our primary keys have been updated from VARCHAR(255)to VARCHAR(191)due to extra bytes per character.

The problem is that now we can’t add new FOREIGN keys to new tables that reference existing ones. We get the following error in MySQL Workbench:

"There are no candidate columns with a compatible type for table.id in the referenced table. Hint: the source column is of type VARCHAR (191), PK of the referenced table is of type VARCHAR (191)."

If we try to add a foreign key through the console, we get:

LAST FOREIGN KEY ERROR

2014-02-13 10:27:51 126bb3000 Foreign key constraint error on table / # sql-159_2b2 table: foreign key table (fk_id) (id): cannot find the index in the referenced table where the referenced columns are displayed as first columns or column types in the table and the reference table is not compliant.

However, both types are defined as VARCHAR(191), as you can see in the error reported.

What are we missing?

Answer 1

Problem and solution detected. It seems that when using utf8mb4 MYSQL has some problems when comparing during sorting.

, MYSQL utf8mb4 , , .

, A, A utf8mb4 - ( DB ). p ( "Default Default", "utf8mb4 - " ) x B, B : utf8mb4 - , x utf8mb4_unicode_ci, :

- table.id. : VARCHAR (191), PK - VARCHAR (191).

, , - .

, p, , :

ALTER TABLE `database`.`A` CHARACTER SET = utf8mb4 ;
ALTER TABLE `database`.`A` CHANGE COLUMN `p` `p` VARCHAR(191) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL  

:

ALTER TABLE `database`.`A` 
ADD CONSTRAINT `FKD84ACC0C2200B55`
FOREIGN KEY (`p` )
REFERENCES `database`.`B` (`x` )
ON DELETE CASCADE
ON UPDATE CASCADE;