Knowledgebase

Database collation errors can occur with the PrismConnect module when there is a mismatch between collations in different tables within a database. This causes an error when a query tries to join two tables and throws an error due to the mismatch. The error message typically looks like the one below and will occur when the cron job tries to sync licenses between WHMCS and Prism Portal.

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'

WHMCS provide guidance on database collation in this article - https://docs.whmcs.com/Database_Collations

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.

To fix the error, all tables and columns must be set to the character set "utf8mb4" and collation "utf8mb4_unicode_ci". To make the change, follow the steps below.

1. IMPORTANT - Backup your database!
2. Login to phpmyadmin and select the whmcs database
3. Click on the operations tab 
4. Scroll to the Collation section which should appear towards the bottom of the page
5. Using the dropdown, select the "utf8mb4_unicode_ci" collation. Then tick the checkboxes beneath for "Change all tables collations" and "Change all tables columns collations" and click on Go.

If you run into any errors trying to change the collation they will need to be addressed individually. This can vary depending on the content in your database but in our experience it usually works first time. 

If you're still stuck, please open a ticket or email support@axiomit.com.au.