What’s in the bag? Behind the scenes at vBrownBag.com

A few weeks ago, I asked my good friends Alastair Cooke & Chris Williams how I could be useful to them & the vBrownBag.com website. Al suggested that I take a look at the automation script, affectionately known as “the meatgrinder” that had been running behind the scenes for years but hadn’t been running for about the past 6 months. It was written mostly in PowerShell and ran on a Windows VM and its job was to process a video file (such as one from a live vBrownBag TechTalk recorded at a conference, or a recording from the weekly video podcast). The script would upload the file to AWS S3, post to the YouTube channel, update the Apple Podcasts RSS feed, and post by email to the WordPress install that powers vBrownBag. Finally, Al wanted it to run as an AWS Lambda function.

Excitement intensifies

I was very excited, and still am, because I just love PowerShell. I find it fascinating and always have. I think it’s because I used to love bash scripting and PowerShell’s pipeline reminds me of that. Also, there’s super cool things like splatting that just make life easier. Looking back on the first few weeks of working through this process, I realize that I’ve learned way more than merely reacquainting myself with PowerShell in a very short period of time. And, due to the fact that I’m running PowerShell on Lambda (which is an Amazon Linux image underneath) I’ve run into some unique issues that there aren’t a lot of Google results about. So I decided that I should start writing these down in public for a few reasons. The first one is documentation for myself & any friend of the vBrownBag who may need to work on the meatgrinder in the future. Second, it’s for folks like me who are stuck trying to figure out things like the WordPress REST API, working through Google’s OAuth framework in order to post to YouTube, optimizing Lambda layers, and more.

Bad data & a good ending

However, before I get to all of that, I’d like to start with the first problem I had to solve which was (what appeared to be) corrupted data in the vBrownBag.com database. I’m not entirely sure when it happened, but there were a lot of characters that didn’t belong in the posts, pingbacks, and comments such as Â, €, or ™. There were even some non-printable characters that wouldn’t render properly in a decent text editor like Notepad++. I’m not sure if it was a bad automated restore, or a past friend of the vBrownBag that accidentally messed it up, but it was a problem that impacted the majority of the site content. I tried a simple find & replace but working with database dump files is a risky business and I could’ve easily made the problem worse. I did a lot of googling too, like any decent technologist would do. 😉 Finally, I found the name of the problem: mojibake. It’s garbled or gibberish text that is the result of text being decoded using an unintended character encoding. That took me down a wild rabbit hole full of arcane magic and dark spells, but eventually I stumbled onto this page. Turns out that a fellow nerd and former MySQL guy for Yahoo decided to write down all sorts of random database things on one very long page. The fix that I was looking for was in the “fixes for various cases” section. Specifically, this command:

UPDATE tbl SET col = CONVERT(BINARY(CONVERT(col USING latin1)) USING utf8mb4);

The command converts the col column to latin1 encoding, then converts it to a binary string, then converts the string to utf8mb4 encoding. The fix was as easy as that. I was amazed. Thank you, Mr Rick “MySQL Superman” James for helping to fix the website.

For my next post in the series, I’ll break down the meatgrinder steps into smaller pieces and more of the things I’ve learned along the way.

Edit: Read part 2 in the series. You know you want to.