MySQL: unique key clieconninst_namclaconins_uix is too large for utf8mb4

[Ghada El-Zoghbi]

Mahara: 20.04.1
OS: Linux
DB: MySQL 5.7
Browser: N/A

We have a client with a MySQL database. It was originally utf8 but they found they could not save emojis in text fields. We changed collation of the DB to utf8mb4 to allow the saving of emojis.

But, this created an issue with the foreign key clieconninst_namclaconins_uix on the table client_connections_institution:

This unique key is too large for utf8mb4:
UNIQUE KEY `clieconninst_namclaconins_uix` (`name`,`class`,`connection`,`institution`)

* name (255) + class (255) + connection(255) + institution(255) = 1020 * 4 = 4080 bytes

but the maximum MySql key length is 3072.

The field lengths need to be reduced to allow utf8mb4 collation in MySQL (one possible solution).

SUMMARY

This foreign key fails:

ALTER TABLE `client_connections_institution` ADD CONSTRAINT `clieconninst_ins_fk` FOREIGN KEY (`institution`) REFERENCES `institution` (`name`);

because:
client_connections_institution and institution use different collations.

institution is utf8mb4 and client_connections_institution is utf8

because:

we could not convert client_connections_institution to utf8mb4.