1459069 - (bmo-emoji-test-run) Test MySQL database utf8mb4 row format conversion in stage

Status: RESOLVED FIXED

(bugzilla.mozilla.org :: Infrastructure, enhancement)

Description

[Bob Micheletto [:bobm]]

Convert the Staging BMO database to utf8mb4 and verify that BMO functions correctly afterward.  It might be instructive to add or edit some bugs to highlight potential character set related issues and check them afterward.

1. Snapshot DB for rolling back afterwards
2. Perform conversion: See Bug 1328659 comment 41
3. Test / verify.

Comment 1

[Bob Micheletto [:bobm]]

:dylan, what might be a good date to take over stage for a day or so to test this?

Comment 2

[Dylan Hardison [:dylan] (he/him)]

Let's try to do this right after the next production push.

Comment 3

[Bob Micheletto [:bobm]]

(In reply to Dylan Hardison [:dylan] (he/him) from comment #2)
> Let's try to do this right after the next production push.

Agreed.

Comment 4

[Dylan Hardison [:dylan] (he/him)]

Once bug 1473143 is deployed, we should deploy the same image to staging.

Then we need to go to /editparam.cgi and change the 'utf8' option to 'utf8mb4',
and run checksetup.pl -t -p

This will take a long time, but it will convert all the columns to the dynamic row format (unless they're already DYNAMIC or COMPRESSED) and then convert the character set to utf8mb4.

The utf8 option cannot be changed once it is set to utf8mb4.

Comment 5

[Dylan Hardison [:dylan] (he/him)]

Okay, steps (from a command line only perspective)

./scripts/update_params.pl utf8 utf8mb4
./checksetup.pl -t -p

Checksetup is going to run:

ALTER TABLE $TABLE ROW_FORMAT=dynamic

for every table (that isn't already dynamic or compressed)

and then it will

ALTER TABLE $TABLE DEFAULT CHARACTER SET utf8mb4
ALTER DATABASE $DB_NAME CHARACTER SET utf8mb4

It's possible that some tables would benefit from ROW_FORMAT=compressed.
If (before running checksetup) any tables are converted to ROW_FORMAT=compressed, they will not be converted to dynamic.

The best way of handling this would be 1) knowing which tables are write-heavy, and 2) making all NON-write-heavy tables compressed. Off the top of my head user_request_log and bug_visit_last_blah are the most write-heavy, but probably also logincookies and tokens. YMMV.

Comment 6

[Bob Micheletto [:bobm]]

(In reply to Dylan Hardison [:dylan] (he/him) from comment #5)
This has been completed in stage.  The innodb_large_prefix setting for the parameter group needed to be flipped to on.

Comment 7

[Bob Micheletto [:bobm]]

Closing this bug.