Support MySQL 8.0 now that it is GA
MySQL 8.0 went General Availability on April 19, 2018. CiviCRM's aim is to support the latest GA for required infrastructure (MySQL, PHP) as soon as feasible.
In reviewing removed features that cause incompatibilities (https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html) I noticed a couple of issues that definitely require changes, one that might cause syntax errors that are easily fixed when found, and a fourth that we may want to change to improve our functionality and get current.
Issue: Field Names now Reserved Words
The new reserved words includes two which we use as field names:
While our code to create the fields will work because it uses ` to quote them as identifiers, we'll likely want to either change these names or go through the code base and add the backtick quotes everywhere. Either approach will still mean that extensions will have to make changes to become MySQL 8.0 compatible. Using backticks will mean that extensions can continue to run on earlier versions of MySQL, and is likely the better approach. We don't want to force all extension maintainers to change references to these fields all at once when only a small number of installations will be using MySQL 8.0.
Testing issue: dependence on PHP version for MySQL authentication
Upgraders will have to deal with hassle of changes to MySQL user password library (https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password) but that doesn't affect our code. We may need to adjust some of our test infrastructure for PHP <7.1.16 or PHP 7.2 less than 7.2.4 as per instructions at http://php.net/manual/en/ref.pdo-mysql.php .
Possible issue: Sort order on GROUP BY clauses
I don't think we have any code that tries to specify sort order on GROUP BY fields, but if so we will need to change to add an ORDER BY clause with the sort order. I think we should just try running tests on MySQL 8.0 and using it manually to determine locations in code with this issue, and play whack-a-mole on anything missed.
Nice to have: Upgrade our text fields to support all utf8 characters
I think now is a good time to consider shifting from charset utf8 (which only supports 3 bytes and doesn't fully implement the UTF8 standard https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434) to the new default charset of utf8mb4, and at same time change the collation from utf8_unicode_ci to the new default of utf8mb4_0900_ai_ci. This will allow new emoticons as well as more languages and their characters to be properly stored in CiviCRM. My sense is that this is not difficult to code in GenCode. However, extensions that define fields in other collations and then compare them to core fields will likely need to be modified. Maybe an extension that changes the charset and collation of all core fields, and that hooks into the dynamic creation of fields for custom fields, is a way to start down this path. (@bgm ^)