COVID-19 quarantine, day... who knows anymore.

06 July 2020

I have no idea how long I've been in quarantine.  I've stopped counting because the numbers were just making me twitchy.  Life is going about as well as one could reasonably expect.  We're all save and sound in northern California, as much as we can be during a pandemic.  Working from home is working from home.  To minimize risk we're getting as much stuff delivered as we can, modulo periodic trips to the local pharmacy to pick up filled prescriptions and suchlike. I wish I could say the same of things back home in Pennsylvania, but I'd be lying and I'm really not ready to talk about that right now.

I keep thinking of stuff that I want to write about, but everytime I sit down at a keyboard state-dependent memory kicks in and I forget all of it.  It is equally annoying and frustrating when that happens.  So I think I'm just going to ramble a bit and see what pops out.

About two weeks back I started running into pretty serious database problems with Huginn - events weren't being deleted in a timely manner, so much so that the SQL DELETE queries themselves were timing out.  I don't think I've ever seen that happen before.  I spent some time tinkering with the MySQL server configuration, manually expiring events, running various maintenance routines on the database and messing with the configuration.  I eventually came to the conclusion that running a MySQL server with 40 threads plus a full Huginn install with 26 job workers (which kept the processor cores pegged with a system load between 5.0 and 6.0 on the ten minute average) was bogging everything down.  After a day or two of sleeping on it I eventually remembered that Digital Ocean now offers hosted and managed database clusters.  I'm not a DBA; I know just enough about databases to be dangerous so if I can get someone else to do it for me for a good price I'm all over that.

So, I requested a MySQL cluster (one core, one gig of RAM, 10 gigs of disk space), ran one last dump of my databases, and when the cluster was ready I created the databases I needed and reloaded the database dumps.  A little tinkering and re-configuring later and Huginn is running way better now than when I was running the database myself.  Shutting down MySQL on my box freed up a few extra gigabytes of RAM and I don't remember when the system load has been this low (1.49 on the one minute average and 1.5 on the five minute average) in a very long time.  I haven't run into any agents running out of time and terminating, nor have I seen any database errors since then.  Definitely worth an extra $15us a month as far as I'm concerned.

About two weeks back one of the machines in my office that I'd dedicated to the Folding@home and Rosetta@home teams at hackers.town went casters up on me.  The bootable flash drive I'd been running the OS on had finally given up the ghost so I had to replace and rebuild it from scratch.  Unfortunately, after the installation was complete I ran into another weird problem: I couldn't boot anymore.  The keyboard was ignored completely so I couldn't even pick a boot device.  The box in question lay fallow until I felt like pulling it out of the rack and digging into it.  Replacing the CMOS battery did nothing.  So I set a couple of agents loose searching for fixes for a Dell T105 with this problem and eventually they came back with a solution.  The BIOS settings for the T105 occasionally get corrupted, which causes weird malfunctions like what I was seeing.  This particular model of server has a little jumper on the mainboard that forces the NVRAM back to a known-good state (the default factory settings).  So the fix (for anyone out there who needs it in a hurry) is:

  • Kill the power to the server.  Do NOT disconnect the server from mains power!
  • Pop the side of the server case open.
  • Locate the jumper in the bottom-right quadrant of the mainboard labelled "NVRAM."  It should have four pins side by size, the bottommost for normal operation, the uppermost labelled "CLEAR."
  • Move that jumper to the upper pair of pins (CLEAR).
  • Boot the machine.  You'll hear a lengthy sequence of error beeps.  The errors themselves are of no consequence, they come from the fact that you just wiped the (bad) BIOS settings.  I let the server go through this sequence four or five times.
  • Power the server down again.
  • Move the jumper back to the original position.
  • Reboot the machine and get ready to re-do some of your BIOS settings.
  • You should be good.

A few weeks back I finally fixed an annoying problem with my blog that dates back to when I migrated my site off of PivotX (which is no longer maintained) and onto Bolt CMS, namely, PivotX separated tags with commas while Bolt doesn't.  This meant that I had a lot of tags that had commas in them.  So off I went, spelunking in the database backing my website where Lain fears to tread.  Much to my surprise, I discovered that the way Bolt handles tagging of posts is with a single table called bolt_taxonomy which is organized like this:

id  content_id  contenttype  taxonomytype  slug        name       sortorder
--  ----------  -----------  ------------  ----        ----       ---------
7   7           entries      tags          default     default    0
8   7           entries      tags          flattened   flattened  0
9   7           entries      tags          life        life       0
10  7           entries      tags          sickness    sickness   0

Each entry (content_id) appears multiple times, once per tag used on a post.  Each tag has a taxonomytype of 'tags', and both slug and name are the tag itself.  This happens only once in the entire database, in this one table.  So, if one were to do a bulk update for all of the weird_tags, it would be relatively safe to do so with a single query.  After making sure my latest backup was good I logged into the MySQL database at Dreamhost and, with a bunch of Google searches, came up with a query that I thought would match the annoying tags and strip the commas off of the end.  I used the EXPLAIN query to dry-run the update and show me exactly what it would match and what it would do.  The query in question:

mysql> EXPLAIN UPDATE bolt_taxonomy SET name = REPLACE(name, ',', '');
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+-------+----------+-------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+-------+----------+-------+
|  1 | UPDATE      | bolt_taxonomy | NULL       | index | NULL          | PRIMARY | 4       | NULL | 12422 |   100.00 | NULL  |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+-------+----------+-------+
1 row in set (0.05 sec)

(Note: Because I did this a few weeks ago, re-running it returned no results.)

That query returned all of the tags I wanted to edit.  Great.  Then I re-ran that query without the EXPLAIN part and I am (and you are) now short one more annoyance.

mysql> UPDATE bolt_taxonomy SET name = REPLACE(name, ',', '');

And now a quick test to see if there are any tags in that table that have a comma at the end:

mysql> SELECT content_id, name FROM bolt_taxonomy WHERE name LIKE '%,';
Empty set (0.01 sec)

It's looking like I'll be on a panel at HOPE this year.  I have to get approval from multiple levels of management at my day job to be allowed to do this, and it is as slow a process as one might imagine.  Progress is finally being made, which I'm pleased with.

Next up: Another hacking at home project.