Database Collations

From WHMCS Documentation

MySQL stores textual data according to a character set, and applies a set of rules for handling textual data within a character set. This set of rules is called a "collation". Every character set in MySQL has one or more collations that can be used to perform searches on data stored according to that character set. When two columns are being used in a query, a mismatch between their character sets and/or their collations may lead to inability to complete the query as intended. In other cases, when a collation assigned to a column is not ideal, MySQL may be unable to sort data stored in that column, or results of such sorting may be different from what is expected.

In the past, certain tables and/or columns in WHMCS used varied character sets and collations. In addition, some tables and/or columns that used "utf8" character set were set to use utf8_general_ci collation. This collation allows for marginally better performance than a comparable utf8_unicode_ci collation. However this performance boost is achieved at a cost of not applying all the rules of Unicode standard when it comes to working with textual data, including comparisons, searches and sorting. Using utf8_unicode_ci collation ensures correct data processing, while the performance hit associated with this change is usually negligible and rarely exceeds 5%. More information on the difference between utf8_general_ci and utf8_unicode_ci can be found here:

https://forums.mysql.com/read.php?103,187048,188748

Editing Database Collations

In order to synchronize collations across your database, you need to find which tables and columns use mismatching collations. For tables you may use the following query:

SELECT table_collation AS collation,GROUP_CONCAT(table_name) AS tables FROM information_schema.tables WHERE table_schema='whmcs_db' GROUP BY collation;

This will show your tables grouped by collations they use. If more than one collation is displayed, or any of the displayed collations are outside of the recommended list (utf8_unicode_ci or utf8mb4_unicode_ci), WHMCS may not function properly. A similar query for columns may look as follows:

SELECT collation_name AS collation,GROUP_CONCAT(concat(table_name, ".", column_name)) AS columns FROM information_schema.columns WHERE table_schema='whmcs_db' AND collation_name IS NOT NULL GROUP BY collation;

To synchronize all tables and columns to use the same collation, you first need to decide which character set to use. If you have MySQL 5.5.3 or higher or MariaDB 5.5 or higher, using "utf8mb4" charset and utf8mb4_unicode_ci collation is recommended. Otherwise "utf8" charset and utf8_unicode_ci collation should be used.

IMPORTANT: Changing table / column character sets or collations may alter the way your database stores information. We highly recommend making a backup of your database before performing any data conversions.

To choose the best path for updating your table / column character sets or collations, please refer to MySQL documentation:

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html