[Solved] EOS7.6 Error: Init DB failed [Specified key was too long; max key length is 767 bytes…

Init DB failed![Specified key was too long; max key length is 767 bytes[CREATE INDEX IDX_WFWI_PARTICI ON WFWORKITEM( PARTICIPANT )]
Specified key was too long; max key length is 767 bytes[CREATE INDEX WTRI_EXPAT ON WFTASKRESOURCEINFO(EXCLUDEUNIQUEID)]
Specified key was too long; max key length is 767 bytes[CREATE INDEX WTRI_COMP ON WFTASKRESOURCEINFO(QUEUENAME,STATUS,SERVERID)]
]


– select 'yes' for retry and' no 'for exit

 

If the system variable innodb_large_prefix is enabled (enabled by default, it is off by default on my computer installation of MySQL 5.6.41, and on by default on MySQL 5.7), the index key prefix is limited to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix is limited to 767 bytes for any table in row format.

innodb_large_prefix will be removed and deprecated in future releases. The innodb_large_prefix was introduced in MySQL 5.5 to disable large prefix indexes for compatibility with earlier versions of InnoDB that did not support large index key prefixes.

For InnoDB tables using REDUNDANT or COMPACT row formats, the index key prefix length is limited to 767 bytes. For example, you might reach this limit using a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and a maximum of 3 bytes per character.

Attempts to use an index key prefix length that exceeds the limit will return an error. To avoid such errors in replication configurations, avoid enabling enableinnodb_large_prefix on the master server (if it cannot be enabled on the slave server).

The restrictions that apply to index key prefixes also apply to full column index keys.

Note: The above is 767 bytes, not characters, specifically the number of characters, which is related to the character set. gbk is double-byte, utf-8 is triple-byte

Solutions.

1: Enable the system variable innodb_large_prefix
Note: It is not enough to have this system variable enabled. The following conditions must be met.

2: System variable innodb_large_prefix is ON

3: System variable innodb_file_format is Barracuda

4: ROW_FORMAT is DYNAMIC or COMPRESSED

mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like '%innodb_file_format%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
+--------------------------+----------+
3 rows in set (0.00 sec)

mysql> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_file_format_max=BARRACUDA;
Query OK, 0 rows affected (0.01 sec)

After completing the above operations, EOS platform 7.6 is successfully installed.

Read More: