Tag Archives: MySQL

Changed WordPress Blog to Use HTTPS

A few days ago I noticed some odd errors in my PHP error log indicating some HTTPS accesses to my site which shouldn’t have been possible. Upon further investigation, it turns out that my web host, Surpass Hosting, had enabled cPanel’s AutoSSL on their shared servers back on January 24th, 2018 or so. I decided to take advantage of this and began migrating my blog to use HTTPS. 

I first went into the General Settings and changed the WordPress Address (URL) and Site Address (URL) to use https. I then followed the directions at Replacing Image Links in WordPress After Installing an SSL Certificate – WPMU DEV to manually update my database using SQL commands. While there are plugins that could probably do this, I wanted to know exactly what SQL commands were being executed on my database. Note: I made a backup of the database before starting any of this. These are the commands I used to change my image and other links to use HTTPS (I replaced my actual domain name here to confuse bots):

UPDATE wp_posts
SET post_content = REPLACE(post_content,'src="http://blog.(domain.com)','src="https://blog.(domain.com)');

UPDATE wp_posts
SET post_content = REPLACE(post_content,'src="http://www.(domain.com)','src="https://www.(domain.com)');

UPDATE wp_posts
SET guid = REPLACE (guid, 'http://blog.(domain.com)', 'https://blog.(domain.com)') WHERE post_type = 'attachment';

UPDATE wp_posts
SET post_content = REPLACE(post_content,'href="http://blog.(domain.com)','href="https://blog.(domain.com)');

Because some of my featured images are external images on my main site being handled by Featured Image From URL, I also needed to update the wp_postmeta database. I used

SELECT * FROM wp_postmeta
WHERE meta_key = "fifu_image_url" AND meta_value != "";

to list the posts that had one and just manually updated these entries in my SQL editor directly.

I also had to modify the code in my widgets to avoid loading mixed content from my gallery and main website, which will also work over HTTPS now.

To make my local development environment match the web host, I then began to figure out how to get SSL working on my local Apache installation. I’ll save that endeavor for another post (which I did get working). 😃

Anyway, so far, so good.

Blog Updated to WordPress v4.2.1 and Other Changes

WordPress came out with version 4.2.1 today to address a security issue. I have thus updated my blog to that version. I have also made a couple other changes.

First, Version 4.2 introduced this Emoji stuff which I didn’t really care for and it broke some of my smileys in older posts, namely the “confused” one: :???: (or

:???:

as one of the textual representations). It came across as an unrendered character on my system instead of the smiley image icon_confused.gif. I found a new plugin called Classic Smilies which removes the Emoji CSS and JS from loading and restores the older images.

Second, I decided to change the character set in my blog’s MySQL database from latin1 to utf8 which has been the default for some time. When I started my blog, latin1 was the default. I found Fixing a MySQL Character Encoding Mismatch and followed it. Here’s what I did:

  1. I used SQLyog, which I bought a long time ago, to first backup the database to another database and then to dump the original database as a SQL file.
  2. Edited the SQL file and changed all the CHARACTER SET= latin1 and CHARSET=latin1 statements to utf8 instead.
  3. Emptied the database to remove all tables and data.
  4. Altered the database to make the default charset utf8.
  5. Restored the database from the edited SQL file.

Now, all the fields in the database have a charset of utf8 and collation of utf8_general_ci. So far everything looks normal. I actually converted the database before updating to 4.2.1. :)