Shaun Shaun - 1 year ago 91
SQL Question

Magento Error when searching on frontend caused by report_event

I have a magento install setup with 2 website connecting to it (different URLS). With one of them, I have no problems, the site is fast loading, no errors etc. The other, which is the main one, with a lot more products, I have constant errors, and is painfully slow.

The main issue I am having is that I have to constantly empy the report_event table in the database, otherwise I get errors on the frontend when people are search, such as this:

There has been an error processing your request
SELECT COUNT(report_table_views.event_id) AS `views`, `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, `e`.`name`, `e`.`sku`, `e`.`price`, `e`.`small_image`, `e`.`status`, `e`.`tax_class_id`, `e`.`url_key`, `e`.`thumbnail`, `e`.`short_description`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`required_options`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`links_purchased_separately`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`links_exist`, `e`.`man_part_code`, `e`.`our_part_no`, `e`.`donor_make_attr`, `e`.`donor_model_attr`, `e`.`donor_modeldetails_attr`, `e`.`donor_bodycolour_attr`, `e`.`donor_intcolour_attr`, `e`.`donor_engine_attr`, `e`.`condition_description`, `e`.`part_condition_description`, `e`.`part_condition_description_value`, `e`.`year`, `e`.`make`, `e`.`v_model` FROM `report_event` AS `report_table_views`
INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = report_table_views.object_id AND e.entity_type_id = 10
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '3' WHERE (report_table_views.event_type_id = 1) AND (e.entity_id IN(267, 366, 2389, 3892, 3893, 3895, 4039, 5129, 5217, 5218, 6132, 6320, 6452, 6583, 8308, 8566, 8857, 8862, 9500, 9561, 9562, 9566, 9567, 9568, 9569, 9570, 9572, 9573, 9574, 9614, 9674, 10000)) GROUP BY `e`.`entity_id` HAVING (COUNT(report_table_views.event_id) > 0) ORDER BY `views` DESC LIMIT 5

It is really frustrating me, as I can't seem to be able to figure out what it is. I have tried disabling reporting of events, but no joy. I think this also has something to do with the speed of the site as well.

Anyone come across this, or know a way to disable this table from being used?


Answer Source

In the end, I disabled the reporting of product views by entering the following into the local.xml file. Don't know exactly what caused the issue, but it no longer occurs since I have added this.


Hope this helps someone else out.