Troubleshooting & How-Tos 📡 🔍 Servers

Database Upgrade Problems! MySQL 5 → 8 with WordPress

My webhost upgraded their database server from MySQL 5 to 8. The upgrade itself went smoothly, but I did find some after-effects on my WordPress sites:

Broken characters!

Only on K-Squared Ramblings, whose database goes back to 2002, before WordPress branched off of the old b2 blogging engine. I thought I’d fixed the old column definitions, but apparently I missed some, or possibly the DB migration script was keying off of another field (which would fit with the MB3/MB4 problems below).

As near as I can tell, the fields were still using Latin1 character encoding even though WordPress itself has been sending and reading UTF8 for years. 🤦 I used the 3-step conversion process from the WP Codex on post_content, post_title, comment_author and comment_content:

  1. Tell the database that the contents were Latin1.
  2. Convert it to a blob, which ignores character sets.
  3. Convert it to utfmb4
    That worked, though I had to drop some indexes before and recreate them after.

More broken emojis!

The migration recreated the tables with utf8mb3 instead of utf8mb4*, and not all emoji fit in the three-byte limit. Fortunately, converting from MB3 to MB4 isn’t a problem the way converting from non-Unicode to UTF-anything is. Unfortunately, you have to update everything individually. Fortunately, this is SQL, so you can script just about anything. I used Florian Henry’s answer on this StackOverflow question to generate a script to fully convert each table, setting it to utf8mb4 instead of just utf8:

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA= "(your database name here)"
AND TABLE_TYPE="BASE TABLE"

Then I ran all the ALTER TABLE statements it generated. So far, so good!

I’m sure there are some more broken characters from the Latin1/UTF8 issue, but I figured most of them were going to be in posts and comments, and I did not want to go through every single column with the wrong encoding and fix them using the Codex method above.

Update: I also needed to change the default character set and collation on the overal database, juuuust in case of new tables in the future:

alter database (your database name here) default character set utf8mb4 collate utf8mb4_general_ci

Broken Backups!

I have a script that runs mysqldump regularly on each blog database, then uploads it (along with the files) to another server. After the upgrade to MySQL8, the script started kicking back an error:

mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces

Naturally, the PROCESS privilege wasn’t something I could assign any of my logins. The solution here was to add --no-tablespaces to the command line, after confirming that it doesn’t cause the backup to skip the table schema definitions.

–

I think that covers everything I’ve found so far.