Set MySQL 5.6 optimizer setting “block_nested_loop = off” for OXID eShop Enterprise Edition

MySQL BugsTo say it upfront: OXID eShop Enterprise Edition 5.2.x runs almost fine with MySQl 5.6, but there’s a little nasty MySQL Bug (#79203) that you better be aware of. OXID eShop Professional and Community Edition are not affected by this.

We first encountered inconsistencies with applying discounts when running OXID eShop Enterprise Edition 5.2.6 on a Debian GNU/Linux 7.8 (wheezy) with PHP 5.6.15 and MySQL 5.6.25.

Example:

  • set up OXID eShop Enterprise Edition 5.2.x
  • create a test user with ‘user’ rights
  • in shop admin panel, create two new user groups: user group A and user group B
  • in shop admin panel, create three different discounts, starting from quantity 1
  • 10% on top of whole basket for subshop 1 (yes, it’s a negative discount, but it’s only an example)
  • 5% off for all users in user group A (assign discount to user group A)
  • 20% off for all users in user group B (assign discount to user group B)

Say our test user belongs to user group A and shops in subshop 1. In that case he should get a 10% surcharge for the subshop and 5% off for his user group.

As a result, in front end were either all three discounts or none applied, three of them when
at the same time being logged in as admin in another tab and selecting the discount overview. This means that discounts are not applied correctly in our case. With the same setup, but MySQL 5.5 installed, all discounts are applied correctly.

It looks like the MySQL 5.6 optimizer has trouble with a specific type of query when views
are involved. Class oxDiscountList is executing a query of the structure

on a view created like

When we force core table usage, use a view not created with a join or simply append an ‘order by’ to the query, the result set is correct. After some digging into it, we found the culprit to be the setting for ‘block_nested_loop’,. if that is on, we can get wrong result sets. With MySQL 5.5, all is well.

There are some more places in OXID eShop that use a similar query structure as class oxDiscountList:

  • oxActionList
  • oxDeliveryList
  • oxDeliverySetList

But they work fine with MySQL 5.6 due to either selecting from a differently structured view table or because they use a query with appended ‘order by’.

To summarize: as long as we do not use any discounts for OXID eShop Enterprise Edition 5.2.x under MySQL 5.6.x, all is well. When discounts are used, the MySQL optimizer setting for block_nested_loop should be set to ‘off’ to be on the safe side.


1 reply

Trackbacks & Pingbacks

  1. […] 5.5 or 5.7. We do not recommend to use MySQL 5.6 because we found some issues with EE. See this blog post  for […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *