Worldcubeassociation.org: Upgrade to MySQL 8

[jfly]

@tussosedan has been leading the charge on this project. We spent some time working on this together yesterday. I'm writing up this issue to document our current progress/knowledge, and next steps.

Staging

We tried spinning up a new staging server with MySQL 8.0 (https://github.com/thewca/worldcubeassociation.org/pull/4654), but that didn't work because our chef mysql recipe couldn't figure out how to install MySQL 8.0 on Ubuntu 14. It's not really clear to me from reading the chef mysql recipe docs which versions of Ubuntu it would work on, but we're guessing that it might work if we upgrade to Ubuntu 18 (which is a whole separate project).

Prod

1. First, we restored an old snapshot
   
   
   
   • db.t2.small
   
   
   • Turned off auto minor upgrades
   
   
   • Had to modify the security group to allow access from the prod server
   
   
2. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html
3. We created two parameter groups: default-mysql5-7-two-char-fulltext-min-word-len and default-mysql8-0-two-char-fulltext-min-word-len.
4. In RDS console: click Modify, select DB engine version 5.7.26
5. Select our our 5.7 parameter group: default-mysql5-7-two-char-fulltext-min-word-len
6. The upgrade to 5.7 took just under 20 minutes.
7. Click Modify. Select DB engine version
8. Select our MySQL 8.0 parameter group: default-mysql8-0-two-char-fulltext-min-word-len
9. It looks like having columns named rank causes the upgrade checker to fail*. We dropped these two columns and were able to actually proceed:

mysql> alter table Events drop column `rank`;
mysql> alter table RoundTypes drop column `rank`;

1. Click Modify. Select DB engine version
2. Select our MySQL 8.0 parameter group: default-mysql8-0-two-char-fulltext-min-word-len.
3. The upgrade from 5.7 to 8.0 took just under 20 minutes.

Next steps

We still want to upgrade staging to use MySQL 8.0, but I don't think it's worth the effort of upgrading to Ubuntu 18 as part of that (I think that will be its own big project). So, we'd like to switch staging to use an Amazon RDS instance, which will allow us to upgrade MySQL without upgrading our base os.

In order to work around the rank column naming issue, we're planning to first rename the columns to something else, and then rename them back to rank afterwards. I think it's ok to take the server down for this whole time period (which should be just about 1 hour).

* full output

Executing Compatibility Checks for the MySQL server at localhost:3306.
Source Version: 5.7.26-log - Source distribution, Target Version: 8.0.16.

1) Usage of old temporal type
No issues found.

2) Usage of db objects with names conflicting with reserved keywords in 8.0
The following objects have names that conflict with reserved keywords that are new to 8.0. Ensure queries sent by your applications use `quotes` when referring to them or they will result in errors.
More Information:
https://dev.mysql.com/doc/refman/en/keywords.html

cubing.Events.rank - Column name
cubing.RoundTypes.rank - Column name


3) Usage of utf8mb3 charset
The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.
More Information:
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

cubing.ar_internal_metadata.key - column's default character set: utf8
cubing.ar_internal_metadata.value - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_parents - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_name - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_desc - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_desc_bitfield - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_desc_uid - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_link - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_password - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_image - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_rules - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_rules_link - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_rules_bitfield - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_rules_uid - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_last_post_subject - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_last_poster_name - column's default character set: utf8
cubing.archive_phpbb3_forums.forum_last_poster_colour - column's default character set: utf8
cubing.archive_phpbb3_posts.poster_ip - column's default character set: utf8
cubing.archive_phpbb3_posts.post_username - column's default character set: utf8
cubing.archive_phpbb3_posts.post_subject - column's default character set: utf8
cubing.archive_phpbb3_posts.post_text - column's default character set: utf8
cubing.archive_phpbb3_posts.post_checksum - column's default character set: utf8
cubing.archive_phpbb3_posts.bbcode_bitfield - column's default character set: utf8
cubing.archive_phpbb3_posts.bbcode_uid - column's default character set: utf8
cubing.archive_phpbb3_posts.post_edit_reason - column's default character set: utf8
cubing.archive_phpbb3_posts.post_delete_reason - column's default character set: utf8
cubing.archive_phpbb3_topics.topic_title - column's default character set: utf8
cubing.archive_phpbb3_topics.topic_first_poster_name - column's default character set: utf8
cubing.archive_phpbb3_topics.topic_first_poster_colour - column's default character set: utf8
cubing.archive_phpbb3_topics.topic_last_poster_name - column's default character set: utf8
cubing.archive_phpbb3_topics.topic_last_poster_colour - column's default character set: utf8
cubing.archive_phpbb3_topics.topic_last_post_subject - column's default character set: utf8
cubing.archive_phpbb3_topics.poll_title - column's default character set: utf8
cubing.archive_phpbb3_topics.topic_delete_reason - column's default character set: utf8
cubing.archive_phpbb3_users.user_permissions - column's default character set: utf8
cubing.archive_phpbb3_users.user_ip - column's default character set: utf8
cubing.archive_phpbb3_users.username - column's default character set: utf8
cubing.archive_phpbb3_users.username_clean - column's default character set: utf8
cubing.archive_phpbb3_users.user_password - column's default character set: utf8
cubing.archive_phpbb3_users.user_email - column's default character set: utf8
cubing.archive_phpbb3_users.user_birthday - column's default character set: utf8
cubing.archive_phpbb3_users.user_lastpage - column's default character set: utf8
cubing.archive_phpbb3_users.user_last_confirm_key - column's default character set: utf8
cubing.archive_phpbb3_users.user_lang - column's default character set: utf8
cubing.archive_phpbb3_users.user_timezone - column's default character set: utf8
cubing.archive_phpbb3_users.user_dateformat - column's default character set: utf8
cubing.archive_phpbb3_users.user_colour - column's default character set: utf8
cubing.archive_phpbb3_users.user_topic_sortby_type - column's default character set: utf8
cubing.archive_phpbb3_users.user_topic_sortby_dir - column's default character set: utf8
cubing.archive_phpbb3_users.user_post_sortby_type - column's default character set: utf8
cubing.archive_phpbb3_users.user_post_sortby_dir - column's default character set: utf8
cubing.archive_phpbb3_users.user_avatar - column's default character set: utf8
cubing.archive_phpbb3_users.user_avatar_type - column's default character set: utf8
cubing.archive_phpbb3_users.user_sig - column's default character set: utf8
cubing.archive_phpbb3_users.user_sig_bbcode_uid - column's default character set: utf8
cubing.archive_phpbb3_users.user_sig_bbcode_bitfield - column's default character set: utf8
cubing.archive_phpbb3_users.user_jabber - column's default character set: utf8
cubing.archive_phpbb3_users.user_actkey - column's default character set: utf8
cubing.archive_phpbb3_users.user_newpasswd - column's default character set: utf8
cubing.archive_phpbb3_users.user_form_salt - column's default character set: utf8
cubing.schema_migrations.version - column's default character set: utf8


4) Table names in the mysql schema conflicting with new tables in 8.0
No issues found.

5) Foreign key constraint names longer than 64 characters
No issues found.

6) Usage of obsolete MAXDB sql_mode flag
No issues found.

7) Usage of obsolete sql_mode flags
No issues found.

8) ENUM/SET column definitions containing elements longer than 255 characters
No issues found.

9) Usage of partitioned tables in shared tablespaces
No issues found.

10) Usage of removed functions
No issues found.

11) Usage of removed GROUP BY ASC/DESC syntax
No issues found.

12) Removed system variables for error logging to the system log configuration
No issues found.

13) Removed system variables
No issues found.

14) System variables with new default values
No issues found.

15) Schema inconsistencies resulting from file removal or corruption
No issues found.

16) Issues reported by 'check table x for upgrade' command
No issues found.


Errors: 1
Database Objects Affected: 2
----------------------- END OF LOG ----------------------

[jfly]

Staging is now using RDS with MySQL 5.7. I'm going to try upgrading the RDS instance to MySQL 8.0 now. I'll record the steps I took here.

1. Ran the following SQL on staging db:

ALTER TABLE Events CHANGE `rank` `rank_bak` int(11) NOT NULL DEFAULT '0';
ALTER TABLE RoundTypes CHANGE `rank` `rank_bak` int(11) NOT NULL DEFAULT '0';

1. Go to https://us-west-2.console.aws.amazon.com/rds/home?region=us-west-2#database:id=staging-worldcubeassociation-dot-org;is-cluster=false
2. Click Modify, select DB engine version 8.0.16, select default-mysql8-0-two-char-fulltext-min-word-len parameter group. Click "Continue".
3. On the modification confirmation page, you should see 3 settings change: "DB engine version", "DB parameter group", and "Option group". Select "Apply immediately". Click "Modify DB Instance"
4. Wait....
5. The upgrade failed during the PrePatchCompatibility check. From PrePatchCompatibility.log:

2) Usage of db objects with names conflicting with reserved keywords in 8.0
The following objects have names that conflict with reserved keywords that are new to 8.0. Ensure queries sent by your applications use `quotes` when referring to them or they will result in errors.
More Information:
https://dev.mysql.com/doc/refman/en/keywords.html

wca_development_db_dump.Events.rank - Column name
wca_development_db_dump.RoundTypes.rank - Column name

Our developer's database dump must have been running while trying the upgrade.

1. I tried again (no changes), and the upgrade actually started...
2. After the upgrade finishes, rename the rank columns back:

ALTER TABLE Events RENAME COLUMN `rank_bak` TO `rank`;
ALTER TABLE RoundTypes RENAME COLUMN `rank_bak` TO `rank`;

[jfly]

I'm going to try this upgrade starting at 7am UTC on Tuesday November 5th. I expect the website to be down for ~1 hour while the upgrade happens. I will post updates here.

[jfly]

I'm beginning the upgrade now. I expect to be done within 1 hour.

[jfly]

Database backup created with name pre-mysql-8-upgrade.

Upgrading to MySQL 5.7 now.

[jfly]

Upgrade to MySQL 5.7 done.

Upgrading to MySQL 8.0 now.

[jfly]

MySQL 8.0 upgrade done. I'll bring the server back up now.

[jfly]

Things seem to be working!

[jfly]

No issues reported, so I'm closing this =)