General error: 3988 Conversion from collation utf8_unicode_ci into utf8mb4_0900_ai_ci impossible for parameter

Question

I'm trying to save a string containing html of an email in a text type column. but i'm getting this error. I failed finding where's the incompatible caractere. There is any sanitizing method that i can use to convert all utf8_unicode_ci into utf8mb4_0900_ai_ci?

Answer 1

My specific problem was actually caused by PHP substr.

echo substr('ããããã', 0, 5);
//ãã�

It use utf8_encode, get the substring and then use utf8_decode. In this example the string "ããããã" becomes "ããããã" after the internal call to utf8_encode, then is cropped to "ããÃ" by substr, and when utf8_encode is aplied it endup with "ãã�".

I solved this using the Laravel helper substr instead of the php one.

Answer 2

You can convert the text before inserting to the right collation

SELECT CONVERT('abc' USING utf8mb4)  COLLATE  utf8mb4_0900_ai_ci;