General error: 1116 Too many tables

Shopware 6 (English) Programming (EN)
,
1

Hi,

I am working with a shop with large number of items. It is on the migration stage.

For some days I am facing an issue. The error is as follows

An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join

I am not able to update product from admin side.
I have deactivated all 3rd party plugins, no changes. Still error is there.

Anybody face something like this before?
So, is there any workaround to handle this situation?

I am using shopware 6.5.8.7

Thank you

2

Good morning,

this error is present since the early 20th but that doesn’t help you.
MariaDB und mySQL share the same join table limit.

I personally never hit that error message. Take a brief look at the SQL-Statement (or post it here, somewhat obfuscated to protect your privacy)
Try disabling some of your addons in a development environment and try again.

3

Hi @chapter2 thanks for the feedback.

I deactivated all the plugins and checked again and still the issue repeated.
But the funny part is, later I noticed that the value get saved even if the error message comes up (So for now I can continue with the heavy workload).
There is no custom code running on the application so far I know.
No suspicious error message shown on the Shopware error log.
I will probably come back with more information after a closer look.

Thank you.

4

Hi together,
maybe it’s related to this issue:

Unfortunately there isn’t any solution so far.

5

It is possible that Shopware could fix this?
Seems to be a limit by MySQL/MariaDB: „…can only use 61 tables in a join“.

Could you fix it by yourself: reorganizing the products by splitting the products in 2 or more shops?

Write something about your products:

  • how many products and variants?
  • how many crossselling products are linked to products?
  • some extra fields on products?
6

We have around 300, 000 products and too many cross selling products. We also have many custom fields also.

However for me the issue comes when I add Rule something like „Category - Alle Außer and give a 100 categories to validate“. If I reduce the category number to a lower number, no more issues.

But the same product stream associated is very much important for the feed generation. (And I am not ready to speak about feed generation using messenger. It is a sad story)

Thank you

7

Every generic system reaches limits at specific point.

You might need to write your own api endpoint or plugin, that uses pure sql instead of DAL. Then you will be fine.

8

Yup. That’s what I did for now. But needed upgrade on the plugin also. Will do on the go.

Thank you

9

Shopware supports B2B shops so it should be possible to have a high amount of products, customer, custom fields etc.

Maybe get in contact directly with Shopware by booking an appointment on feedback.shopware.com.

You can also speak to Shopware with the topic „feed generation using messenger“.