View Issue Details

IDProjectCategoryView StatusLast Update
0005987OXID ERP InterfaceOXID ERP Interface - subpublic2015-01-05 12:52
Reporterwinkelmann Assigned To 
PriorityhighSeveritymajorReproducibilityalways
Status resolvedResolutionno change required 
Product Version2.13.0 
Summary0005987: Too many tables in a join in get methods
DescriptionWe are running a shop with 31 languages and the lowest possible option to set for languages per tables (before OXID creates a _set# table).

When we are trying to use the ERP Soap Connector's "OXERPGetArticle"-method, we are getting an error for using too many tables in a join (see error below). For me, it seems like the connector ignores if a language and shop is set.


stdClass Object
(
    [blResult] =>
    [sMessage] => mysql:EXECUTE error: [1116: Too many tables; MySQL can only use 61 tables in a join] in EXECUTE with parameters select `a`.OXSHOPID,`a`.OXSHOPINCL,`a`.OXSHOPEXCL,`a`.OXPARENTID,OXACTIVE as OXACTIV,OXACTIVEFROM as OXACTIVFROM,OXACTIVETO as OXACTIVTO,`a`.OXARTNUM,`a`.OXEAN,`a`.OXDISTEAN,`a`.OXTITLE,`a`.OXSHORTDESC,`ax`.OXLONGDESC, IFNULL(`f2s`.`OXPRICE`, `a`.`OXPRICE`) as `OXPRICE`,`a`.OXBLFIXEDPRICE, IFNULL(`f2s`.`OXPRICEA`, `a`.`OXPRICEA`) as `OXPRICEA`, IFNULL(`f2s`.`OXPRICEB`, `a`.`OXPRICEB`) as `OXPRICEB`, IFNULL(`f2s`.`OXPRICEC`, `a`.`OXPRICEC`) as `OXPRICEC`,`a`.OXBPRICE,`a`.OXTPRICE,`a`.OXUNITNAME,`a`.OXUNITQUANTITY,`a`.OXEXTURL,`a`.OXURLDESC,`a`.OXURLIMG,`a`.OXVAT,`a`.OXTHUMB,`a`.OXICON,`a`.OXPIC1,`a`.OXPIC2,`a`.OXPIC3,`a`.OXPIC4,`a`.OXPIC5,`a`.OXPIC6,`a`.OXPIC7,`a`.OXPIC8,`a`.OXPIC9,`a`.OXPIC10,`a`.OXPIC11,`a`.OXPIC12,'' as OXZOOM1,'' as OXZOOM2,'' as OXZOOM3,'' as OXZOOM4,`a`.OXWEIGHT,`a`.OXSTOCK,`a`.OXSTOCKFLAG,`a`.OXSTOCKTEXT,`a`.OXNOSTOCKTEXT,`a`.OXDELIVERY,`a`.OXINSERT,`a`.OXTIMESTAMP,`a`.OXLENGTH,`a`.OXWIDTH,`a`.OXHEIGHT,'' as OXAKTION,`a`.OXFILE,`a`.OXSEARCHKEYS,`a`.OXTEMPLATE,`a`.OXQUESTIONEMAIL,`a`.OXISSEARCH,`a`.OXVARNAME,`a`.OXVARSTOCK,`a`.OXVARCOUNT,`a`.OXVARSELECT,`a`.OXVARNAME_1,`a`.OXVARSELECT_1,`a`.OXVARNAME_2,`a`.OXVARSELECT_2,`a`.OXVARNAME_3,`a`.OXVARSELECT_3,`a`.OXTITLE_1,`a`.OXSHORTDESC_1,`ax`.OXLONGDESC_1,`a`.OXURLDESC_1,`a`.OXSEARCHKEYS_1,`a`.OXTITLE_2,`a`.OXSHORTDESC_2,`ax`.OXLONGDESC_2,`a`.OXURLDESC_2,`a`.OXSEARCHKEYS_2,`a`.OXTITLE_3,`a`.OXSHORTDESC_3,`ax`.OXLONGDESC_3,`a`.OXURLDESC_3,`a`.OXSEARCHKEYS_3,`a`.OXFOLDER,`a`.OXSUBCLASS,`a`.OXSTOCKTEXT_1,`a`.OXSTOCKTEXT_2,`a`.OXSTOCKTEXT_3,`a`.OXNOSTOCKTEXT_1,`a`.OXNOSTOCKTEXT_2,`a`.OXNOSTOCKTEXT_3,`a`.OXSORT,`a`.OXSOLDAMOUNT,`a`.OXNONMATERIAL,`a`.OXFREESHIPPING,OXREMINDACTIVE as OXREMINDACTIV,`a`.OXREMINDAMOUNT,`a`.OXAMITEMID,`a`.OXAMTASKID,`a`.OXVENDORID,`a`.OXSKIPDISCOUNTS,`a`.OXORDERINFO,'' as OXSEOID,'' as OXSEOID_1,'' as OXSEOID_2,'' as OXSEOID_3,`a`.OXPIXIEXPORT,`a`.OXPIXIEXPORTED,`a`.OXVPE,`a`.OXID from oxv_oxarticles_1 AS `a` LEFT JOIN oxv_oxfield2shop AS `f2s` ON `f2s`.`oxartid` = `a`.`oxid` AND `f2s`.`oxshopid` = '1' LEFT JOIN oxv_oxartextends AS `ax` ON `ax`.`oxid` = `a`.`oxid` where `a`.`oxid` = 'f38a89ddfdd08e7bfd1d616cc44a4' order by `a`.`oxid`, for user testshop

)
Steps To ReproduceLogin per SOAP with OXERPLogin (set shopid, admin credentials, language id) and after that, use OXERPGetArticle with the provided sid & article oxid.
TagsNo tags attached.

Relationships

related to 0005875 resolvedvilma_liorensaityte Low perfomance when using view tables (OXERPGetArticles) 

Activities

jurate.baseviciene

2014-12-16 09:59

reporter   ~0010502

Reminder sent to: winkelmann

Hi,
We are thinking that this bug was fixed with bug 0005875. The fix is added from ERP version 2.14.0. You can add this fix and this problem would be solved.

Could you please tell to us how how many language do you are using through one table?


Best regards,
Jurate

winkelmann

2014-12-16 10:06

reporter   ~0010505

Hello,

we are using 1 language per table.

jurate.baseviciene

2014-12-16 12:11

reporter   ~0010516

Reminder sent to: winkelmann

Hi,

We are recommend to use 8 languages per table not one.

Best regards,
Jurate