Kuma: Upgrade MySQL to support UTF-8mb4

[tobinmori]

So that. . .we an support Chinese chars.

[tobinmori]

In reference to this issue:
Internal Server Error occurs when some chapters are translated into Chinese#5940

[peterbe]

See https://github.com/mdn/kuma/issues/6017 for a good way to validate this. Just copy that little padlock emoji in the wiki and try to save.

[tobinmori]

@atopal this task would be a big one, but could potentially turn into a bigger one.

This will mean migrating all our MySQL data to a new version. The potential extra work could come upon hitting many errors during migration or getting munged data as a result. Perhaps, this is low-risk since we're doing an upgrade, but just want to check that you consider this high priority.

Let us know, thanks.

[atopal]

@tobinmori is there a way for us to get a better understanding of the size of this?

[peterbe]

I don't know where posted my original comments on this but...

The commands you need to run to tell MySQL to get it's 👕 together is:

ALTER DATABASE developer_mozilla_org CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE wiki_revision CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE wiki_document CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I tried doing that to my local mysql db and it always fails on the last command with:

MySQL [developer_mozilla_org]> ALTER TABLE wiki_document CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

That makes me wonder if I can simply alter the table to set some longer contraints on certain columns.

[tobinmori]

Generally it seems low(er) risk to me (since we're not switching from MySQL to Postgres). Typically the steps are pretty standard, but there are quite a few and some of the steps can take hours at a time (depending on db size) - off the top of my head:

• export data / db dump (*can take a while depending on db size)
• setup up a new upgraded AWS RDS (not sure how long this would take @limed)
• import data (as a test run) (*can take a while depending on db size)
• watch for any errs thrown
• fix any errs thrown (*this is the unknown part that can vary in time, that could possibly take a while). if errs are thrown you might have to retroactively repair bad data or write transforms in SQL to fix any bad data
• prep configurations in codebase to point to new DB
• replace/update any stored procs if they exist
• update any Django models if they're affected by new datatypes (i don't think that should happen here)
• test import again against new code, if there are any major model changes
• do the switch over: put MDN in read only mode, do an export, do an import (do the fixes you'e previously run into during your initial test run)
• test that things look right

did i miss/forget anything @limed @escattone?

[tobinmori]

@peterbe i was under the (maybe misguided?) impression from @escattone that we'd have to upgrade the whole DB. if we can do an alter then that's great.

[tobinmori]

reading up a little it sounds like this could be the biggest headache (digging in and hand modifying mysql datatypes):

"For example, a TINYTEXT column can hold up to 255 bytes, which correlates to 85 three-byte or 63 four-byte characters. Let’s say you have a TINYTEXT column that uses utf8 but must be able to contain more than 63 characters. Given this requirement, you can’t convert this column to utf8mb4 unless you also change the data type to a longer type such as TEXT — because if you’d try to fill it with four-byte characters, you’d only be able to enter 63 characters, but not more.

The same goes for index keys. The InnoDB storage engine has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8 columns with indexes longer than 191 characters, you will need to index a smaller number of characters when using utf8mb4. (Because of this, I had to change some indexed VARCHAR(255) columns to VARCHAR(191).)"

[peterbe]

So, I ran all those ALTER ... commands mentioned above.

MySQL [developer_mozilla_org]> SELECT character_set_name, column_name FROM information_schema.`COLUMNS` WHERE table_name = "wiki_document" ;
+--------------------+---------------------+
| character_set_name | column_name         |
+--------------------+---------------------+
| NULL               | id                  |
| utf8mb4            | title               |
| utf8mb4            | slug                |
| NULL               | is_template         |
| NULL               | is_redirect         |
| NULL               | is_localizable      |
| utf8mb4            | locale              |
| utf8mb4            | json                |
| utf8mb4            | html                |
| utf8mb4            | rendered_html       |
| utf8mb4            | rendered_errors     |
| NULL               | defer_rendering     |
| NULL               | render_scheduled_at |
| NULL               | render_started_at   |
| NULL               | last_rendered_at    |
| NULL               | render_max_age      |
| NULL               | render_expires      |
| NULL               | deleted             |
| NULL               | modified            |
| utf8mb4            | body_html           |
| utf8mb4            | quick_links_html    |
| utf8mb4            | toc_html            |
| utf8mb4            | summary_html        |
| utf8mb4            | summary_text        |
| NULL               | current_revision_id |
| NULL               | parent_id           |
| NULL               | parent_topic_id     |
| utf8mb4            | uuid                |
+--------------------+---------------------+
28 rows in set (0.001 sec)

MySQL [developer_mozilla_org]> SELECT character_set_name, column_name FROM information_schema.`COLUMNS` WHERE table_name = "wiki_revision" ;
+--------------------+------------------------+
| character_set_name | column_name            |
+--------------------+------------------------+
| NULL               | id                     |
| utf8mb4            | title                  |
| utf8mb4            | slug                   |
| utf8mb4            | summary                |
| utf8mb4            | content                |
| utf8mb4            | keywords               |
| utf8mb4            | tags                   |
| NULL               | toc_depth              |
| NULL               | render_max_age         |
| NULL               | created                |
| utf8mb4            | comment                |
| NULL               | is_approved            |
| NULL               | is_mindtouch_migration |
| NULL               | based_on_id            |
| NULL               | creator_id             |
| NULL               | document_id            |
| utf8mb4            | tidied_content         |
+--------------------+------------------------+
17 rows in set (0.001 sec)

(To make it work, I had to also change some varchar(255) to varchar(191) because Stack Overflow told me)

Sadly, I still can't put 🔐 into the wiki. Even after this change.

I'm wondering what difference it'll be to switch to Python3 and what effects that might have on our MySQL driver.

[escattone]

@peterbe @tobin Yeah, I was originally thinking we should alter the entire database, just for consistency sake, but maybe we should just do what @peterbe has outlined? Personally, I don't think this task is worth the effort, since we've lived with this limitation already for years and I'm hoping we will be able to move to GitHub within the next 6 months anyway.

[escattone]

@peterbe @tobin i'm worried that this will take more time that it seems on the surface due to the follow-on effects. For example, we have to change a set of columns from varchar(255) to varchar(191) to stay within the index key limit, and then that leads to further changes to tests, and so on.

[peterbe]

For example, we have to change a set of columns from varchar(255) to varchar(191) to stay within the index key limit, and then that leads to further changes to tests, and so on.

No guarantee that that will actually work. I did that stuff to my local mysql but I could still not save with a big emoji. It could be due to some other UnicodeDecodeError which might be hopeful.

[atopal]

Thanks for looking into this. I agree it doesn't seem like its worth the effort at this time.