fbpx

Convert WordPress Tables to utf8mb4

WordPress version 4.2 changed up the default database table collation. Swapping to utf8mb4 provides support for a wider range of characters, including emojis. If the contents of your content editor are deleted when saving with an emoji in it, this is probably your issue.

If you upgraded to WordPress 4.2 while running MySQL version 5.5.2 or lower, this wouldn’t have happened automatically like it should have. MySQL 5.5.2 and lower don’t support the new collation.

The following code can be added to your functions or a custom plugin to force-run the upgrade on all database tables once you’re running an updated version of MySQL.

// Notice admin_init - will only run in backend add_action('admin_init', function() { require_once(ABSPATH.'/wp-admin/includes/upgrade.php'); global $wpdb; $mytables=$wpdb->get_results("SHOW TABLES"); foreach ($mytables as $mytable) { foreach ($mytable as $t) { maybe_convert_table_to_utf8mb4( $t ); } } });
Code language: PHP (php)

Index Resizing

This update also brought changes to the size of the indexes. That database upgrade should have happened regardless of your MySQL version, so you shouldn’t need to worry about updates to that.

3 Comments

Leave a Reply

Your email address will not be published.

  1. I realize this is pretty old, but I want to try it. This seems like a relatively safe, easy way to go.

    I want to do it in my staging site first. I don’t see where you can specify the database name. I don’t want to accidentally update my non-staging database before making sure it works.

    Thanks

  2. Oh, nevermind, I eventually figured it would only do the staging database if I put it in the staging’s functions.php.

    But another question – does this process address the stuff about differing index sizes? I totally do not understand that issue.

    • Hey Jim – you shouldn’t have to worry about the index sizes. Regardless of your MySQL version, that process should have completed successfully. This code won’t make any of those updates.