Conquering Challenges

Months ago, I posted an article about some of the challenges encountered while migrating from one service level to another for my blog.

As it turns out, I had some long lingering effects that I was delaying fixing because I didn’t want to have an apparent flood of republished posts.

What could possibly be wrong after fixing everything I listed in the aforementioned article? Good thing you asked. Every occurrence of a percent symbol was changed to a new string – “{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170}”.

Things that make you go huh?

As you can see, this string is hardly useful. I started noticing this string in several articles and it took a while to figure out what the heck was going on. It wasn’t until I was reviewing an old post that had a SQL Script in the post that contained a “LIKE” keyword. An example of one such article is this one about SQL Trace. At that point, the lightbulb went off and I decided to give it a try. Lo and behold, that string truly did represent a percent symbol. I made the appropriate changes and voila, the post looking perfect all over again.

That was one measly little post. I have ~100 articles affected by this problem. Obviously that becomes tedious to change over and over and over. In addition, if I do change them manually one by one, I end up with ~100 articles triggering social media alerts that makes it look like I am re-publishing a bunch of stuff when really it is just editing and fixing the articles. What to do? What to do?

Thank goodness I have half an idea how to update data in a database and my blog is stored in a database. I can run a mass update to replace that string properly wherever it occurs. Let’s try it.

First, just one article to validate…

UPDATE `wp_posts` 
SET post_content = REPLACE( post_content, '{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170}',  '%' )  

WHERE post_title='Primary Key Discovery';

And if I look at the post after the update, I can confirm that the update succeeded (in my staging database first and foremost). Next, I roll that change to my production database and test again.

After I can confirm that it worked there (you can also see that it worked via this link), then I can work on the mass update.

UPDATE  `wp_posts` 
SET post_content = REPLACE( post_content,  '{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170}',  '%' ) 
WHERE post_status <>  'draft';

Now, when I check my blog for that string, I find that there is no more residue left and all seems to be back to being correct.

To fix the string problem, I did use the replace function. Since this is a MySQL database, I had to use that version of it. Notice it isn’t terribly different from the TSQL replace function.

The Wrap

I did not enjoy this journey much at all. Much of the experience was due to outside forces. I can’t do much to control them, but I can do something to fix the net effect of what they caused. I am sure that with all of the problems encountered in that migration, I will find something further that needs to be fixed.