Database queries took down host. Please help

[linnx]

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

[DrByte]

> 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?

[kuroi]

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.

[linnx]

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.

[DrByte]

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

[kuroi]

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.

[linnx]

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?

[linnx]

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

thank you :)

[kuroi]

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.

[linnx]

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.