Error500 MySQL can only use 61 tables in join

[Jakub Grufik]

Hello guys, we were adding some fields in opportunities (we have a lot of fields there) and system started generating error500:

[2023-05-16 06:55:53] ERROR: (HY000) SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join; GET /Opportunity/6462c2f2a771b6853; line: 70, file: /data/0/f/0f6e82d6-07b5-42f8-803a-fad2758ce077/esmerozz.eu/web/application/Espo/ORM/SqlExecutor.php [] []
[2023-05-16 06:55:53] ERROR: (HY000) SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join; GET /Opportunity/6462c2f2a771b6853/stream; line: 70, file: /data/0/f/0f6e82d6-07b5-42f8-803a-fad2758ce077/esmerozz.eu/web/application/Espo/ORM/SqlExecutor.php [] []
[2023-05-16 06:55:53] ERROR: (HY000) SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join; GET /Activities/Opportunity/6462c2f2a771b6853/history; line: 70, file: /data/0/f/0f6e82d6-07b5-42f8-803a-fad2758ce077/esmerozz.eu/web/application/Espo/ORM/SqlExecutor.php [] []
[2023-05-16 06:55:53] ERROR: (HY000) SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join; GET /Opportunity/6462c2f2a771b6853/tasks; line: 70, file: /data/0/f/0f6e82d6-07b5-42f8-803a-fad2758ce077/esmerozz.eu/web/application/Espo/ORM/SqlExecutor.php [] []
[2023-05-16 06:55:53] ERROR: (HY000) SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join; GET /Activities/Opportunity/6462c2f2a771b6853/activities; line: 70, file: /data/0/f/0f6e82d6-07b5-42f8-803a-fad2758ce077/esmerozz.eu/web/application/Espo/ORM/SqlExecutor.php [] []
[2023-05-16 06:55:53] ERROR: (HY000) SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join; GET /Opportunity/6462c2f2a771b6853/documents; line: 70, file: /data/0/f/0f6e82d6-07b5-42f8-803a-fad2758ce077/esmerozz.eu/web/application/Espo/ORM/SqlExecutor.php [] []
[2023-05-16 06:55:54] ERROR: (HY000) SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join; GET /Opportunity/6462c2f2a771b6853/childZakazka; line: 70, file: /data/0/f/0f6e82d6-07b5-42f8-803a-fad2758ce077/esmerozz.eu/web/application/Espo/ORM/SqlExecutor.php [] []​

Could you please help me waht can I do about this in order to fix it? Thanks a lot.

[Jakub Grufik]

The system is not working properly at the moment. A lot of errors being generated and we cannot even send an email.

[lazovic]

Hi Jakub Grufik,

This documentation article may be helpful: https://docs.espocrm.com/administrat...bles-in-a-join.

[Jakub Grufik]

thanks a lot man!! will check it

[esforim]

Too many tables, how much field do you have? But all I see is Relationship?

I see about 5 in your logs, that not much. One of my entity (Case) got like 10 relationship and it working fine. So something else causing it.

It mentioning about 61 tables, so maybe 61 fields? That doesn't seem right either, a quick check of my Case entity I seem to have about 88 field, include duplicate field like "Currency"converted

[esforim]

Look like lazovic solve it. And it a very specific error that the EspoCRM saw coming!

[Jakub Grufik]

lazovic thanks a lto man, I tried number 4 tip from the docs you sent me and it seems like its fixed the issue..

1. Set currencyNoJoinMode ('currencyNoJoinMode' => true, in data/config.php). With this mode enabled you will need to clear cache (in Espo) every time you change currency rates. (as of v7.3)

espocrm thanks a lot for the tips man, we have 200+ fields in opportunities at the moment (I am currently working on clean up of the system, because this is getting wild). We have 35 relationships in opportunity and a lot of others in other entities (should take a look on the clean up of this as well)

[esforim]

Hope my biggest entity (case) don't get this issue

[Jakub Grufik]

It seems like our system is getting overloaded with lot of data and lot of fields, entities, relationships etc.. I have to plan and do some proper cleanup otherwise ty system will commit suicide soon..