Use mysql-8.0 for the site

[cgolubi1]

When this is ready to merge, it will fix #2928

I think there are several remaining pieces here:

1. Gain confidence that i understand how to maintain database access across this upgrade
2. Rough edges or bugs that need to be addressed
3. Replay testing
4. Dev testing

Database access across upgrade, notes:

• So far i've tested:
  • Standing up a new site on mysql-8 with no database and creating databases from scratch (locally under docker)
  • Standing up a dev site running mysql-8 and having it load a staging database backup recorded on mysql-5
• I have not yet tested:
  • Standing up a dev site running mysql-8 and having it load a staging database backup recorded on mysql-8 (should work, but needs to be tested)
  • Cross-version access to a remote database, e.g. when we do the actual migration, the upgrade of the RDS database won't happen at the same moment as the replacement of the site container, so we need one of: (A) mysql-5 client works with mysql-8 server, (B) mysql-8 client works with mysql-5 server, (C) extended downtime while we replace both. I'll have to experiment and pick one.
• In addition, the staging backup load didn't initially work --- on the dev site i created on 2024-06-15, the database load finished at 04:37 UTC, at 04:39 PHP database access failed, at 14:29 MySQL restarted, and at 18:20, i was able to successfully access the site. (Apache did not restart during this time period.) So open questions:
  • Did the MySQL restart cause PHP/apache to become able to access the database?
  • Why did MySQL restart? It looks like it was killed --- is there a problem there?

Rough edges:

• I had to copy in a version of /etc/init.d/mysql after the apt update, and that seemed to work, but it doesn't seem right, and if it's due to an error on my end, it may cause other problems later.

Anyway, all of that notwithstanding, the reason i'm putting up the draft PR is so we can get some replay and dev testing:

• The dev site is at https://2928-mysql-80.cgolubi1.dev.buttonweavers.com/ui/ and it would be great if people could check it out and report on anything weird you see.

[blackshadowshade]

I've played a couple of turns, made a post in a forum, created a new test thread, done a search in History. All looks good.

[cgolubi1]

Thanks for checking. I can't login now, because mysqld got killed, and then got killed again on restart.

Looking at metrics, i strongly suspect these are OOM kills, and that MySQL-8.0 just needs more memory than MySQL-5.7. (We might be able to mitigate that by tweaking something, but one thing at a time.)

[No advice needed on this atm; i have my next set of steps. Just commenting so other people who might try the dev site won't be surprised when they can't login.]

[cgolubi1]

After increasing memory from 512Mb to 1024Mb, i was able to kick off replay testing right away. Let's let that run for awhile and see how it does.

[cgolubi1]

I rebuilt the dev site, but i had to load the database again after build, because mysqld hadn't finished starting up at the point that the script tried to load it. So:

• the dev site should be active now
• i need to figure out what's up with the timing there, because in particular either it means mysqld startup takes long enough that we need to plan around it, or it means there was a crash in there that i don't understand yet.

[dwvanstone]

I changed preferences, added to the forums, searched the corners of history, created a game, took a turn in a game ... everything looks good!

[cgolubi1]

Thanks, dwvanstone. I'm going to tear down and rebuild the dev site a few times to try to reproduce the issue where it takes mysql awhile to start up and thus the post-install database load fails.

[cgolubi1]

Recapping next steps:

• Try changing the line in mysql_apt_config.dat.erb that i flagged earlier, and see if that gets away from having to install mysql's init.d script by hand
• Do something to work around staging database loads on dev sites apparently not always succeeding --- the right thing to do is add a check for whether mysqld is running yet prior to trying to run mysql commands, and if it's not wait a bit, and eventually fail
• Make a cutover plan for an RDS database, that i can test using the dev RDS site

[cgolubi1]

Modifying the apt config script didn't seem to change anything; i still have to manually install an init script. Weird, but i don't have a good thought about what to do about it, so i'm going to not worry.

[cgolubi1]

Okay, i think https://2928-mysql-80.cgolubi1.dev.buttonweavers.com/ui/index.html is now using a mysql-8.0 client to talk to the remote mysql-5.7 RDS server. This is good to know if it works, because if it does, we can do the deployment with less downtime, by first pushing the new client and then upgrading the database. So i'm going to let that run for awhile, and if folks could kick the tires a bit, that'd be great.

