View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0007157 | OXID eShop (all versions) | 4.01. Database handling | public | 2020-07-09 13:43 | 2022-02-11 13:25 |
| Reporter | pbenke | ||||
| Priority | normal | Severity | major | Reproducibility | always |
| Status | closed | Resolution | no change required | ||
| Product Version | 6.1.6 | ||||
| Target Version | Fixed in Version | ||||
| Summary | 0007157: Error in SQL statement on language en, if MySQL runs in STRICT_MODE | ||||
| Description | File: vendor/oxid-esales/oxideshop-ce/source/Application/Model/ActionList.php Row 131: if(EXISTS(select 1 from oxobject2action, $sGroupTable where $sGroupTable.oxid=oxobject2action.oxobjectid and oxobject2action.oxactionid=$sTable.OXID and oxobject2action.oxclass='oxgroups' LIMIT 1), => This is wrong, the condition "$sTable.OXID" cannot work, because $sTable is not defined in "from"-clause. => Correct: if(EXISTS(select 1 from oxobject2action, $sGroupTable, $sTable where $sGroupTable.oxid=oxobject2action.oxobjectid and oxobject2action.oxactionid=$sTable.OXID and oxobject2action.oxclass='oxgroups' LIMIT 1), (...from oxobject2action, $sGroupTable, ***$sTable*** where...) | ||||
| Steps To Reproduce | Multi-language shop e.g. de/en Set MySQL to Strictmode => /log/oxideshop.log will be filled with... Unknown column 'oxv_oxactions_en.OXID' in 'where clause'... | ||||
| Tags | Database, SQL | ||||
| Theme | Not defined | ||||
| Browser | Not defined | ||||
| PHP Version | Not defined | ||||
| Database Version | Not defined | ||||
|
|
Dear pbenke, OXID has the Systemrequirement of MYSQL 5.5 or MYSQL 5.7, and not 5.6. Nevertheless i tried to reproduce your report with MYSQL 5.7 but without encountering any problem. My steps where as followed: 1. Install a fresh version of OXID 2. activate Language EN 3. Set sql_mode = strict_all_tables 4. refresh views. 5. modifie an action can you reproduce it on an mysql 5.7 server? i guess there is a diffrence in behavior with embedded sql-snippets like your quoted snippet. best regard QA -SG- |
|
|
Hello, I do not have the possibility to check this at the moment, but nevertheless this cannot work, it is not possible! Please look exactly: Row 130 $sGroupSql = count($aIds) ? "EXISTS(select oxobject2action.oxid from oxobject2action where oxobject2action.oxactionid=$sTable.OXID and... => Means: if $aIds is set, the following string will be used => Please do your tests in that way, that $aIds will be filled => If it is filled => $sTable must exist in FROM-clause, but this is not the case: ...if(EXISTS(select 1 from oxobject2action, $sGroupTable where... Correct is: ...if(EXISTS(select 1 from oxobject2action, $sGroupTable, $sTable where... Thank you Cheers Peter |
|
|
Hello Peter, I retried to reproduce the issue, but I couldn't. To reproduce the issue I used a user which wasn't assigned to a group. ($aIds: Array ()) and the languages "de" and "en" were activated. To call the method _getUserGroupFilter I called the method loadBanners which returned the following result: string(532) "select * from oxv_oxactions_en where oxtype=3 and ( oxv_oxactions_en.oxactive = 1 or ( oxv_oxactions_en.oxactivefrom < '2022-02-10 14:53:00' and oxv_oxactions_en.oxactiveto > '2022-02-10 14:53:00' ) ) and oxshopid='1' and (if(EXISTS(select 1 from oxobject2action, oxv_oxgroups_en where oxv_oxgroups_en.oxid=oxobject2action.oxobjectid and oxobject2action.oxactionid=oxv_oxactions_en.OXID and oxobject2action.oxclass='oxgroups' LIMIT 1),0,1)) order by oxsort" Example result: b5639c6431b26687321f6ce654878fa5 1 3 Banner 1 1 0000-00-00 00:00:00 0000-00-00 00:00:00 surfer_wave(1)_promo.jpg 0 2016-07-19 14:38:25 The variable $sTable is defined right before the initialising of $aIds: https://github.com/OXID-eSales/oxideshop_ce/blob/master/source/Application/Model/ActionList.php#L119. So this shouldn't affect the query. I made the test with a default demoshop. Please check if the method wasn't changed and is equal to the original code. Also there's maybe a module which could change the behaviour by overwriting the object. -MK |
|
|
Hello, >> To reproduce the issue I used a user which wasn't assigned to a group. ($aIds: Array ()) No, this array has to be filled, you have to ensure, that $sGroupSql is filled with the following string: https://github.com/OXID-eSales/oxideshop_ce/blob/master/source/Application/Model/ActionList.php#L130 Nevertheless, you can extract the following subselect from the query, you have posted: select 1 from oxobject2action, oxv_oxgroups_en where oxv_oxgroups_en.oxid=oxobject2action.oxobjectid and oxobject2action.oxactionid=oxv_oxactions_en.OXID and oxobject2action.oxclass='oxgroups' LIMIT 1 => Then you will get an error, because the table "oxv_oxactions_en" ist not listed in the from-clause ("...from oxobject2action, oxv_oxgroups_en where...") Cheers Peter |
|
|
Hello Peter I tested it with the same scenario again, except this time I used the demo user [email protected] which is in a couple of groups. $aIds: array(1) { [0]=> string(26) "36944b76defac5622.13882269"I took the query and executed it manually on the database: MySQL [oxid]> select * from oxv_oxactions_en where oxtype=3 and ( oxv_oxactions_en.oxactive = 1 or ( oxv_oxactions_en.oxactivefrom < '2022-02-11 13:15:00' and oxv_oxactions_en.oxactiveto > '2022-02-11 13:15:00' ) ) and oxshopid='1' and ( if(EXISTS(select 1 from oxobject2action, oxv_oxgroups_en where oxv_oxgroups_en.oxid=oxobject2action.oxobjectid and oxobject2action.oxactionid=oxv_oxactions_en.OXID and oxobject2action.oxclass='oxgroups' LIMIT 1), EXISTS(select oxobject2action.oxid from oxobject2action where oxobject2action.oxactionid=oxv_oxactions_en.OXID and oxobject2action.oxclass='oxgroups' and oxobject2action.OXOBJECTID in ('36944b76defac5622.13882269') ), 1) ) order by oxsort;
+----------------------------------+----------+--------+----------+------------+----------+---------------------+---------------------+----------------------------------+-----------------------------+--------+---------------------+
| OXID | OXSHOPID | OXTYPE | OXTITLE | OXLONGDESC | OXACTIVE | OXACTIVEFROM | OXACTIVETO | OXPIC | OXLINK | OXSORT | OXTIMESTAMP |
+----------------------------------+----------+--------+----------+------------+----------+---------------------+---------------------+----------------------------------+-----------------------------+--------+---------------------+
| b5639c6431b26687321f6ce654878fa5 | 1 | 3 | Banner 1 | | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | surfer_wave(1)_promo.jpg | | 0 | 2016-07-19 14:38:25 |
| b56a097dedf5db44e20ed56ac6defaa8 | 1 | 3 | Banner 2 | | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | longboard_startpage(1)_promo.jpg | | 0 | 2016-07-19 14:38:25 |
| b56efaf6c93664b6dca5b1cee1f87057 | 1 | 3 | Banner 3 | | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | cabrinha_startpage(1)_promo.jpg | | 0 | 2016-07-19 14:38:25 |
| cb34f86f56162d0c95890b5985693710 | 1 | 3 | Banner 4 | | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | banner4en_promo.jpg | en/Wakeboarding/Wakeboards/ | 0 | 2016-07-19 14:38:25 |
+----------------------------------+----------+--------+----------+------------+----------+---------------------+---------------------+----------------------------------+-----------------------------+--------+---------------------+
4 rows in set (0.001 sec)
MySQL [oxid]>
Anything is fine and I still can't reproduce it. If you are saying the part EXISTS(select oxobject2action.oxid from oxobject2action where oxobject2action.oxactionid=$sTable.OXID and oxobject2action.oxclass='oxgroups' and oxobject2action.OXOBJECTID in (" . implode(', ', \OxidEsales\Eshop\Core\DatabaseProvider::getDb()->quoteArray($aIds)) . ") ) can't be executed by its own, because an error would be thrown, then you are right:MySQL [oxid]> EXISTS(select oxobject2action.oxid from oxobject2action where oxobject2action.oxactionid=$sTable.OXID and oxobject2action.oxclass='oxgroups' and oxobject2action.OXOBJECTID in (" . implode(', ', \OxidEsales\Eshop\Core\DatabaseProvider::getDb()->quoteArray($aIds)) . ") ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS(select oxobject2action.oxid from oxobject2action where oxobject2action.ox' at line 1 MySQL [oxid]> But this very part is meant to be included in a SQL query, in which it works fine. So I have to close the bug as the code works as intended. But if you want to enhance the method I would kindly ask you to create a Pull Request in our repository to get it done: https://github.com/OXID-eSales/oxideshop_ce/blob/master/CONTRIBUTING.md Kind regards and have a nice weekend, Michael Keiluweit |
