View Issue Details

IDProjectCategoryView StatusLast Update
0006247OXID eShop (all versions)1.03. Basket, checkout processpublic2021-06-29 15:09
Reporterflowcontrol Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
Product Version4.9.4 / 5.2.4 
Fixed in Version6.3.1 
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, MySQL
ThemeAll
BrowserAll
PHP Version5.4
Database VersionNot defined

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

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

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

finnegan

2020-02-06 22:52

reporter   ~0013116

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.