[dwvanstone]

The tires have been successfully kicked. Everything looks good so far!

[cgolubi1]

Okay, so backups don't work with an 8.0 client and a 5.7 DB:

$ sudo  /usr/local/bin/backup_buttonmen_database
mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'buttonmen' AND TABLE_NAME = 'button';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

That's not worth figuring out; i'm just looking for an order of operations. It seems like the site works as far as we can tell, and that should be good enough.

[cgolubi1]

Okay! I've now upgraded the RDS site used by https://2928-mysql-80.cgolubi1.dev.buttonweavers.com/ui/index.html from:

• MySQL 5.7.44 -> 8.0.28
• t2.micro -> t3.micro (forced instance type upgrade)
• SSL cert CA that was about to expire -> new one that should be good until 2061 (note: players don't interact with this SSL connection --- it's just between the site and the DB; so i think i've already tested that it's okay and there's nothing else to see here.)

After these changes, i successfully:

• accessed the DB via cli
• ran a DB backup
• used the site

Can other folks kick the tires on the site again now? If this looks good, i'm going to put this up for PR.

[dwvanstone]

Tires kicked some more and seem fine.

[cgolubi1]

Thanks! I'm marking this ready to be reviewed for merge, and if it looks good, we'll deploy it out to staging and prod next weekend.

[blackshadowshade]

I can confirm that we have files that are only to do with the deployment, rebased into a single commit. The files look reasonable, but I must admit to being unable to adequately review the new mysql configuration and delaying stuff.

Is there someone else who can run their eyes over the new code here and give some meaningful feedback? @irilyth?

[cgolubi1]

Here's the gist of what the new/changed files do, if it helps:

• create_databases.erb: mysql-8.0 treats users as separate first-class entities, instead of just being a side effect of grants. so when we create databases from scratch for local sites, we have to create the users if they don't exist, and then grant them to the databases separately. (for RDS, the upgrade process takes care of this for us, so we don't have to do anything.)
• mysql/manifests/init.pp: install all the new files (more about them below). Run the custom script configure_mysql_apt before trying to install mysql-server or mysql-client. Use some extra flags (--allow-unauthenticated and -f) when using apt to install mysql software, because the configuration we're using to get mysql-8.0 requires those flags. (This isn't 100% ideal, but it's basically fine; it's a side effect of the APT repo i was able to find which allowed us to install mysql-8.0 on our ancient Ubuntu version, so all of that's a bit hackish, but IMO the right answer is to make a plan to upgrade our Ubuntu version in the nearish future rather than worry too much about this step.)
• buttonmen.cnf.erb: changes to SQL parameters mysql-8.0 supports. I think you know as much about these as i do. The setting of default-authentication-plugin is to allow us to continue to use passwords for connection as we do under mysql-5.7, as that's no longer the default under mysql-8.0.
• configure_mysql_apt.erb: a custom script which downloads APT configuration for mysql-8.0 that works on our OS from dev.mysql.org, then installs that APT configuration using some custom system config settings (more below), then runs apt-get update so future installation activities will use the new versions. The purpose of this script is to override our OS default of mysql-5.7 when installing packages, so that we're able to install mysql-8.0 using mostly-normal apt commands.
• mysql_apt_config.dat.erb: apt settings which say we would prefer mysql-8.0. apt-config installs these. I think for all this apt version configuration stuff, you can take the fact that this works (when we install containers with this code, those containers come up with mysql-8.0) as proof that it's syntactically reasonable, if that makes sense.
• init_mysql.erb: the one thing this new stuff doesn't seem to do is install /etc/init.d/mysql, which is the script needed to launch mysqld at container startup. I don't really understand why not, but i copied this one from our mysql-5.7 installation, and, again, the fact that it works is proof that it works. (Also note that we only run mysqld on dev sites, not on staging or prod, so if this goes totally wrong, it can't break staging and prod, which only need mysql-client.)

[blackshadowshade]

Okay, let's give this a go. The one file that I was somewhat uncertain about was the init_mysql.erb, and the fact that breaking mysqld doesn't affect staging or prod means I'm much happier with not exactly understanding what's going on there.