oscommerce getting me suspended from my host

[Guest]

this is the second time in a week this has happened, and here's the query that got me suspende:

 

 177958 | db_name | localhost | db_name | Query | 143 | Copying to tmp table | select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_p |
| 177984 | db_name | localhost | db_name | Query | 110 | Copying to tmp table | select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_p |
| 178005 | db_name | localhost | db_name | Query | 82 | Copying to tmp table | select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_p |
| 178006 | db_name | localhost | db_name | Query | 82 | Copying to tmp table | select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_p |
| 178016 | db_name | localhost | db_name | Query | 71 | Copying to tmp table | select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_p |
| 178028 | db_name | localhost | db_name | Query | 65 | Copying to tmp table | select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_p |
| 178029 | db_name | localhost | db_name | Query | 64 | Copying to tmp table | select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_p |
| 178030 | db_name | localhost | db_name | Query | 57 | Copying to tmp table | select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_p |

 

what page could these people/bots have been looking at that caused the high server usage?

[Guest]

Why don't you put in some debug code and find out?

[Guest]

not sure how to do that?

i don't think this is something that happens 24/7, it seems to come at random.

[safoo]

search for chemo's query output contribution that also logs queries that take longer than x number of seconds to a text file. install it and that will help you debug.

[Jack_mcs]

You could also search your file for

select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_p

There probably aren't a lot of files where that would be found. It might help slim down the possibilities.

 

Jack

[cannuck1964]

if you have the all prods contribution then when this poage is called, the script hits the DB each time for tax rates, there is a contribution using a different tax class (I think Chemo did it up) which when installed will help out in this.

 

Basically if add tax to the price is turned off, and using this contribution should fix the issue.

 

The only other place which might be an issue is the also sold module which may also slow down the system (I do not think it does many querries though).

 

I am thinking it is from the tax querries issue though ;)

 

Peter M.

[Guest]

The only way to know is by installing the query output code and tracing down that query!

[Guest]

You could also search your file for

select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_p

There probably aren't a lot of files where that would be found. It might help slim down the possibilities.

 

Jack

found a "distinct" in index.php:

 

<?php

$products_options_name_query = tep_db_query("select distinct popt.products_options_id, popt.products_options_name from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_ATTRIBUTES . " patrib where patrib.products_id='" . (int)$HTTP_GET_VARS['products_id'] . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)$languages_id . "' order by popt.products_options_name");

while ($products_options_name = tep_db_fetch_array($products_options_name_query)) {

$products_options_array = array();

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'");

while ($products_options = tep_db_fetch_array($products_options_query)) {

$products_options_array[] = array('id' => $products_options['products_options_values_id'], 'text' => $products_options['products_options_values_name']);

if ($products_options['options_values_price'] != '0') {

$products_options_array[sizeof($products_options_array)-1]['text'] .= ' (' . $products_options['price_prefix'] . $currencies->display_price($products_options['options_values_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) .') ';

}

}

 

to be quite honest, i have no idea what it's for, but right above it, there is this:

			<?php
$products_attributes_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_ATTRIBUTES . " patrib where patrib.products_id='" . (int)$HTTP_GET_VARS['products_id'] . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)$languages_id . "'");
$products_attributes = tep_db_fetch_array($products_attributes_query);
if ($products_attributes['total'] > 0) {
?>

 

 

 

.....then in the same file, there is also this:

	// optional Product List Filter
		if (PRODUCT_LIST_FILTER > 0) {
			if (isset($HTTP_GET_VARS['manufacturers_id'])) {
				$filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' order by cd.categories_name";
			} else {
				$filterlist_sql= "select distinct m.manufacturers_id as id, m.manufacturers_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "' order by m.manufacturers_name";
			}

 

any suggestions?

[jeroman]

We have the exact same issue with high mysql load and that "copying to tmp table".

It occurs every now and then, multiple times per day and server load can go up to 30-40.

I tested to change the session_store handler if that was the case but no result there.

I also tweaked mysql with different values but it didn't help.

 

What contributions are you running ?

[RonEnglund]

Same problem here.

 

Did you find a solution to this?