Various collation types in DB after upgrade to 4.2.1

After upgrading my multisite to 4.2.1, I have tables with utf8mb4, as well as utf8 tables in my database, whereas the utf8 tables are the plugin related ones. Is that correct?

Yes, WordPress now uses the utf8mb4 collation whenever possible, but of course the updater script can only know about WordPress’s standard tables, not plugin-provided tables.

Do I have to expect any issues in the future if the database contains a mixture of different collation types?

No, you shouldn’t have to worry. Tables are roughly self-contained in that regard.

Do I have to update the plugin-related tables to utf8mb4 too (and if yes, how)?

It’s not necessary, but if you want to, here’s a guide on how to convert those: https://pento.net/2014/04/07/wordpress-and-utf-8/

Currently, the collation of my DB is set to utf8. Shall I change the collation of the DB to utf8mb4 too?

If you do covert the collation of the other tables to utf8mb4, you might as well change the database’s collation to (note, all this does is set the default for future tables).

Before trying any of this though, make a backup: https://codex.www.ads-software.com/WordPress_Backups

Thanks a lot for your reply and links!

Would you suggest to change the following entries

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8');
/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', 'utf8_general_ci');

to

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8mb4');
/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', 'utf8mb4_unicode_ci');

? Or is it better to leave the definitions blank? What are the consequences of this rules? Do they override the server settings?

I’d recommend laving DB_COLLATE blank, and DB_CHARSET at utf8.

This is how they are in the current version of WordPress’s wp-config.php file.

Hi James,

Perfect, it’s what I already have set. Thanky for your help!

You’re welcome!

Do I have to expect any issues in the future if the database contains a mixture of different collation types?

No, you shouldn’t have to worry. Tables are roughly self-contained in that regard.

Actually, that isn’t always true. I ran into a problem recently where Gravity Forms was using a different collation from the WordPress core tables, and received an error because GF was doing a left join between one of its tables and a WP table:

WordPress database error Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'ifnull' for query SELECT n.id, n.user_id, n.date_created, n.value, n.note_type, ifnull(u.display_name,n.user_name) as user_name, u.user_email\n FROM wp_rg_lead_notes n\n LEFT OUTER JOIN wp_users u ON n.user_id = u.id\n WHERE lead_id=12 ORDER BY id ...

Right, as mentioned, “Yes, WordPress now uses the utf8mb4 collation whenever possible, but of course the updater script can only know about WordPress’s standard tables, not plugin-provided tables.”

I didn’t think they’ve be doing joins via that method though, seems disastrous, and know I know why. ??

I’d recommend telling the folks behind Gravity Forms about this.

I did. ??

They just told me to change the collations.

Ha, well hopefully they’ll have a more proactive fix in the future. ??