How to fix bug: #1977 - Unable to convert 'utf8' character 0xD8AD to 'latin1'.

Question

I am working with phpMyAdmin and I have Arabic character values ​​in my name column like کلب الرقيÙ'Ø© علی العزیزبوری , I want to convert them into the correct form.

I tried the following query and it converted most of the records in the table into the correct form.

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);

I also tried other methods.

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

But several of them remain the same and show the following 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;

This query runs fine and displays the unconverted characters perfectly. But when I use update query it doesn't work.

Answer 1

This is garbled or "double-encoded" Arabic text.

There are several ways to repair data. There is only one way to improve it, other ways will make it worse.

Have a look at this UTF-8 character issue; what I see is not what I'm storing and follow the suggestions therein about getting the hex value of what's in the database , in addition to providing SHOW CREATE TABLE defined by the current schema. Please also provide SHOW VARIABLES LIKE 'char%' and connection parameters.

Before backing up the various ALTERs and CONVERTs, please provide the hex values ​​and I might choose the correct conversion method.