SQL Collation Not Displaying Correctly

Question

I've asked this question before, but the post was marked as duplicate to this question and deleted.

I've read the post intensively and tried all the things suggested. Unfortunately the post didn't solve the problem I'm currently having. The notice on the deleted question referred me to asking a new question.

Original question:

I recently moved to a different hosting provider with the database backup of the previous provider.

Somehow the database values are not displaying correctly anymore.

For example, different languages like 信長の野望・創造 パワーアップキット will output as ä¿¡é•·ã®é‡Žæœ›ãƒ»å‰µé€ .

Trademark icons in titles such as ™ will be displayed as â„¢.

What can I do to fix this? Considering the database is 1:1, I have no idea what the problem is here.

---

What I've done

• My whole database is set to latin1_swedish_ci. I've changed it to utf8mb4_unicode_ci. This didn't make any changes.

• I've specified charset=utf8mb4 in the PDO mysql connection DSN.

• I've set the default charset to UTF8.

Is there anything I've missed/can do to fix this?

---

Small update: uploading the database to my local database does seem to output them correctly, which is a 1:1 installation. This is very frustrating.

• Webpage has UTF8 encoding tag.
• PHP info shows webpage is displayed in UTF8.

---

31-7-2020:

Running query

SHOW SESSION VARIABLES LIKE 'character_set_%'; SHOW SESSION VARIABLES LIKE 'collation_%';

gave me the following results:

"character_set_client"  "utf8mb4"
"character_set_connection"  "utf8mb4"
"character_set_database"    "utf8mb4"
"character_set_filesystem"  "binary"
"character_set_results" "utf8mb4"
"character_set_server"  "utf8mb4"
"character_set_system"  "utf8"
"character_sets_dir"    "/usr/share/mysql/charsets/"

"collation_connection"  "utf8mb4_general_ci"
"collation_database"    "utf8mb4_unicode_ci"
"collation_server"  "utf8mb4_unicode_ci"

Running query SET character_set_results = NULL; SET character_set_results = binary; didn't gave any results.

Answer 1

1. Changing the collation or character set in database does NOT change existing column & tables collation settings. Similarly, changing character set of a column does NOT change the existing data in the columns. See what each column is set to use. Start from there.

   select COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME from information_schema.columns

2. In your mysql client, run the following to see what's the connection settings are

   SHOW SESSION VARIABLES LIKE 'character_set_%'; SHOW SESSION VARIABLES LIKE 'collation_%';

3. In your mysql client, you can ask server not to do any conversion and see if that helps (i.e. do a SELECT on the table and see how it looks).

   SET character_set_results = NULL; SET character_set_results = binary;

See here for more details

https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html