`Syntax error or access violation: 1069 Too many keys specified; max 64 keys` allowed when adding new language

[adrianbj]

Short description of the issue

Creating a new language results in this error. Note that the system has 20 languages, so maybe that's the issue.

ALTER TABLE fieldtype_options ADD UNIQUE title20125 (title20125(171), fields_id) -- SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed
ALTER TABLE fieldtype_options ADD INDEX value20125 (value20125(171), fields_id) -- SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed
CREATE FULLTEXT INDEX title20125_ft ON fieldtype_options(title20125) -- SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed
CREATE FULLTEXT INDEX value20125_ft ON fieldtype_options(value20125) -- SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed

Here are the indexes for that fieldtype_options table.

PRIMARY | fields_id, option_id
-- | --
title(171), fields_id
title7529(171), fields_id
title10562(171), fields_id
title7759(171), fields_id
title8574(171), fields_id
title8575(171), fields_id
title6479(171), fields_id
title7737(171), fields_id
title12523(171), fields_id
title13307(171), fields_id
title13308(171), fields_id
title13309(171), fields_id
title14239(171), fields_id
title14240(171), fields_id
title14241(171), fields_id
title15574(171), fields_id
value, fields_id
sort, fields_id
value7529, fields_id
value10562, fields_id
value7759, fields_id
value8574, fields_id
value8575, fields_id
value6479, fields_id
value7737, fields_id
value12523, fields_id
value13307, fields_id
value13308, fields_id
value13309, fields_id
value14239, fields_id
value14240, fields_id
value14241, fields_id
value15574, fields_id
title
value
title7529
value7529
title10562
value10562
title7759
value7759
title8574
value8574
title8575
value8575
title6479
value6479
title7737
value7737
title12523
value12523
title13307
value13307
title13308
value13308
title13309
value13309
title14239
value14239
title14240
value14240
title14241
value14241

These SO links note the need to recompile MySQL with --with-max-indexes=256 but I am wondering if PW might be able to handle this some other way - are all those indexes on that table really needed?

https://stackoverflow.com/questions/16896866/error-too-many-keys-specified-max-64-keys-allowed https://stackoverflow.com/questions/5021586/mysql-error-1069-too-many-keys-specified-max-64-keys-allowed

[adrianbj]

A little more info. I am using Digital Ocean's managed DBs for this and they don't provide a way to change the max-indexes setting.

[adrianbj]

@ryancramerdesign - I have received a follow up from Digital Ocean:

We do not offer to recompile mysql with customized options. The engineering team would like to know if there's a business case to increase the number of indexes.

Do you think you can reduce the number of indexes on the select options field, or do you think we can make the case to DO that they should consider recompiling mysql with a 255 limit instead of the default 64 (https://dev.mysql.com/doc/refman/8.0/en/source-configuration-options.html#option_cmake_max_indexes).

Thanks for responding as quickly as you can on this as I need to respond to them.

[adrianbj]

Final decision from Digital Ocean is that they won't recompile with the setting change, so completely out of luck at that end.