View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006247 | OXID eShop (all versions) | 1.03. Basket, checkout process | public | 2015-10-16 10:34 | 2021-06-29 15:09 |
| Reporter | flowcontrol | Assigned To | |||
| Priority | normal | Severity | major | Reproducibility | always |
| Status | resolved | Resolution | fixed | ||
| Product Version | 4.9.4 / 5.2.4 | ||||
| Fixed in Version | 6.3.1 | ||||
| Summary | 0006247: SQL Bug in oxDeliverySetList::_getFilterSelect() | ||||
| Description | in Line 158 it reads: if(EXISTS(select 1 from oxobject2delivery, $sCountryTable where $sCountryTable.oxid=oxobject2delivery.oxobjectid and oxobject2delivery.oxdeliveryid=$sTable.OXID and oxobject2delivery.oxtype='oxdelset' LIMIT 1) Shouldn't it be: if(EXISTS(select 1 from oxobject2delivery, $sCountryTable where $sCountryTable.oxid=oxobject2delivery.oxobjectid and oxobject2delivery.oxdeliveryid=$sTable.OXID and oxobject2delivery.oxtype='oxcountry' LIMIT 1), | ||||
| Steps To Reproduce | - create a EE Shop with two Subshops - delivery and deliverysets to germany and austria - everything ist configured correct - go shopping in the shop frontend - ship to austria - shop says there is no shipping configuration for this country | ||||
| Tags | EE, MySQL | ||||
| Theme | All | ||||
| Browser | All | ||||
| PHP Version | 5.4 | ||||
| Database Version | Not defined | ||||
| related to | 0006282 | closed | florian.auer | Filter SQL (oxDeliverySetList) doesn't work for MySQL 5.6 and OXID 5.2.4 |
|
|
And i missed line 152, this should read: 152 $sCountrySql = $sCountryId ? "EXISTS(select oxobject2delivery.oxid from oxobject2delivery where oxobject2delivery.oxdeliveryid=$sTable.OXID and oxobject2delivery.oxtype='oxcountry' and oxobject2delivery.OXOBJECTID=" . $oDb->quote($sCountryId) . ")" : '0'; instead of 152 $sCountrySql = $sCountryId ? "EXISTS(select oxobject2delivery.oxid from oxobject2delivery where oxobject2delivery.oxdeliveryid=$sTable.OXID and oxobject2delivery.oxtype='oxdelset' and oxobject2delivery.OXOBJECTID=" . $oDb->quote($sCountryId) . ")" : '0'; |
|
|
I tried to reproduce in a locally installed OXID eShop Enterprise Edition, Version 5.2.5 shop, but didn't get the message "there is no shipping configuration for this country". So maybe your delivery and deliverysets settings are not correct. So please check settings or let us know the settings. |
|
|
it seems .sql files are not allowed to be uploaded, i pasted the sql for the configuration here: http://pastebin.com/k2fPxA4y It is unlisted and will expire in one week from now. |
|
|
Let 6282 be a duplicate or not. First, the 'duplicate' ticket (this ticket) doesn't offer steps for a solution. Else the mentioned blog article http://planet.oxidforge.org/2015/11/set-mysql-5-6-optimizer-setting-block_nested_loop-off-for-oxid-eshop-enterprise-edition.html offers some hint, which also doesn't lead to a result for us. We tried to do: SELECT @@optimizer_switch\G; SET GLOBAL optimizer_switch='block_nested_loop=off'; But still, our query gets us an empty result. To be correct, if it's the first request of a database session, the correct result is returned. If executed later, it again does not work. Maybe you could share more of your MySQL settings? The other workaround could be to remove the "SELECT" before "if(EXISTS", unless you have arguments to not do so. Br, @matths |
|
|
You could also point out, that you filled a bug for mysql with exactly the same result, that we get. Would have saved us time. https://bugs.mysql.com/bug.php?id=79203 |
|
|
In our case we needed not only to set optimizer_switch='block_nested_loop=off', but also batched_key_access=on, mrr=on and mrr_cost_based=off. So the "fix" was: SET optimizer_switch="block_nested_loop=off,batched_key_access=on,mrr=on,mrr_cost_based=off" The real fix was to update to MySQL 5.7 ;-) |
|
|
The latest hint to do 'SET optimizer_switch="block_nested_loop=off,batched_key_access=on,mrr=on,mrr_cost_based=off"' doesn't work for us. Is there any other solution than updating to MySQL 5.7? |
|
|
@m0rb: to quote myself: > The other workaround could be to remove the "SELECT" before "if(EXISTS", unless you have arguments to not do so. <?php class mod_mymod__oxdeliverysetlist extends mod_mymod__oxdeliverysetlist_parent { protected function _getFilterSelect($oUser, $sCountryId) { $sQ = parent::_getFilterSelect($oUser, $sCountryId); // temporary fix for Bug introduced with MySQL 5.6 // remove 'select' between 'and (' and 'if(EXISTS' $count = null; $sQ = preg_replace('/(and\\s\\(\\W*)(select\\W*)(if\\(EXISTS)/', '$1$3', $sQ, -1, $count); return $sQ; } } |
|
|
May I bring this topic up again? We tested Oxid EE 6.1.5 on MySQL 5.5 - everything works fine. On MySQL 5.6 the error is as described here. No deliveryset is found although 1 matching deliveryset exists. The comments state that the problem could be solved by upgrading to MySQL 5.7. BUT we found (on 2 different machines) that the error still exists on MySQL 5.7 in its default configuration. When we edited my.cnf and added the optimizer_switch settings optimizer_switch="block_nested_loop=off,batched_key_access=on,mrr=on,mrr_cost_based=off" as described by user flowcontrol the query gave the correct result. Since not all Oxid admins have the chance to fiddle with my.cnf it would be great if this problem could be resolved soon. |
