[PLEASE READ] Recent Forum Issues & Future Plans. (Last updated 26/FEB/2020)

[Lizzian 46]

Just a heads up, this is going to be a bit of a long post with a bit of technical information mixed in.

Forum access over the past few days

You may have tried to access the forums over the last few days (13th - 15th of January, 2020) and been met with a message that read something like "Forums are currently offline due to database corruption".
Whilst the forums are now back up again, the database corruption is still here. I'll get into the specifics in a bit but first a bit of context:

Context:

Over the period of the last month, I have been migrating to a new (and cheaper) dedicated server. The forums' files were moved over last weekend but they've been pointing at my database cluster for the past few weeks. Unfortunately, what wasn't apparent to me at the time (and I only realised it yesterday), is that the OC Forums' Database has a mix of InnoDB tables (good, they can replicate) and MyISAM tables (bad, they don't replicate).
This has resulted in the database servers printing to their respective consoles, that various tables within the Forum's database are marked as crashed and should be repaired.
After a while (or a few requests to the same table, I'm not entirely sure what causes it), it also causes the node that was being accessed to completely crash out, resulting in cutting off anything else that also happened to be using that node at the time. I've limited the forums to one node so the impact is lessened, but it still takes some time when Systemd restarts the downed node and gets it re-synced with the surviving node.
I've also tried repairing the tables using various mysqlcheck commands, which didn't succeed. I found an option in the config to use "InnoDB" as the table storage method, but any combination of activating that and converting the MyISAM tables to InnoDB ones just ends up in either more crashes or stuff not even loading at all. Nothing I have tried is getting it back to how it was before all my migrations started a month ago.

I do have some backups, but they're either from over a month ago, or already contain the corruption. Going forward, there'll be daily snapshots of the database nodes, but that doesn't help fix things retroactively sadly.

What's the TL;DR?

As it stands right now, the databases are corrupted and the frequency of the crashes is only going to get worse. One of the tasks that is currently "stuck" (and trying to manually run kills the database) is the cache clearing process. The table it accesses is a MyISAM-backed one.
With no way to convert the tables or fix the corruption and the only way to "back up" the data of the forum is to take a snapshot of the (corrupted) database, there isn't a lot of options to be able to do a backup-wipe-and-reload whilst keeping all the data.

Future plans and fixes

To those of you who have been giving some domains towards the license costs, Thank you! They are a good help. The amount on the sidebar is currently a little off because trying to update the value end up killing the Database node, which is what alerted me to this issue in the first place (it doesn't auto-update annoyingly).

So what's the plan?

Right now? I honestly don't know. I'm contemplating switching to a different forum software, preferably something free/open source. But moving all the topics, discussions and userdata over is going to be a pain, even more so when the database is in a fragile state.
I was looking into seeing if I could get the Wayback Machine to snapshot the entirety of the forums, but their site for snap-shotting everything confused me. (If you know more about this, please @Lizzian me on either the Discord guild or IRC). AFAIK the Wayback Machine allows you to snapshot single pages, so if you want to snapshot random pages/threads, please do.

 

I'll be leaving this topic open for replies (if permissions allow you to reply, I don't know if you can and the database might die again if I try to look), so if you have any questions either reply to this thread or poke me on IRC/Discord.

In writing all of the above, the database node that the forums is pointing to has died a total of 5 times so far and even made the container that the node was running in crash.

 

Edit as of 24th of January: I think I have possibly fixed some of the issues? The cache table (which was at a whopping 18GB or so when I was trying to fiddle with stuff last week) has now been manually cleared out. Had to do that 2k rows at a time or the mysql client and server got killed for exceeding the container's allocated RAM amount. The tasks are now running smoothly and so far (it's only been 10 minutes since i did it) there have been no errors in the console!

 

Update as of the 26th of February: It would appear that the forums has deleted my last two updates to this post and also asked me to "update" to the latest version again today, despite me doing it yesterday....
I am confident that the mix of MyISAM and InnoDB tables are to cause for this weirdness. I am now going to look at replacements. In the mean time, please make notes of stuff you post cause it could end up going poof

[PixelPi 0]

I regularly work with MySQL databases. Mainly I operate with self-written Perl scripts on them. If I could help in any way, I would be happy to do so. 

[Lizzian 46]

On 1/19/2020 at 2:52 PM, PixelPi said:

I regularly work with MySQL databases. Mainly I operate with self-written Perl scripts on them. If I could help in any way, I would be happy to do so. 

Thanks for the offer, I think I've got it working properly now (see my edit on the bottom of the OP). If it ends up being an issue again I may call for help

[Lizzian 46]

Update as of the 26th of February: It would appear that the forums has deleted my last two updates to the OP of this thread and also asked me to "update" to the latest version again today, despite me doing it yesterday....
I am confident that the mix of MyISAM and InnoDB tables are to cause for this weirdness. I am now going to look at replacements. In the mean time, please make notes of stuff you post cause it could end up going poof