In this post, I will talk about a problem I faced after migrating my website to a new server. The problem is related to the values of character_set and collation variables. I have a website with pages in different Indian languages such as Hindi, Tamil, Telugu, and Malayalam. I had created databases and tables with “utf8” as character_set and “utf8_general_ci” as collation. I migrated the website to the new server after installing a control panel on the new server. I installed the same PHP, Apache, and MariaDB versions on the new server to not worry about additional settings with different versions. However, when I accessed webpages from the new server, all non-English characters got lost. Those non-English characters were shown as some weird characters, which was enough to give me a headache.
I searched for the solutions; people suggested changing the collation to “utf8_general_ci” and adding “meta charset=utf-8” to pages. None of these solutions worked for me. The columns of my tables already had “utf8_general_ci” as collation, and pages also had “meta charset=utf-8“.
Here is how I solved the problem. Luckily I had access to my old server. So, I checked the difference in MariaDB settings on the old and new servers. Both servers had the same version of MariaDB. I compared the character_set and collation variables and found that values differed on the new and old servers.
The values of character_set and collation variables on the old server were as follows:
MariaDB [(none)]> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)
MariaDB [(none)]> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.001 sec)
The new server had “utf8mb4” as character_set and “utf8mb4_general_ci” as collation whereas the old server had “utf8” as character_set and “utf8_general_ci” as collation. So, I edited the configuration file of the MariaDB (my.cnf) and deleted all lines that were setting character_set variables to “utf8mb4” and collation variables to “utf8mb4_general_ci“. I saved the modified configuration file and restarted the MariaDB server. It fixed the problem, and now my web pages correctly display all non-English characters.
If you are also facing this issue, look into your MariaDB configuration file. The problem may be hiding in the configuration file.