# Table Special Character issue after MySQL Upgrade **URL:** \ **Category:** MySQL & MariaDB\ **Tags:** mysql, new-release\ **Created:** [May 24, 2024, 6:48pm UTC](https://forums.percona.com/t/table-special-character-issue-after-mysql-upgrade/30554 "2024-05-24T18:48:32Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Pavanmysql](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pavanmysql/32/7152_2.png) [@Pavanmysql](https://forums.percona.com/u/Pavanmysql)\ **Post date:** [May 24, 2024, 6:48pm UTC](https://forums.percona.com/t/table-special-character-issue-after-mysql-upgrade/30554/1 "2024-05-24T18:48:32Z")
I have below table in mysql 5.7.36 database. CREATE TABLE `message_table` ( `id` int(11) NOT NULL AUTO\_INCREMENT COMMENT ‘primary key for community messages’, `icon` varchar(255) NOT NULL DEFAULT ‘’ COMMENT ‘this field will contain IPFS hash’, `subject` varchar(255) DEFAULT ‘’ COMMENT 'encrypted document data to uniquely identify ', **`details` text CHARACTER SET utf8mb4 COLLATE utf8mb4\_unicode\_ci COMMENT ‘this field will contains the body of community messages’,** PRIMARY KEY (`id`), KEY `message_owner_nci` (`message_owner`) ) ENGINE=InnoDB AUTO\_INCREMENT=1234 DEFAULT **CHARSET=utf8** In MYSQL 5.7.36 in This table’s ‘details’ column is set to utf8mb4 with the utf8mb4\_unicode\_ci collation to store special characters like emojis and other unique symbols. Recently, we upgraded our MySQL from version 5.7.36 to 8.0.36 using the mysqldump command. After importing the data into MySQL 8, we altered the table’s character set to DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_0900\_ai\_ci. However, after this alteration, we encountered data issues in the ‘message\_table’. The ‘details’ column displays ‘???’ instead of the special characters. show create table in mysql 8.0.36 CREATE TABLE `message_table` ( `id` int NOT NULL AUTO\_INCREMENT COMMENT ‘primary key for community messages’, `icon` varchar(255) NOT NULL DEFAULT ‘’ COMMENT ‘this field will contain IPFS hash’, `subject` varchar(255) DEFAULT ‘’ COMMENT 'encrypted document data to uniquely identify ', **`details` text CHARACTER SET utf8mb4 COLLATE utf8mb4\_0900\_ai\_ci** COMMENT ‘this field will contains the body of community messages’, PRIMARY KEY (`id`), KEY `message_owner_nci` (`message_owner`) ) ENGINE=InnoDB AUTO\_INCREMENT=1234 DEFAULT **CHARSET=utf8mb4 COLLATE=utf8mb4\_0900\_ai\_ci;** Could you please help me resolve this issue? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 24, 2024, 8:02pm UTC](https://forums.percona.com/t/table-special-character-issue-after-mysql-upgrade/30554/2 "2024-05-24T20:02:04Z")
Verify the data at all stages to identify where the conversion loss is happening. On 5.7, SELECT HEX(details) WHERE id = X Then check the mysqldump output for id = X Then, repeat the above SQL on 8 after import. Somewhere, something is causing conversion issues. ---
**Author:** ![Pavanmysql](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pavanmysql/32/7152_2.png) [@Pavanmysql](https://forums.percona.com/u/Pavanmysql)\ **Post date:** [May 24, 2024, 8:29pm UTC](https://forums.percona.com/t/table-special-character-issue-after-mysql-upgrade/30554/3 "2024-05-24T20:29:17Z")
> [@matthewb](#): > > SELECT HEX(details) WHERE id = X Hi Matt, Thank you for your response. Here is the output we observed: - **MySQL 5.7** : `F09F989DF09F9890EFB88F` - **mysqldump** : `'ð<9f><98><9d>ð<9f><98><90>ï¸<8f>'` - **MySQL 8** : `3F3F3F3F3F3F3F3FEFB88F` Could you please advise on how to resolve this issue? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 24, 2024, 8:51pm UTC](https://forums.percona.com/t/table-special-character-issue-after-mysql-upgrade/30554/4 "2024-05-24T20:51:10Z")
I was unable to reproduce this ```auto mysql [localhost:8035] {msandbox} (test) > CREATE TABLE message_table ( id int NOT NULL AUTO_INCREMENT, icon varchar(255) NOT NULL DEFAULT '', subject varchar(255) DEFAULT '', details text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, PRIMARY KEY (id)) ENGINE=InnoDB AUTO_INCREMENT=1234 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.04 sec) mysql [localhost:8035] {msandbox} (test) > INSERT INTO message_table VALUES (1, 'my icon', 'my subject', UNHEX('F09F989DF09F9890EFB88F')); Query OK, 1 row affected (0.01 sec) mysql [localhost:8035] {msandbox} (test) > SELECT * FROM message_table; +----+---------+------------+-------------+ | id | icon | subject | details | +----+---------+------------+-------------+ | 1 | my icon | my subject | 😝😐️ | +----+---------+------------+-------------+ 1 row in set (0.00 sec) $ ~/dbdeployer/opt/mysql/8.0.35/bin/mysqldump -h 127.0.0.1 -P8035 -u msandbox -pmsandbox test message_table >dump.sql $ ./use test SELECT * FROM message_table; +----+---------+------------+-------------+ | id | icon | subject | details | +----+---------+------------+-------------+ | 1 | my icon | my subject | 😝😐️ | +----+---------+------------+-------------+ 1 row in set (0.00 sec) ``` Your mysqldump is clearly not correct. You might want to add a set names or something to the running mysqldump command. ---
**Author:** ![Pavanmysql](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pavanmysql/32/7152_2.png) [@Pavanmysql](https://forums.percona.com/u/Pavanmysql)\ **Post date:** [May 24, 2024, 9:16pm UTC](https://forums.percona.com/t/table-special-character-issue-after-mysql-upgrade/30554/5 "2024-05-24T21:16:55Z")
> [@Pavanmysql](#): > > 3F3F3F3F3F3F3F3FEFB88F Hi Matt, You were right. I tried to back up only a single table from MySQL 5.7.39, and when I checked the `mysqldump`, it showed the emojis correctly. However, during the upgrade, I exported the full schema structure and data into separate files. The command I used to back up the schema’s data is below. During the full schema backup, the `mysqldump` file showed junk characters,strange behavior? but when I backed up a single table, it displayed the emojis correctly. It seems that the mysqldump behavior is such that when I export only a table, it exports the data properly. However, when I export the full schema and data, it shows junk characters in the mysqldump file. You can reproduce this issue using the following command: **Command used to take schema backup and mysqldump file contain juck char:** mysqldump -uroot -p --no-create-info --no-create-db --routines=0 --events=0 --triggers=0 --max\_allowed\_packet=512M --single-transaction --databases db\_name\_contain\_table \> db\_name.sql **Command used to take only single table and it shows proper data in dumpfile:** mysqldump -uroot -p --no-create-info --no-create-db --routines=0 --events=0 --triggers=0 --max\_allowed\_packet=512M --single-transaction homebase\_stage tablename \> dump\_table.sql ---
**Author:** ![Pavanmysql](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pavanmysql/32/7152_2.png) [@Pavanmysql](https://forums.percona.com/u/Pavanmysql)\ **Post date:** [May 24, 2024, 9:45pm UTC](https://forums.percona.com/t/table-special-character-issue-after-mysql-upgrade/30554/6 "2024-05-24T21:45:55Z")
It might be because we were using UTF-8 as the default server and database character set in MySQL 5.7. To store special characters in MySQL 5.7, we changed the only column character set to utf8mb4. When exporting only the table, it exports the data correctly. However, when exporting the whole schema, it adds junk characters because the database character set is UTF-8.