Cannot use utf8mb4 in MySQL 5.6

• Type: Bug
• Status: Closed
• Resolution: Won't Fix
• Affects Version/s: 7.0.0 DXP SP2, Master
• Fix Version/s: None
• Component/s: Environments, Environments > Databases
• Labels:
  • QA-V
  • cn-ts
• Fix Priority: 3

`Issue
Cannot use utf8mb4 in MySQL 5.6

Steps to reproduce

1. Prepare a MySQL 5.6 with utf8mb4 collation
2. Make sure the proper my.ini has the following configuration:

   [client]
   default-character-set=utf8mb4
   
   [mysql]
   default-character-set=utf8mb4
   
   [mysqld]
   character-set-server=utf8mb4
   collation-server=utf8mb4_general_ci
   innodb_file_per_table=true
   innodb_file_format=barracuda
   innodb_large_prefix=1
   

3. Make sure the database is set to utf8mb4_general_ci

   mysql> show variables like "%char%";
   +--------------------------+---------------------------------------------------------+
   | Variable_name            | Value                                                   |
   +--------------------------+---------------------------------------------------------+
   | character_set_client     | utf8mb4                                                 |
   | character_set_connection | utf8mb4                                                 |
   | character_set_database   | utf8mb4                                                 |
   | character_set_filesystem | binary                                                  |
   | character_set_results    | utf8mb4                                                 |
   | character_set_server     | utf8mb4                                                 |
   | character_set_system     | utf8                                                    |
   | character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\ |
   +--------------------------+---------------------------------------------------------+
   8 rows in set (0.00 sec)
   
   mysql>
   

4. Prepare a clean de-10 bundle to connect to that db
5. Set the following jdbc to avoid force-using utf8

   jdbc.default.url=jdbc:mysql://liferay-mysql/lportal?dontTrackOpenResources=true&holdResultsOpenOverStatementClose=true&useFastDateParsing=false

6. Start the bundle

Actual results
MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes error is thrown and liferay schema is not initialized.
See attached log for full stacktrace.

Expected results
Liferay schema is initialized successfully and Liferay starts properly

Reproduced in
de-10 + MySQL 5.6.26

Could not reproduce in
de-10 + MySQL 5.7.12

Note1
It could be possible that the issue to be related to innodb_default_row_format parameter.
MySQL 5.6 does not have such parameter, but in 5.7 it does and it is set to DYNAMIC by default in 5.7. Looks like in 5.6 it is forced to COMPACT.

Note2
innodb_large_prefix might also be related. Setting it to innodb_large_prefix=1, innodb_large_prefix=ON or innodb_large_prefix=true did not help.

Note3
MySQL 5.6 is a supported database so it should work as well

Task

1. Is this a Liferay bug or misconfiguration?
2. What configuration is needed to make Liferay work in a utf8mb4 database (in 5.6)?