Symptom
If none of the Admin Dashboard widgets are loading after you update WHMCS, there may be a problem with the table collation values.
Cause
To gather more information about the cause of the issue:
- Go to Configuration > System Settings > General Settings (Setup > General Settings prior to WHMCS 8.0) and choose the Other tab.
- Enable SQL Debug.
- Return to the Admin Dashboard. The problem will persist.
- Go to Configuration > System Logs (Utilities > Logs > Activity Log prior to WHMCS 8.0).
- Make a note of any MySQL® errors. For example:
Smarty Error: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' (SQL: select count(tblorders.id) as aggregate from tblordersinner join tblorderstatuses on tblorders.status = tblorderstatuses.title where tblorderstatuses.showpending = 1)
orSmarty Error: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' (SQL: select count(tblorders.id) as aggregate from tblordersinner join tblorderstatuses on tblorders.status = tblorderstatuses.title where tblorderstatuses.showpending = 1)
Solution
To resolve the error, edit the MySQL database structure to ensure that both the tables have the same collation setting.
Before you make changes to the database, you must back it up.
If, after you make these changes, the error persists, or if you are not comfortable making these changes to your WHMCS database, create a support ticket with technical support.
Examples
The examples below describe two possible scenarios for this issue.
Example 1
In this example, the following collation settings are different:
tblorders = latin1_general_citblorderstatuses = latin1_swedish_ci
For success, these values must match.
To do this, you could run the following command against the database:
ALTER TABLE tblorderstatuses CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;Example 2
In this example, the following collation settings are different:
tblorders = utf8_general_citblorderstatuses = utf8_unicode_ci
For success, these values must match.
To do this, you could run the following command against the database:
ALTER TABLE tblorders CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;