With the release of WordPress v5.7 and WooCommerce v5.1, there have been reports around the WordPress Community of “Illegal mix of collations.” This is caused when MySQL has tables with different collation algorithms being used. Upon investigation, we’ve found that most users are running into this issue within WooCommerce checkout although has not been limited to people running WooCommerce.
Since version 4.2, WordPress has been detecting the MySQL version. If its version is 5.5.3 or greater, it automatically selects the ‘utf8mb4_unicode_520_ci’ Collation Algorithm. However, there are plugins and themes that do not support or store data in the utf8mb4 algorithm. This results in the “Unknown Collation” Error.
Luckily, when WordPress v4.2 came out, we developed a Design-Time Plugin that resolves this by ensuring that the same algorithm is used throughout your database. While it was designed specifically for use with DesktopServer, it will work with any WordPress installation and is as simple as adding a plugin.
Within DesktopServer, the plugin is designed to run any time you import, copy, or move a site. When you run any of these functions, the Database Collation Fix Plugin will automatically take care of any mixed collation algorithms and standardize them. Further, it will run as a CRON job and continually keep the database or databases (if you have multiple sites) in check.
If you are not running DesktopServer, simply go to your WordPress Dashboard —> Plugins —> Add Plugin, and do a search for “Database Collation Fix” or you can download it from the WordPress Repository and install it manually. As opposed to using this as a DesktopServer Design-Time Plugin which you only need to run within DesktopServer to affect all sites, this method will require you to install the plugin for every WordPress installation.
Adding the Database Collation Fix plugin to your sites is a quick and easy way to keep your database collations consistent and running properly.
We hope you enjoy this free plugin and would love to hear from you if you’ve found it to be useful!
Marc’s driven spirit moves ServerPress, LLC forward with elevated momentum. With his business expertise, he rolls up his sleeves helping the Team in every possible way to ensure a smooth Workflow. Besides work, Marc is a big Advocate for Foster Care. Yes. 8 kids and counting…