To 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.
- 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
select viewtable.oxid from viewtable where (select if(EXISTS( 'search relation table plus related table for any matching rows containing viewtable.oxid. Entry in related table must exist.'), EXISTS( 'do detail search on relation table for rows matching conditions containing viewtable.oxid'), 1) && if(EXISTS( 'search other relation table plus other related table for any matching rows containing viewtable.oxid. Entry in other related table must exist.'), EXISTS( 'do detail search on other relation table for rows matching conditions containing viewtable.oxid'), 1) ... and so on )
on a view created like
CREATE ... VIEW `viewtable` AS select `table`.`OXID` AS `OXID`, ..., from (`table` join `relations` `t2s` on((`t2s`.`OXMAPOBJECTID` = `table`.`OXMAPID`))) where (`t2s`.`OXSHOPID` = 1)
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:
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.