Maximum number of joins – product attributes

Hi

i am sorry but i am not very technical, if you need further information please let me know and i will see if i can gather it.

I have an Oro v3 install and have added nearly 60 different attributes, this caused the product listing to not load due to the maximum number of joins in MYSQL being exceeded. I resolved this by limiting the number of attributes shown on the product listing page within admin.

However the product export is not working my developer has also written a bespoke Export which is now failing, they have told me they are using the standard Oro product object to gather product data and that the number of joins is being exceed when this is run.

Is anyone aware of this problem relating to product attributes? Are there any suggestions other than limiting the number of attributes as these are needed during the product export.

Are they any solutions to this in later releases?

thanks.

Topic

[Andrey YatsenkoModerator]

Hi Matthew,
Please share the query that fails, or analyze joins by yourself to understand where they come from.
Usually, depending on the type of attributes developers have the same issue starting from 150-400 attributes. The issue could go with the less number of attributes when most of them are enums (select or multi-select) fields.
Also, we recommend using serialized fields to store attributes, to do that, when creating new attributes, you should mark as many of them as you can as not filterable and not searchable and use scalar types when you can.

[matthew.brookesParticipant]

Hi

thanks for your help so far.

We have identified the issues as being generated by a large number of select attributes being created. When we run the update schema and the site then fails.

We have tried to mark the select attributes as for deletion but the schema update seems to want to run the creation first? We have been trying to identify in the database where all the schema updates are stored or being generated from as we were considering removing the schema queue to try and clear things.

Also has the issue with regard to the system allowing you to create more select attributes than the database can handle been resolved in a new version of Oro our site is based on version 3.0 and i am aware of a newer version.

thanks,
Matthew

[Andrey YatsenkoModerator]

We have tried to mark the select attributes as for deletion but the schema update seems to want to run the creation first

It is recommended to update the schema with migrations instead of using “doctrine:schema:update”, as with migrations you can explicitly control what is happening.

Also, it’s preferred to use “oro:platform:update” command to run migrations to have all the required caches updated after migrations automatically. See https://doc.oroinc.com/backend/setup/upgrade-to-new-version/ guide for the detailed instruction on how to upgrade schema and https://doc.oroinc.com/backend/bundles/platform/MigrationBundle/#bundle-docs-platform-migration-bundle to know how to create schema migrations.

[matthew.brookesParticipant]

Hi Andrey,

Thanks for your response, just so you are aware we created the selects via the ORO admin so will the above work?

rather than doing in code.

[Andrey YatsenkoModerator]

Yes, it should work, but if it’s a new project you should also consider a full reinstall instead of writing a migration to remove UI changes.

[matthew.brookesParticipant]

Hi Andrey,

i would just like to say thank you for your help in pointing us in the right direction.

After a bit of further debugging we look to have cleared this issue with Select Attributes in our system.

[Author]

Replies