This error happens when there are tables on your WordPress installation created with different collations. Here we’re going why that happens.
WordPress has a built in feature: if you do not explicitly declare the database collation (how a database should compare strings) it makes some thoughts about it and possible decide to use the new collation utf8mb4_unicode_520_ci .
It could happen if the database you’re using supports it, and it happens every time you’re using a MySQL version >= 5.6. Note that MariaDB declares itself as a Mysql version 5.5.5, so even if MariaDB supports that collation, WP never assumes it can be used and sticks on the “old” utf8mb4_unicode_general_ci.
Where is the problem? If you installed you WP time ago on an environment which didn’t support that special “unicode 520” collation, WP created every table (for its own or for third party plugins) with the standard “unicode general” collation.
When you, or your provider, upgraded the database to a more recent version, every new plugin which is installed and uses the WP table creation support, finds it’s tables to be built on the new “unicode 520” collation which is incoherent with all other tables in your installation.
Nothing strange will happen until you tries to query those incoherent tables with a join which tries to match text fields with different collation.
There is not an automated solution
This is a bug and at writing time there is not a solution. You can only convert all tables in your installation to the new “unicode 520” collation or convert back the new table to the “unicode general collation”.
WP should not assume a new collation to use base on the changed environment, it should store the original collation and keep on it. Or it should upgrade all tables to the new collation.
Problems with WooCommerce
This problem is widely reported but I found it using WooCommerce and some join queries. WooCommerce was installed on an old blog and all its tables have been created with the new collation. To convert it back a query like this one could be run:
alter table wp_woocommerce_order_items CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
which converts the WooCommerce table wp_woocommerce_order_items to the “unicode general” collation. That query should be run on every WooCommerce table.
MariaDB
Apparently on MariaDB where the tables have been made incoherent to test the problem, no errors are raised. Probably MariaDB manage internally the different collation when executing the query.
