Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

“Copying to tmp table” results in Server Crash
 
VIANEL
Jr. Member
 
Avatar
Total Posts:  4
Joined:  2008-06-18
Essen/ Beyşehir
 

I have nearly 4000 products on Magento 1.7.0.2. The folllowing query starts every 5 minutes and does not seem end. Normally there are more then 15 processes at the same time. After hours it ends with a server hang up, because the RAM snd swap are full with tables that cannot be handled. I have run the Dataase repair tool wich has found many missing Indexes and Keys. Any help would be great smile

SELECT`e`.* , IF( at_status.value_id >0at_status.valueat_status_default.value )AS`status` , IF( at_visibility.value_id >0at_visibility.valueat_visibility_default.value )AS`visibility` ,`at_price`.`value`AS`price` ,`stock`.`qty` ,`stock`.`is_in_stock` ,`stock`.`manage_stock` ,`stock`.`use_config_manage_stock` ,`url`.`request_path` ,`categories`.* , GROUP_CONCATcategories_index.category_id )AS`categories_ids` ,`price_index`.`min_price` ,`price_index`.`max_price` ,`price_index`.`tier_price` ,`price_index`.`final_price
FROM`catalog_product_entity`AS`e
INNERJOIN`catalog_product_website`AS`product_website`ON product_website.product_id e.entity_id
AND product_website.website_id =\'2\'
INNERJOIN`catalog_product_entity_int`AS`at_status_default`ON (`at_status_default`.`entity_id`=`e`.`entity_id`) 
AND (
 `
at_status_default`.`attribute_id`=\'87\'
)
AND`
at_status_default`.`store_id`=0
LEFTJOIN
`catalog_product_entity_int`AS`at_status`ON (`at_status`.`entity_id`=`e`.`entity_id`) 
AND (
 `
at_status`.`attribute_id`=\'87\'
)
AND (
 `
at_status`.`store_id`=2
)
INNERJOIN`catalog_product_entity_int`AS`at_visibility_default`ON (`at_visibility_default`.`entity_id`=`e`.`entity_id`) 
AND (
 `
at_visibility_default`.`attribute_id`=\'93\'
)
AND`
at_visibility_default`.`store_id`=0
LEFTJOIN
`catalog_product_entity_int`AS`at_visibility`ON (`at_visibility`.`entity_id`=`e`.`entity_id`) 
AND (
 `
at_visibility`.`attribute_id`=\'93\'
)
AND (
 `
at_visibility`.`store_id`=2
)
INNERJOIN`catalog_product_entity_decimal`AS`at_price`ON (`at_price`.`entity_id`=`e`.`entity_id`) 
AND (
 `
at_price`.`attribute_id`=\'67\'
)
AND (
 `
at_price`.`store_id`=0
)
LEFTJOIN`cataloginventory_stock_item`AS`stock`ON stock.product_id e.entity_id
LEFTJOIN
`core_url_rewrite`AS`url`ON url.product_id e.entity_id
AND url.category_id ISNULL 
AND is_system =1
AND ISNULLoptions 
AND 
url.store_id =2
LEFTJOIN
`catalog_category_product`AS`categories`ON categories.product_id e.entity_id
INNERJOIN
`catalog_category_product_index`AS`categories_index`ON categories_index.category_id categories.category_id
AND categories_index.product_id categories.product_id
AND categories_index.store_id =2
AND categories_index.category_id
IN 
3298299300301302306462463464465466467468469470303434435436441442443444445446304305480481482307309310311474313447448449418312419479314405406407408409315428438316415416417317402403404384385387388389390391430437439440308378379380381382458459383392393394395396397398399400401450451497452454455456457460461318319420421422423320424425426427321322323364365366367473487488489490491492483484485486324325369370326453327328371372329376377330373374331332333334471335375337338336431432433339340341496342343344345346347348493495498499500502503349350351494501352353354355356357358359360361362363410411412413414368475477478
LEFTJOIN`catalog_product_index_price`AS`price_index`ON price_index.entity_id e.entity_id
AND customer_group_id =0
AND price_index.website_id =2
WHERE 
(
IF( 
at_status.value_id >0at_status.valueat_status_default.value )=\'1\'
)
AND (
 `
e`.`type_id
IN (
 
\'simple\',\'configurable\',\'bundle\',\'grouped\',\'virtual\',\'downloadable\'
))
AND (
IF( 
at_visibility.value_id >0at_visibility.valueat_visibility_default.value 
IN (
 
\'2\',\'3\',\'4\'
))
AND (
at_price.value >\'0\'
)
AND (
(
is_in_stock !=\'0\'
OR (
manage_stock !=\'1\'
AND use_config_manage_stock !=\'1\'
)
OR (
use_config_manage_stock !=\'0\'
AND 1=0
AND is_in_stock =0
)))
GROUPBY`e`.`entity_id` , `e`.`entity_id
LIMIT 1500
 
Magento Community Magento Community
Magento Community
Magento Community
 
VIANEL
Jr. Member
 
Avatar
Total Posts:  4
Joined:  2008-06-18
Essen/ Beyşehir
 

No one can help or has a solution?

 
Magento Community Magento Community
Magento Community
Magento Community
 
x_cem
Jr. Member
 
Total Posts:  8
Joined:  2009-11-15
 

Hi,

Same problem. : (

Regards,
Jem

 
Magento Community Magento Community
Magento Community
Magento Community
 
eineinzigerbrei
Jr. Member
 
Total Posts:  1
Joined:  2008-12-09
 

The same here for Magento Enterprise 1.12.0.2.
The above query is running constantly and slowly consumes more and more memory until the server doesn’t respond anymore.
Any new ideas on that?

Regards
Matt

 
Magento Community Magento Community
Magento Community
Magento Community
 
GerryBrix
Jr. Member
 
Total Posts:  2
Joined:  2010-06-15
 

Hi, Did any one ever work this one out? This is bringing my server down 3-4 time s a week.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top