Miscellaneous and Useless Information

James Lin’s infrequently updated blog

This blog is working again!

Even though I haven’t blogged in a while, I at least try to keep the version of WordPress and PHP up-to-date. Last year, my hosting service DreamHost notified me that it would start charging me for using PHP 7.4, which was no longer supported. So I upgraded all of my PHP installations to version 8.1, to avoid the extra charge. However, I forgot to first upgrade the WordPress installation running this blog. This completely borked my blog, throwing a PHP error for every page.

I finally managed to fix it today, by doing the following steps:

  1. Install a fresh installation of WordPress directly from DreamHost, which allows DreamHost to automatically keep WordPress upgraded
  2. From the DreamHost control panel, go to phpMyAdmin to view the MySQL database for the old WordPress installation. Export the following tables to .sql files:
    • wp_commentmeta
    • wp_comments
    • wp_postmeta
    • wp_posts
    • wp_term_relationships
    • wp_term_taxonomy
    • wp_terms
  3. In the database for the new WordPress installation, also export the tables with the same names as above.
  4. For each exported .sql file from Step 3, copy the CREATE TABLE statement and paste it into the corresponding .sql file from Step 2.
  5. In the database for the new WordPress installation, drop the tables as named in Step 2, and then import the .sql files that I exported in Step 2. In this case, dropping the tables was fine, because the new WordPress installation didn’t have any content yet (besides theme boilerplate).
  6. In wp_user in the old database, copy the row for the user that authored all of the posts, then insert that data into wp_user in the new database.

And voila, it worked!

I also took this opportunity to fix an encoding issue. At some point when I upgraded WordPress in the past, the default encoding to view switched from Latin-1 to UTF-8, but the database was still in Latin-1, so a bunch of characters turned into garbage, such as curly quotes (’, “, ”) and dashes (–, —). I found a StackOverflow post with a SQL statement to change the encoding of particular columns of a database from one encoding to another, and applied it to the post_content and post_title columns in the wp_posts table:

UPDATE wp_posts SET
post_content=convert(cast(convert(post_content using latin1) as binary) using utf8mb4),
post_title=convert(cast(convert(post_title using latin1) as binary) using utf8mb4)
WHERE 1

I’ve also changed the theme to The Minimal Blogger, which is more modern than what I had before, especially that it is a responsive theme that looks good in mobile. It looks quite different from the rest of my website, so I’m not yet sure I’ll stick to it, but it’s better than before.

Leave a Reply

Your email address will not be published. Required fields are marked *