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 3c5323e 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 #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.