How to resolve ERROR : #1977 - Cannot convert 'utf8' character 0xD8AD to 'latin1'

Question

I am working in phpmyadmin where I have Arabic values in name columns like

کلب الرقيّة علی العزیزبوریï

And I want to convert them to proper form.

I tried these queries and it converted most of the records in the table

ALTER DATABASE alfeker_book CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE guestbook CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
UPDATE guestbook SET name = CONVERT(BINARY CONVERT(name USING latin1) USING utf8mb4);

And also tried

UPDATE guestbook SET name = CONVERT(BINARY CONVERT(name USING binary) USING utf8mb4);

but few of them remain unchanged and give this error

#1977 - Cannot convert 'utf8' character 0xD8AD to 'latin1'

I also tried this query:

SELECT id,name, CONVERT(BINARY CONVERT(name USING latin1) USING utf8mb4) AS converted_name FROM guestbook;

Which works fine and show the non converted characters perfectly. But when I use the update query it doesn't work.

Answer 1

That's Mojibake or "double encoding" of Arabic text.

There are several ways of fixing the data. Only one makes it better; the rest make it worse.

Please read Trouble with UTF-8 characters; what I see is not what I stored and follow the suggestions about getting the HEX of what is in the database, plus SHOW CREATE TABLE of the current schema definition. Also provide SHOW VARIABLES LIKE 'char%'; and the connection parameters.

And please back up to before attempting the various ALTERs and CONVERTs.

With the HEX, I can probably pick the proper conversion.