Changing database to utf8mb4

[DrQuincy]

I have changed a database over to utfmb4 and it seems to work. I:

• Exported the database
• Replaced utf8 with utf8mb4
• Imported SQL back in
• Added $config->dbCharset = 'utf8mb4'; to config.php

And now I can add Emojis via the CMS. Do I need to do anything else? I know that types like VARCHAR will no longer hold the same amount of characters now some characters take up four bytes but here Ryan says:

Quote

Because utf8mb4 uses more bytes per character, it places new limits on the length of indexes used by ProcessWire. ProcessWire has several 255-character index lengths, and the maximum allowed by utf8mb4 are 250 (250x4=1000). For this reason, we've updated all of our core Fieldtypes to use no more than 250 length indexes in order to support utf8mb4. However, it's possible that 3rd party modules might be using index lengths that aren't compatible with utf8mb4, so this is something to keep in mind.

Modules-wise I am using mainly Pro Fields and core ones. I also notice studying the database schema that most fields would not be affected by this; some use ASCII, most use larger TEXT fields. The few UTF8 VARCHAR fields are system ones set to 250 as above, which from the sounds of it Ryan allocated for years ago. EDIT: I can see the above refers to index lengths and not VARCHAR lengths, the latter can be larger than 250. See my other post.

Am I likely to run into any problems? Other than smaller indexes or a huge VARCHAR column being too big to convert over (highly unlikely) what is the worst that can happen?

[pine3ree]

Innodb tables supports a maximum of 767 bytes for indexes, so floor(767/3) == 255 for utf8(mb3) and floor(767/4) == 191 for utf8mb4. So the index length should be set to 191 max.

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html

Edited August 12, 2022 by pine3ree
added reference

[AAD Web Team]

I was a bit worried about the 191 number, given ProcessWire uses indexes up to 250 characters. However, the maximum length of a utf8mb4 index would be 191 only if the InnoDB table uses compact or redundant row format. If your InnoDB table uses compressed or dynamic row format then you can have up to 768 characters (3,072 bytes) in a utf8mb4 index.

I’d never paid attention to row format before, but I checked all our databases, which are up to 5 years old, and they all use dynamic row format. This SQL command gives the row format information, among other things, for a database:

SHOW TABLE STATUS IN my_database;