A fun time with WordPress and Azure

I’m Back!!!!! Kind of.

It’s been a while folks. I’d like to be able to blame my long absence on the frustration I’m about to talk about with WordPress and Azure and something called cleardb but alas, I don’t really have that excuse. What actually happened was the life-sucking but ultimately fulfilling experience of leading the NexTech Africa planning team, transitioning to a new role and spending the first 6 weeks of the year in 6 different countries. I’ll write about all of that (maybe) eventually but for today I wanted to talk a little about what has been a particularly frustrating experience in my life.

It actually started late last month. I got back from some of my travels and wanted to start drafting a post about them and when I tried to login to my WordPress admin, it refused. This was a very confusing experience for me. I get to the login screen, it asks for my credentials, I provide them, hit enter and then… I get to the login screen. Tired and frustrated, I just figured I had once again forgotten my password and decided to go through the password reset process. So I get to the password reset screen, it asks for my username, I provide it, hit enter and then… yup back at password reset screen. This time, I noticed an error message saying “Could not save password reset key to database.” which I promptly decided was a problem for Future Me to solve.

Quick aside: have I told y’all about Future Me? This dude is awesome! He is smarter(?), wiser(?) and generally more experienced with life than present me which is awesome!  Most importantly though, he is always willing (read: has no choice) to take on problems, challenges, etc, that present me is unwilling or unable to deal with. Basically, Future Me is one of my faves!

Yeah, so anyways, delegated the problem to Future Me and promptly moved on to other things like trying to figure out how to code on a transatlantic flight with limited/no internet while sitting in a middle seat in Delta economy class (FWIW: tried, failed).

Fast forward about a month to yesterday and now my blog post backlog is growing out of control, I have a day off (President’s Day?) and I’m trying to procrastinate figuring out how to use Azure Functions because, you know, Future Me. So I decide to take another look at this freakin’ WordPress problem and alas, it persists – Future Me was really hoping I would get back it would turn out to have been a transient issue that I would never be able to explain but was also gone so I wouldn’t really care. No such luck.

After some rapid googling (on bing and google), I discovered the problem. Apparently, this error about saving the password reset key in the database tends to come up when your database is full. Now I’m out here like full? Bruh, my entire website, database and all, is hosted on Microsoft Azure, the global, trusted, distributed/geo-diverse, 4 9s of availability, auto-scale up and out etc etc cloud. There ain’t no way my damn database is full. There must be some mistake or some huge dump of data must have found it’s way into my database. Maybe it got overloaded with spam comments? Maybe, maybe, maybe. So I’m out here running through my mind how my database could possibly have filled up and so I go check it out. Here’s what I learned:

  1. While Azure is all those things, my little web app, not so much: As an MSDN subscriber (I think) I get $200 dollars of free azure credits a month which I was using to host my website. This meant that more or less, I was on the free or lower tier of most of the things which meant I didn’t have as much hyper-scale and all that as would be available to me with a few more dollars. I do, however, with the App Service Plan that I am on for hosting my WordPress site, have access to 10 GB of azure storage which would bring me back to my original question about how on earth do I consume 10 GB of storage on a tiny little WordPress blog? It isn’t possible. Which quickly led me into…
  2. When Azure says they are hosting your  WordPress site, it’s only mostly true: See, when I set up the website originally, I used some turnkey ‘click here for WordPress’ type solution from the azure marketplace and just figured they’d figure it out. They did. But not necessarily in the way that I would have expected to make sense. Because the database engine that WordPress uses is MySQL (InnoDB?) rather than MSSQL which is Microsoft’s own SQL thingy, Azure doesn’t seem to actually host the WordPress DB. Instead, it is hosted by a partner cleardb who, in the free tier, only offers 20 MB of database storage space of which I was already consuming 26 MB. So essentially, in 2017, cleardb gives me the equivalent of of a handful of floppy disks on which to host my website. Thanks Obama. Net-net I have 10GB of Azure storage I can’t use, 20MB of cleardb storage that is full, and therefore, a readonly database which has crippled my ability to post on my blog. Now I’m like “Okay, Dunni, you are not completely poor. How much is the next tier of this thing? pay the money and get more space.” which is when I discovered that…
  3. I’m basically never going to be able to pay for Azure: This is a mild exaggeration. I’m sure there’s a fix being made for this (word on the street is that it will be out next year) but currently, my azure account which was created while I lived in Canada as a student does not support the ability to pay with anything other than a Canadian credit card. No PayPal, no US credit card, no bitcoin, nu’n. Additionally, Azure also doesn’t support the ability to change the country of your Azure account. Apparently, if I want to do this, I should call support (too much work) who will then tell me that the work around is to create a new Azure account in the right country and then transfer all my resources over (way too much work) which is a solution that is also not guaranteed to work. Net-net, I’m not paying for azure so if anything comes up that requires payment, well, I’m SOL. For my current situation though, this was very very problematic because, as I said earlier, only way to get more space was to pay for higher tier, which I can’t do with my free credits I guess because it’s a payment to an external party but I can’t pay money because no credit card on file so once again, SOL. Now, unable to get a bigger database, I had to settle for the alternative. Smaller data.
  4. wp_options is a weird table: In my quest to smallen (not a real word. sue me.) my data set, I went diving in the actual WordPress database to discover where there might be some fat to trim. After checking all the places I would have thought might contain large amounts of data (posts, comments, etc) I was coming up blank so I queried the DB for the sizes of all the and discovered that the largest table (22 of the 25 MB) was the wp_options table. For some reason known only to God and WordPress, they seemed to be storing not only options and config information that one would expect in the DB but also a shit ton of logs. Like 350 000 row of logs. Don’t ask why. I don’t know or care. Anyways, I deleted the top 345000 rows which seemed to be all log files, left the rows that seemed to be actual configuration stuff and moved on with my life. DB immediately shrunk from 25 MB to 3 MB, I waited a couple of hours (cleardb does their database limit checks what seems to be twice a day so had to wait for them to do that and reinstate my write privileges) and I was once again able to log in to my account and write this post

As with many of my challenges with technology, this was ultimately not that difficult per se to overcome, but just, in my opinion, unnecessarily frustrating. Now, obviously, the rational next step is for me to come up with a more permanent solution to this problem because obviously, WordPress will continue to fill wp_options with logs and Azure will continue to not take my money but as you might have already guessed, that problem is one for Future Me. 🙂

-Dunni

 

Shallah to Nate for sitting on an 8-hour skype/whatsapp call with me to figure all of this out 🙂

Leave a Reply

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