January 15

Hi everyone, hope you're well. Had a pretty rough two weeks, but I got a decent amount done, nonetheless. Updates below!

Documentation server

I’ve added more documentation about potential pitfalls when upgrading LAMP stack webservers (various oddities in PHP versions), and other infrastructure documentation for other art site owners.

Also added information about potential caching pitfalls, including with search providers, and frontend pitfalls with jQuery and CSS animations.

DB improvements and testing

I sorted out rotating the SSL certificates for Deserted Chateau’s test database server, as the old RDS certificate expires in the middle of 2024 (not sure why AWS sets it as the default). Much easier than expected, didn’t cause any issues.

I also fixed an issue where on occasion, I’d get an error saying the maximum number of connections to the database was being exceeded. Turned out to be a basic error: when making the messaging server, I’d set the connection pool to have a limit of 25 connections, and so it was using 25 connections all the time on standby; the max connections for t4g.micro AWS instances is 31, meaning only a few were left available. I’ve documented this in the docs server, and also did a bit of testing as to the max_connections value for different instance types.

I also tested upgrading the MariaDB version on the test database. For minor version upgrades this is easy (and AWS lets you schedule it automatically), but major version upgrades are actually a bit weirder than expected, due to a bug in the AWS browser console. For whatever reason, it won’t let you set a flag that’s required to perform a major upgrade, so you have to do it in AWS CloudShell with a manual command. I’ve documented the details of that in the docs server, but it worked fine.

Redis & Caching

I fixed a bug that was causing the cache server to have a ton of PHP web sessions open, even though only I was browsing (and so only a handful of sessions would be likely to exist at any one time). After some investigation, I realised that the load balancer’s health checker was pinging the server every couple of seconds from a large variety of different IP addresses, causing the PHP code to create a new session for it every time.

As the health checker always sends a specific User-Agent header, fixing this was simple: checking the User-Agent header for the health checker’s identity, and not creating a session when the health checker is the source of an incoming request. Documented in the docs server.

TinyMCE profile pages

Making highly customisable profile pages has been proving difficult. It’s doable, but a lot of work, so I might start with something more simple, perhaps, or introduce more types of them after launch or something, we’ll see.

One difficulty to get past was that most WAF configurations block HTML bodies, such as rich text editor content, for XSS reasons. In order to get past this, I had to design a system where the TinyMCE content is encoded as a base64-string, allowing it to go through the WAF safely, and then run the decoded HTML through the HTMLPurifier library in PHP when it arrives on the server.

That in itself is a little awkward, less for security reasons and more for the fact that HTMLPurifier is very strict, and so configuring it not to remove fairly innocent tags or HTML entities is complicated. On the plus side, this means not overriding the WAF’s XSS rules, which would be a security vulnerability if left that way.

Financial Spreadsheet Updates

I’ve redesigned the financial estimates spreadsheet, to be clearer - each section of costs is in its own sheet, and the main sheet contains estimates and then calculates the total costings based on the other sheets and those estimates. This makes it easier to separate out different costs, and to more easily see how different scenarios would play out.

You can find the updated version here: https://docs.google.com/spreadsheets/d/131YqanaKd5SsPLaz8zszxltzPczbvFdRaeAzUUhoUYQ/edit?usp=sharing 

HTML / JS / CSS improvements

I’ve been investigating a bug with fading in galleries on user profile pages: it’s a minor annoyance where they can sometimes fade in a little slowly, which I think is either to do with TinyMCE description areas loading, or to do with a previous code bug where I was effectively fading in the gallery twice if it was the main landing page for a browser viewing a profile. I’ll keep an eye on this and see if it persists.

I also fixed a minor bug in the private messages area, where message windows didn’t scroll fully to the bottom due to TinyMCE loading after the scroll (and the TinyMCE windows slightly increasing the total pane size).

Gallery code improvements

I’ve finally removed all of the older SQL queries, aside from the main gallery search one: all gallery queries now go through one function and one stored procedure, and use one uniform set of search parameters which they are cached with. I also added some more customisation to how the parameters can be used, to allow for “match these tags OR these words” behaviour rather than only ‘match if all of these conditions are met’.

Artwork submission improvements

In the backend part of artwork uploads, the encoding/resizing was until now being treated as a part of the upload process. Functionally that works, but means that the frontend interface for users would sit stuck at ‘nearly finished’ status for a few moments (or longer, for video uploads) due to the file uploader not being able to tell what was going on.

I’ve separated out the logic for uploads and resizing in the frontend and backend code, so the user gets a clearer progress display, and can be informed that encoding/resizing is occurring.

Moderation Pages

I’ve started properly implementing the moderation side of the site now. I restructured some of the database tables, cleaned up some of the frontend appearance, and have started making templates and code for dealing with artwork reports.

I imagine that’s going to take a bit of time, as there’s a lot of structures that need to be made and layout to design, but other than that it should be fairly straightforward.

Lambda functions

I moved the existing Lambda functions for image and video resizing to the Node.js 20.x runtime (from the older 16.x runtime). AWS is slowly phasing out support for 16.x in June 2024, so this was a good time to test the upgrade.

A pitfall that I ran into here is that the built-in AWS JavaScript API changes versions - in runtimes 16.x and below, it uses the v2 JavaScript API, and in versions newer than that, the v3 API. I had to modify some of the code to use the v3 API, as it isn’t fully backwards compatible, but that is done now.