View Issue Details

IDProjectCategoryView StatusLast Update
0006247OXID eShop (all versions)1.03. Basket, checkout processpublic2017-06-29 11:18
Reporterflowcontrol 
PrioritynormalSeveritymajorReproducibilityalways
Status acknowledgedResolutionopen 
Product Version4.9.4 / 5.2.4 
Target VersionFixed in Version 
Summary0006247: SQL Bug in oxDeliverySetList::_getFilterSelect()
Descriptionin 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
TagsEE
ThemeAll
BrowserAll
PHP Version5.4
MySQL Version5.6

Relationships

related to 0006282 closedflorian.auer Filter SQL (oxDeliverySetList) doesn't work for MySQL 5.6 and OXID 5.2.4 

Activities

flowcontrol

2015-10-16 10:37

reporter   ~0011261

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';

QA

2015-10-19 11:03

administrator   ~0011265

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.

flowcontrol

2015-10-20 10:39

reporter   ~0011271

Last edited: 2015-10-20 10:40

View 2 revisions

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.

matths

2015-12-07 16:14

reporter   ~0011365

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

matths

2015-12-07 16:53

reporter   ~0011369

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

flowcontrol

2016-04-08 13:03

reporter   ~0011527

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 ;-)

m0rb

2017-04-24 16:08

reporter   ~0012045

Last edited: 2017-04-24 16:08

View 2 revisions

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?

matths

2017-04-24 17:21

reporter   ~0012046

@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;
    }
}