Issues with Emoji’s in WordPress Code Blocks

On several sites including this one I use the brilliant code-block-pro code block plugin and as I get ChatGPT to write a lot of comments to my code once i’ve written it or readme files i’ve had issues saving emojis in those code blocks.

I spent sometime checking why and although I couldn’t replicate on a fresh site I opened an issue here and I kept on looking the plugins author concluded there was no plugin issue so I thought I would investigate my second hunch it was the database encoding.

I set about checking the posts database table and it was utf8mb3 encoded rather than the newer and emoji safe utf8mb4_unicode_ci some testing of changing and updating these proved this was the issue and in addition to this it wasn’t simple to convert for several reasons one being that there invalid DATETIME defaults in wp_posts

I used the following code snippet to identify the issue and confirm my suspicions and confirm the upgrade worked :

add_action('admin_notices', function () {
    global $wpdb;

    $table = $wpdb->prefix . 'posts';

    // Query table collation
    $collation = $wpdb->get_var(
        $wpdb->prepare("SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = %s AND TABLE_SCHEMA = %s", $table, DB_NAME)
    );

    // Get full character set from collation
    $charset = $wpdb->get_var(
        $wpdb->prepare("SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME = %s", $collation)
    );

    echo '<div class="notice notice-info"><p>';
    echo " <strong>Table:</strong> {$table}<br>";
    echo "<strong>Character Set:</strong> {$charset}<br>";
    echo "<strong>Collation:</strong> {$collation}";
    echo '</p></div>';
});

add_action('admin_notices', function () {
    global $wpdb;

    $table = $wpdb->prefix . 'posts';

    // Query table collation
    $collation = $wpdb->get_var(
        $wpdb->prepare("SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = %s AND TABLE_SCHEMA = %s", $table, DB_NAME)
    );

    // Get full character set from collation
    $charset = $wpdb->get_var(
        $wpdb->prepare("SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME = %s", $collation)
    );

    echo '<div class="notice notice-info"><p>';
    echo " <strong>Table:</strong> {$table}<br>";
    echo "<strong>Character Set:</strong> {$charset}<br>";
    echo "<strong>Collation:</strong> {$collation}";
    echo '</p></div>';
});

Which outputs a admin notice like this with the database information in it which I also used to confirm successful upgrade using the moderniser script.

A few trial and errors allowed me to get a working update script with a options flag to make sure it only runs the once, and I came up with the https://github.com/stingray82/wp-db-modernizer

With this we now

• Converts all core WordPress tables to use utf8mb4_unicode_ci
• Fixes invalid DATETIME defaults in wp_posts
• Automatically creates a full .sql backup in wp-content/backups/ *
• Disables itself after running once – Safe and recommended to delete after use

* Warning: back up first using something like updraft as you need phpexec enabled for the inline backup to work

In my testing including this site it fixed the issues in 100% of cases I hope this article helps others do just that

General Reminder: Backup before doing any work to your WordPress database