I cannot see unicode characters after migration of database. How to fix it?

A

I cannot see unicode characters after migration of database. How to fix it?

Category: MySQL

At times, when a database is restored under N99panel, the unicode characters in certain field/columns of a table, may not handle the way you had expect it would be handled.  The primary reason for this is the difference in collation of the field/column viz-a-viz the old setup.

When a mysqldump file is generated at the source, and the collation of the table or the field/column is not explicitly defined, therefore when it is restored at N99panel, the default collation of the VPS’s MySQL/MariaDB engine applies. This leads to improper handling of certain field/column types which contain the unicode content.

In order to fix it, before proceeding with the below mentioned steps, please take a full backup of your database and also verify the same. If the concerned field/column or the table has its collation set to ‘latin1_swedish_ci’ , and also when you are totally satisfied with the backup, then only proceed with the following steps.

Here, under PHPMyAdmin, you would need to run the following command w.r.t. a field/column in a table. Please use wise discretion and due-diligence before running these commands by substituting the relevant context/keywords. Details provided further below.

/* -- */
ALTER TABLE TBLNAME ADD COLUMN new_CLMNAME longtext CHARACTER SET utf8mb4;
UPDATE TBLNAME SET new_CLMNAME = CONVERT(CAST(CLMNAME AS BINARY) USING utf8mb4);
ALTER TABLE TBLNAME RENAME COLUMN CLMNAME TO old_CLMNAME;
ALTER TABLE TBLNAME RENAME COLUMN new_CLMNAME TO CLMNAME;
/* -- */

Here,

  • TBLNAME is the name of your table
  • CLMNAME is the name of your field/column containing unicode data
Permalink