index created for freepbx_log table exceeds key limit in mysql 5.6.43

I grabbed the freepbx-15.0-latest.tgz tar file on April 15, 2019.  So I think that's something like 15-beta.

 

During the installation onto my machine, running 'install -f', the freepbx_log table fails to be created, with the following error:

{noformat}
Updating tables admin, ampusers, cronmanager, featurecodes, freepbx_log, freepbx_settings, globals, module_xml, modules, notifications...
In DBALException.php line 131:
                                                                                               
  An exception occurred while executing 'CREATE TABLE freepbx_log (id INT AUTO_INCREMENT NOT   
  NULL, time DATETIME NOT NULL, section VARCHAR(50) DEFAULT NULL, level VARCHAR(255) DEFAULT   
  'error' NOT NULL, status INT DEFAULT 0 NOT NULL, message LONGTEXT NOT NULL, INDEX time (tim 
  e, level), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE 
   = InnoDB':                                                                                  
                                                                      SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes                                                     
In Connection.php line 1044:  
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes                                 {noformat}

 

In my research of the problem, this appears to be a natural limit of mysql 5.6 (regardless of platform).  The actual part of the table definition that is causing the problem is the creation of the index:

{noformat}
index time(time, level){noformat}

The "level" column is varchar(255), which when expanded to 4 bytes per char, due to the locale specification for the table (utf8mb4), is too large for the index to be created.

 

Comparing this to a FreePBX 13.x installation, where the table looks like:

{noformat}
mysql> describe freepbx_log;
----------------------------------------------------------------------------------------------+
| Field   | Type                                          | Null | Key | Default             | Extra          |
----------------------------------------------------------------------------------------------+
| id      | int(11)                                       | NO   | PRI | NULL                | auto_increment |
| time    | datetime                                      | NO   | MUL | 0000-00-00 00:00:00 |                |
| section | varchar(50)                                   | YES  |     | NULL                |                |
| level   | enum('error','warning','debug','devel-debug') | NO   |     | error               |                |
| status  | int(11)                                       | NO   |     | 0                   |                |
| message | text                                          | NO   |     | NULL                |                |
----------------------------------------------------------------------------------------------+
6 rows in set (0.02 sec) {noformat}

Obviously, the "level" column is quite a bit smaller there.

By changing the sql to be executed to only have the "level" column be smaller, the table creation completes. Changing 'level' to be of varchar(191) or smaller work, varchar(192) fails.

This is consistent with various explanations I found about this particular mysql error.