Page MenuHomePhabricator

Mailman3 schema change: change utf8 columns to utf8mb4
Closed, ResolvedPublic


As discussed in T282271: daily-article-l@, education@ import to Mailman3 failed because of unicode characters in display name, we need to switch the charsets in various Mailman3 tables from utf8 to utf8mb4. We are also working with upstream on this as well:

  • ALTER:
    1ALTER TABLE address MODIFY display_name VARCHAR(255) COLLATE utf8mb4_bin NULL;
    3ALTER TABLE domain MODIFY description VARCHAR(255) COLLATE utf8mb4_bin NULL;
    5ALTER TABLE headermatch MODIFY pattern VARCHAR(255) COLLATE utf8mb4_bin NULL;
    7ALTER TABLE mailinglist MODIFY autoresponse_owner_text VARCHAR(255) COLLATE utf8mb4_bin NULL;
    9ALTER TABLE mailinglist MODIFY autoresponse_postings_text VARCHAR(255) COLLATE utf8mb4_bin NULL;
    11ALTER TABLE mailinglist MODIFY autoresponse_request_text VARCHAR(255) COLLATE utf8mb4_bin NULL;
    13ALTER TABLE mailinglist MODIFY bounce_matching_headers VARCHAR(255) COLLATE utf8mb4_bin NULL;
    15ALTER TABLE mailinglist MODIFY dmarc_moderation_notice VARCHAR(510) COLLATE utf8mb4_bin NULL;
    17ALTER TABLE mailinglist MODIFY dmarc_wrapped_message_text VARCHAR(510) COLLATE utf8mb4_bin NULL;
    19ALTER TABLE mailinglist MODIFY description VARCHAR(255) COLLATE utf8mb4_bin NULL;
    21ALTER TABLE mailinglist MODIFY member_moderation_notice VARCHAR(255) COLLATE utf8mb4_bin NULL;
    23ALTER TABLE mailinglist MODIFY nonmember_rejection_notice VARCHAR(255) COLLATE utf8mb4_bin NULL;
    25ALTER TABLE mailinglist MODIFY display_name VARCHAR(255) COLLATE utf8mb4_bin NULL;
    27ALTER TABLE mailinglist MODIFY subject_prefix VARCHAR(255) COLLATE utf8mb4_bin NULL;
    29ALTER TABLE user MODIFY display_name VARCHAR(255) COLLATE utf8mb4_bin NULL;
    31ALTER TABLE user MODIFY password VARCHAR(255) COLLATE utf8mb4_bin NULL;
    33ALTER TABLE workflowstate MODIFY data VARCHAR(510) COLLATE utf8mb4_bin NULL;
  • Where: mailman3 on m5
  • When: no code dependency, but we will need to announce a time as it will require Mailman3 downtime
  • Backwards compatible: yes
  • Tested already: Yes, in Cloud VPS

Event Timeline

Wouldn't a message in tech news be enough?

LSobanski moved this task from Triage to Pending comment on the DBA board.
LSobanski added a subscriber: LSobanski.

Assigning to Manuel for review, can be moved to Blocked afterwards until the announcement goes out.

Wouldn't a message in tech news be enough?

tbh unless we expect it to be like more than 30 minutes, I was thinking just a notice to listadmins-announce would be good enough. And if it's just going to take a minute or two maybe we don't even bother announcing it. I don't really know how long it'll take, maybe DBAs can shed some light on that?

I agree, it's just better safe than sorry. Maybe it errors out and DBAs need some time (=complications). Definitely not a huge announcement but a line in tech news is better than nothing IMO.

From what I can see the tables aren't huge, so it might not take a long time.
However, on the last time we had to alter some tables here, we did have to put mailman under maintenance as otherwise it was impossible to acquire the data locking. These alters aren't online, so tables will be blocked for a few seconds/minutes.
My recommendation would be to announce a maintenance window.

One day early in the morning works for me, let me know which dates/time work for you

How about 2021-05-19 06:00 UTC? Or any other day at that time

Note: I reduced the lists of alters based on discussions with upstream, which wants to keep MySQL 5.6 compat and therefore we won't be converting any columns that are indexed.

Proposed announcement:

Dear listadmins,

There will be some downtime on tomorrow ($day).

When: $time, for about 30 minutes (unlikely to actually take that long)

What: Emails will be queued and not delivered until maintenance finishes. The web interface will not work at this time.

Why: We need to apply some database schema changes to unblock migrating the final lists. <>

If you want to follow along we'll be in #wikimedia-operations on IRC. Let us know if you have any questions.
Please forward/cross-post this message as needed.

How about 2021-05-19 06:00 UTC? Or any other day at that time

Works for me!

Marostegui moved this task from Blocked to Ready on the DBA board.

Yay, thank you! In conclusion the schema changes themselves took a few seconds and we had about 3 minutes of downtime and you should now be able to use emojis and other 4-byte unicode language characters just about everywhere in Mailman3.