Database queries took down host. Please help

Sorry if this is in the wrong spot, but here is the email I got from the host and I'm not sure where to begin. Any help would be appreciated.

Something is wrong with your site. The following query is getting issued by your site every few seconds but requires more than 40 seconds to complete due to the tables involved and what appear to be a lack of good database indexes on the tables involved, causing it to run extremely slowly. There were hundreds of the queries built up and it ran the server out of connections.

select distinct op.products_id,op.products_name , o.delivery_city , o.delivery_country
from orders o, orders_products op ,paypal p
where o.orders_id = op.orders_id
order by o.orders_id desc
limit 20

linnx:

select distinct op.products_id,op.products_name , o.delivery_city , o.delivery_country
from orders o, orders_products op ,paypal p
where o.orders_id = op.orders_id
order by o.orders_id desc
limit 20

I can't find that query anywhere in Zen Cart.
It looks like maybe it's from a poorly-written addon you've installed?

I don't believe that this query is part of Zen Cart's core code either. I recommend using the Admin > Tools > Developer Toolkit to locate the following string "select distinct op.products_id," to see if this is part of an add-on mod or a hack.

Ok thanks. We've disabled some "Popular Products, recently sold, modules." And the problem went away. What is strange is that it just happened today. Nothing has changed for the past year. Just kind of popped up.

linnx:

What is strange is that it just happened today. Nothing has changed for the past year. Just kind of popped up."sudden" changes are always something to pay serious attention to.

http://www.zen-cart.com/forum/showthread.php?t=130161

Could you be more specific about the modules that you disabled? If the problem does lie with one or other of them, it would be helpful for other users to know.

Well we disabled all modules that may have caused it and the problem is still occurring. Did a search for "select distinct op.products_id," in developers tool kit and nothing came up.

Is that query being issued in one of the php files?

If someone can offer guidance or would like to peak at the admin/ftp I can do that.

thank you :)

Can't say where the query is coming from - it's not part of Zen Cart.

It's badly-written, but that's not much of a clue. It might be part of a poorly-constructed mod - most problems like this are. Or it might be that you've been hacked and some php is being executed in a location that wouldn't normally have php, which could explain why the the developers toolbar doesn't find it. Or it could be instigated by somebody connecting directly to your database. There's no way to tell just by looking at the error message.

If this were one of my clients sites I'd do a full file comparison against a fresh download of Zen Cart and ensure that I understood all the differences. Another approach would be to download the entire site and do a search for that query using a development IDE such as Dreamweaver, Eclipse or Aptana, unless your webhost offers an onserver search facility.

I asked the host which files were doing that query and he said

includes/modules/sideboxes/ezppt/recent_orders.php
includes/modules/sideboxes/order_history.php

How can I make it so those don't execute? I disabled everything in the side boxes controller. It is probably hard coded into one of the files.

I'm not sure that I believe them, at least about the includes/modules/sideboxes/order_history.php file. They may have just searched on the first part of the query, which would return that, not the bits that might be causing the problem.

The includes/modules/sideboxes/ezppt/recent_orders.php is a more likely candidate. It's not part of Zen Cart or any mods that I've used. Have you opened it up to see if it contains the errant query? Have you tried removing the file and seeing if that cures the problem?

Yes, that query is in there... I will rename that file.. I'll look at the other file now.

<?php $show_recent_orders_sidebox = true; define('MAX_DISPLAY_RECENTORDERS','20'); define('MIN_DISPLAY_RECENTORDERS','1'); define('BOX_HEADING_RECENTORDERS','Recently Sold items'); define('BOX_HEADING_TRUNCATE','30'); define('BOX_HEADING_TRUNCATE_MORE','...'); /*$recent_orders_query = "select distinct op.products_id,op.products_name , o.delivery_city , o.delivery_country from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op ,". TABLE_PAYPAL ." p where o.orders_id = p.order_id and o.orders_id = op.orders_id order by o.orders_id desc limit " . MAX_DISPLAY_RECENTORDERS;*/ $recent_orders_query = "select distinct op.products_id,op.products_name , o.delivery_city , o.delivery_country from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op ,". TABLE_PAYPAL ." p where o.orders_id = op.orders_id order by o.orders_id desc limit " . MAX_DISPLAY_RECENTORDERS; $recent_orders = $db->Execute($recent_orders_query); if ($recent_orders->RecordCount() >= MIN_DISPLAY_RECENTORDERS) { $title = BOX_HEADING_RECENTORDERS; $box_id = 'recentorders'; $rows = 0; while (!$recent_orders->EOF) { $rows++; $recentorders_list[$rows]['id'] = $recent_orders->fields['products_id']; $recentorders_list[$rows]['name'] = $recent_orders->fields['products_name']; $recentorders_list[$rows]['city'] = $recent_orders->fields['delivery_city']; $recentorders_list[$rows]['country'] = $recent_orders->fields['delivery_country']; $recent_orders->MoveNext(); } require($template->get_template_dir('tpl_recent_orders.php',DIR_WS_TEMPLATE, $current_page_base,'sideboxes'). '/tpl_recent_orders.php'); $title = 'Recently Sold items'; $title_link = false; if( $column_box_default==''){ require($down_column_box_default); }else{ require($template->get_template_dir($column_box_default, DIR_WS_TEMPLATE, $current_page_base,'common') . '/' . $column_box_default); } } ?>

You didn't respond to Kuroi's request to identify the mods you disabled. Posting their names and something about the source could really help other people avoid the problems you have had.

We disabled all mods in the sideboxes controller. But for some reason these mods were still running which leads me to believe they are hard coded. So disabling the mods did nothing to help - So if I told you which ones we disabled it wouldn't give any insight. This is a foreign installation (China) and is not like the standard zen cart I am used to dealing with. I will provide full details once I resolve it.

Thanks

OK, good luck getting the problems cleared out.