Leveling up

Hey guys, hope you’re doing well. I don’t really have much to talk about in the way of new and exciting features or reworks, as a lot of that is still a work-in-progress and limited by the time I can spend coding. So instead I’ll be talking about a few of the system administrator challenges I’ve had to face lately.

As you might know if you’re a hip computer fellow like myself, the latest version of the Debian distribution was released recently. If you’re a square, then let me sum it up quickly: Debian is one of the oldest Linux-based operating systems and focuses on both stability and on having a wide array of (mostly) libre software available. As it has a large community and support, it’s a good fit for server use. THP uses it to run its board software.  

Debian’s release cycle is about every two years and the idea is that the software in any given version will remain predictable and only really get security updates. No annoying big changes or anything that can mess up the user’s workflow. For the most part, it works. If you’re an user there are ways to work around those limitations but it’s usually not really necessary. This obviously means that shiny new features only come around every now and again but it provides a predictable and stable environment to run your software on.  

I’ve got my complaints about Debian, particularly how it handles default configurations for programs, but that isn’t really what I want to talk about today. Instead, all you really need to know is that the big changes to the system only come about every now and again and any disruptive effects are minimized by how the distribution is structured. Or at least, that’s what’s attempted. So long as you read changelogs for the most important programs, a lot of the pain of updating can be minimized.  

Before updating THP to use the latest versions of software such as PHP, I made sure to look through the biggest changes and incompatibilities upstream. As THP’s board software is old, that meant that I had to update a few bits of code here and there, relatively minor things for the most part. After testing a few things in my local server, I was confident that things were up to scratch. A few warnings remain, but that’s mostly stuff that needs to be changed when behavior changes in the future.  

So, I updated THP’s server to the latest stable Debian release, codenamed Buster. It took all of 20 minutes while I stuffed my greasy sys admin face with a piece of cake and looked at lewd images of [redacted]. As far as updates go—it was pretty painless. I had to manually remove a few bits of old software as well as install a few extensions that were no longer being packaged by Debian but all was well. As with my test system, everything seemed in order.  

That is, until I got complaints a few hours later about posting being broken on the site.

It’s never a good thing to hear that things are borked when you’re sure that you did everything right. It’s double plus ungood when there’s no obvious cause for the error. Logs for the webserver, PHP, the database and others showed nothing new or useful. And it certainly didn’t help that yours truly was away from home and couldn’t really do anything else for a couple of hours more.  

As soon as I was able, I tried to diagnose the problem and narrow down what had just happened. No logs meant that it could be just about anything on the system that was failing silently. All of hundreds of packages that make up an installation were suspect. I tried the most obvious culprits first, installing an older of PHP to see if I had maybe missed updating the code and that was causing an error. Didn’t make a lick of difference. The fact that I was able to post in my own tests was endlessly confounding—it worked, but only sometimes? I wrote up a simple insertion program where I could trace things step by step. I could bore you with details of the things I tried but I’ll keep things short and sweet.  

Database queries were going through but only sometimes so I tried to see if maybe something abuot the database had changed. Turned out, it had. As you can see, the newer versions of MariaDB have something called STIRCT_TRANS_TABLES enabled by default. Without getting into what a database is properly or how it works exaclty, this setting basically means that if all of the data you’re inserting doesn’t cover every column/input the whole insertion fails. Now, a lot of the default values in THP’s database are blank or set to zero. When a post is made, data such as a post message, IP, etc are inserted. Because something like an email isn’t always used, that means that the data row has its email column left blank while others are filled.  

This new setting means that it’s always required to have something, even if that something is zero or a placeholder. So my first workaround was to have default column values for most things. So whenever a post was inserted, even if someone hadn’t used an image, the image field in the database would have a non-null entry in it (blank insofar human readability was concerned). And that did the trick. Posts could be made again!

Except that not always.  

For some reason, not everyone could post. Even making sure that there were default values always (or that the board software would pass a value always to the insertion mechanism) didn’t guarantee that there was a post. More confusingly, it may have had to do with IP addresses. A user reported being to post with a VPN but not without it. This user was not banned. There was no logical reason why posts should go through in one case and not in another, since the same information submitted would be processed in the same manner server-side. A few more strict rules about insertion and default values removed that error for some people. Yet it persisted for others.

There’s no obvious error in THP’s code or the database’s structure. I checked. And read manuals. There is absolutely no reason in the world where it should be contingent on the IP address of the user. So that left a few possibilities: 1) it was a very subtle bug in THP’s code I had no way of discerning, 2) There was a bug in the current version of PHP that made information get inserted in the wrong order/timing so that it triggered the “no default” fail in the database, 3) there was a bug in MariaDB that ran along similar lines, 4) My years of shunning Kanako had finally caused her to punish me.

With things unclear, I erred on the side of safety and abjured the workarounds and went for something simpler. I disabled the setting in the database for strict table data. It’s an unsatisfying end as I always try to do things “properly” but this one is beyond my pay grade. I can’t diagnose upstream bugs, I’m barely competent enough to slowly change THP.  

There was no way of knowing that this issue would trigger even if I had read MariaDB’s documentation closely (I didn’t, full disclaimer) because as far as the board software was concerned, default values were almost always passed when posting. Only case it wouldn’t was if you posted an image-only reply (before I tweaked anything).  

Still, despite my having to stay up late a couple of nights to figure this out, it’s not the worst upgrade I’ve had to do in my many years as a computer wizard. There’s some nice shiny stuff that I can now play with when programming, thanks to newer libraries and software. I was able to turn on WebP support when uploading images. I laid the groundwork for that a while ago but it needed PHP 7.1+ to actually work. A couple of other optimizations also made it in alongside this incredibly popular image format.  

I hope to share more tangible things the next time I write up one of these posts, but we’ll see how the month goes. I’d like to give a shout out to a certain fellow from the Windy City and another strange masked-yet-fluffy individual both on THP’s Discord for helping me try a few different things when troubleshooting.  

Until next time, take it easy!