Mysql leads table, too many keys specified max 64 keys allowed

[FerRubioMorales]

What type of report is this:

Q	A
Bug report?	Yes
Feature request?
Enhancement?

Description:

This bug does not let you perform write actions over leads table. When custom field property it's created the system also creates and index in this table. For solve you have to recompile MySQL:

./configure --prefix=/usr/local/mysql --with-charset=cp1251 --enable-thread-safe-client --with-max-indexes=256

If a bug:

Q	A
Mautic version	2.9.0
PHP version	7.1

Steps to reproduce:

1. Create new custom field properties (each custom field property ads an index to the lead table, when max keys are 64 and this is not being controlled)
2. Try tu run doctrine:schema:update
3. You will have an error that says: "too many keys specified max 64 keys allowed". This produce a partial migration and lead tables does not contains all the columns.

Log errors:

[Doctrine\DBAL\Exception\DriverException]
An exception occurred while executing 'CREATE INDEX instagram_search ON leads (instagram)':
SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed

[escopecz]

Thanks for the report! I'll close it in favour of #4372

[pjeby]

How is this related to #4372? That issue doesn't say anything about indexes, only column size. Is it to add an option for whether the field should be indexed or not?

[escopecz]

Yes, that is the idea. Give user option to configure the column size and whether the column should have index. Please comment there with the index limit. I still think these 2 options could be done in one PR.

[moskoweb]

How to solve this problem?

It's stopping my campaigns from run.

[FerRubioMorales]

I did not found solution for this. May be you could solve it recompiling mysql using some options bypassing the 64keys limit, but i cant test it because i cant recompile. El El sáb, 7 abr 2018 a las 1:49, Alan Mosko <notifications@github.com> escribió:

…

How to solve this problem? It's stopping my campaigns from rolling. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#4465 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAqADoMqxLqYz8p2BdZyX7K4_7_CU9Y0ks5tl_8TgaJpZM4OcWxG> .

[escopecz]

A quick fix could be to remove indexes from some custom fields that you think Mautic does not use to search or join by. For example those that aren't used in segment filters. You'd have to do that in your database directly. Use PhpMyAdmin or Adminer for easier database manipulation. Let us know if this worked for you please.

[FerRubioMorales]

Thanks John! I will try it. I think it’s a temporal fix because on next mautic Update this will throw errors due to migrations (i think migrations will add the indexes again.) El El lun, 9 abr 2018 a las 8:51, John Linhart <notifications@github.com> escribió:

…

A quick fix could be to remove indexes from some custom fields that you think Mautic does not use to search or join by. For example those that aren't used in segment filters. You'd have to do that in your database directly. Use PhpMyAdmin or Adminer for easier database manipulation. Let us know if this worked for you please. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#4465 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAqADgT9ljZHtePcNCE5RbEGa_0w0BZKks5tmwUKgaJpZM4OcWxG> .

[escopecz]

Nope, migrations do not add the indexes back. But every time you create a custom field and maybe during update the index will be created.

[moskoweb]

So you have no solution for that yet? Why the problem is giving in a custom field I created in 2.12.11.

[FerRubioMorales]

I confirm that the answer by @john Linhart it's right, but it's more a patch than a solution, but at least you can update your instance without problems. El mar., 10 abr. 2018 a las 2:32, Alan Mosko (<notifications@github.com>) escribió:

…

So you have no solution for that yet? Why the problem is giving in a custom field I created in 2.12.11. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#4465 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAqADigmhom4AgH_-RRAiiVqLqSG7HFPks5tm_2UgaJpZM4OcWxG> .

[moskoweb]

The ideal would be to have a solution, the alternative may work temporarily, but with the growth of the tool, how does it work?

[YosuCadilla]

Might this be helpful?
https://www.safaribooksonline.com/blog/2012/11/06/avoiding-too-many-keys-in-mysql/

[starshunter88]

+1 I am also encountering this issue