1

When i try to run sql query, its not working. i get error like,

enter image description here

My Query is below

SELECT cpev.value as Name,cpe.sku,gp.value category_url, ea.value image_path FROM catalog_product_entity cpe inner join (SELECT entity_id,value FROM catalog_product_entity_varchar WHERE attribute_id="71") cpev on cpev.entity_id = cpe.entity_id inner join (SELECT entity_id,value FROM catalog_product_entity_media_gallery) ea on ea.entity_id=cpe.entity_id inner join( SELECT category_id, product_id FROM catalog_category_product) eavv on cpe.entity_id =eavv.product_id inner join( SELECT entity_id,value FROM catalog_category_entity_varchar WHERE attribute_id='57') gp on eavv.category_id=gp.entity_id

  • Add you query which you are run in database. – Suresh Chikani Oct 13 '16 at 8:58
  • SELECT cpev.value as Name,cpe.sku,gp.value category_url, ea.value image_path FROM catalog_product_entity cpe inner join (SELECT entity_id,value FROM catalog_product_entity_varchar WHERE attribute_id="71") cpev on cpev.entity_id = cpe.entity_id inner join (SELECT entity_id,value FROM catalog_product_entity_media_gallery) ea on ea.entity_id=cpe.entity_id inner join( SELECT category_id, product_id FROM catalog_category_product) eavv on cpe.entity_id =eavv.product_id inner join( SELECT entity_id,value FROM catalog_category_entity_varchar WHERE attribute_id='57') gp on eavv.category_id=gp.entity_id – Gem Oct 13 '16 at 9:00
  • In localhost its working, but not working in my hosting site – Gem Oct 13 '16 at 9:01
  • Please specify magento version. – Suresh Chikani Oct 13 '16 at 9:03
  • magento 1.9.2.3 – Gem Oct 13 '16 at 9:05
1

Looks like your query is too havy for remote mysql server. Here is optimized query.

SELECT cpev.value as Name,cpe.sku,gp.value category_url, ea.value image_path 
FROM catalog_product_entity cpe 
inner join catalog_product_entity_varchar cpev on cpev.entity_id = cpe.entity_id and  cpev.attribute_id = (SELECT attribute_id FROM eav_attribute where attribute_code = 'name' and entity_type_id = 4)
inner join catalog_product_entity_media_gallery  ea on ea.entity_id=cpe.entity_id 
inner join catalog_category_product eavv on cpe.entity_id =eavv.product_id 
inner join catalog_category_entity_varchar gp on eavv.category_id=gp.entity_id and gp.attribute_id = (SELECT attribute_id FROM eav_attribute where attribute_code = 'url_key' and entity_type_id = 3)

if it will not work too, try to add "LIMIT 10" at the end

1

Firstly enable mysql's error log so you can find exact error

1.uncheck limit rows in in Edit → Preferences →SQL Queries

or

2.change specific timeouts. IN MY MySQL WorkBench

For me it was under Edit → Preferences → SQL Editor → DBMS connection read time out (in seconds): 600

Changed the value to 6000. enter image description here

Also unchecked limit rows as putting a limit in every time I want to search the whole data set gets tiresome.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.