Relaunch 4.2 utf8mb4 database upgrade

Question

I updated WP to 4.2 while it was on an old server, that doesn't match the utf8mb4 upgrade requirements.

I moved to another server that fit these requirements, but WP won't launch this database upgrade anymore: /wp-admin/upgrade.php says Your WordPress database is already up-to-date. I tried to set my wp_post table to utf8mb4 manually but all my special chars became “�”.

Any way to force launch the maybe_convert_table_to_utf8mb4 in a simple upgrade, without losing all my special chars?

Thanks!

Answer 1

I think the answer to your question right now is "No".

There is no easy way to trigger maybe_convert_table_to_utf8mb4 on sites once they were already upgraded beyond WP 4.3 on a server that didn't meet the requirements laid out in this post:

https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade/

Note that, based on looking at WP source code, it seems they moved this from 4.2's update sequence to 4.3 (it's no longer present in 4.2, which now has no upgrades at all), maybe hoping to get more users on board.

So that's your answer and it sucks but is more accurate than the others ¯_(ツ)_/¯

We're currently working on making a simple script that lets you trigger the essence of the upgrade sequence based on an action hook. If we can get it stable and working we'll try to come back and share it here for others to use.

Our basic plan is to extract the actual db part of upgrade_430(), isolate it from the db update system, and trigger it manually.

EDIT: SOLUTION BELOW

While there's no easy way to trigger the script, here's a hand-coded workaround, based on upgrade_430() but designed as a drop-in.

https://gist.github.com/carlalexander/4106cfaaf405cec454ba195631bcb6bc

You could put this in a plugin, or just paste it in your functions.php. Either way it should be TEMPORARY.

It's set up to trigger automatically when you visit https://yoursite.com/?update-utf8bm4=1

This ensures that it only runs a single time and you get to choose when (for large databases it could take awhile and you don't want to have anyone editing a post while it's happening).

If you don't want to have the GET trigger just remove the add_action part and the if (!isset($_GET['update-utf8bm4'])) part.

Again: REMOVE THIS WHEN COMPLETE, you don't want to leave a GET trigger like this lying around :)

Answer 2

Of course, if goes without saying, make a backup of the database before trying anything, but you'd work with the original version of the database—I'd try two things: WordPress database repair and maintenance, and phpMyAdmin's optimize tables:

This came from this page, which has a lot of info WP Knowledgebase, but you start by adding this line to your site's wp-config.php:

define('WP_ALLOW_REPAIR', true);

then go to http://yoursite.com/wp-admin/maint/repair.php

You should see a page with two options: 'Repair Database' and 'Repair and Optimize Database.'

Clickon 'Repair and Optimize Database' and give the script time to run. Once they've run successfully, you'll get update messages letting you know the status of various tables.

As soon as it has run, very important, remove the wp_allow_repair line you just added to wp-config.php, delete it from the wp-config.php file.

If that doesn't work, you can try phpMyAdmin's table optimization, but if WordPress's didn't help, that may not either. I'd try it on the same version of the database you ran the last repair on.

Go into phpMyAdmin, select your database, scroll to the bottom of the page, check 'Check All' to select all of the tables in the database, then select "Optimize Table" from the select menu next to it. That's it, it will start automatically.

Here's more detail and screenshots: WPMUDev

If those two steps don't work—alone or together—you can try using another copy of your backup database to run them in the reverse order... Good luck!

Answer 3

Have you tried to change the database table Collation ? Please try this:

Login to phpmyadmin > select your database > Operations > now change Collation from drop-down to "utf8mb4_unicode_ci" or change to what was used on your old server.

I hope it will work for you.

see screenshot: http://prntscr.com/8ip1ro/direct

Remember: If you export sql file from your old server to your pc and open it with any text editor... and customize and save.. then please check when you save the sql file Text Editor "Encoding" is Utf-8 .. see my screenshot: http://prntscr.com/8ip2nr/direct

Thanks