Cms: MySQL: Switch to `utf8mb4` charset

[bramus]

Description

Hi, I'm a potential new Craft CMS user who has played with _(and created)_ many other PHP apps, databases, and CMSes before. I'm now in the process of checking out if Craft CMS is suitable for a possible new client project.

Whilst reading the docs I noticed that the default DB charset is set to utf8, and not utf8mb4. I find this worrying, as MySQL's utf8 does not support all UTF-8 symbols _(See https://mathiasbynens.be/notes/mysql-utf8mb4 for a full writeup on this subject)_.

In commits like https://github.com/craftcms/cms/commit/3c5323e35167a1d5eebbc0ac4bbb45621a76f8ef I see some workarounds for this involving StringHelper::encodeMb4(). Whilst this commit does the job, it looks like an improper fix to me as is fixes a symptom, not the cause. As a result, I guess, all plugins and other things written for Craft will also need to implement this kind of workaround.

I hereby would like to suggest to change the default (MySQL) charset to utf8mb4, as it tackles the problem at the root cause. Furthermore this would also solve issues like https://github.com/craftcms/cms/issues/2513

Steps to reproduce

N/A

Additional info

• Craft version: all
• PHP version: any
• Database driver & version: any MySQL server
• Plugins & versions: all

[angrybrad]

In the context of Craft 2, we can't because utf8mb4 didn't get added to MySQL until 5.5.3 and Craft 2 supports back to 5.1.

In the context of Craft 3, we do require MySQL 5.5+ so it's possible. It will require is to re-think our indexing strategy on many tables as going from 3 to 4 bytes puts many of our indexes over the default 767 byte key limit: https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

Definitely open to this, though.

[whvandervelde]

@angrybrad If its about the key length this should be ok if you're using INNODB and can modify some DB settings. You will need to set innodb_large_prefix = 1, change the row_formats to for example dynamic for those tables (probably all of them then?) using Barracuda and it should work. I haven't tested this with craft3, but afaik that's the "normal" route to solve that.