1104 Error - Really need some help please!

[Guest]

Hi.

 

I'm having this problem on a live site and really would appreciate some help.

 

The databse for this site is pretty huge with a multitude of products.

 

As it has grown bigger, I started getting the 1104 error.

 

I checked wit the host and there is no way to increase the MAX_JOIN_ROW allocation, (currently 9mb), so only the tep_db_query("set sql_big_selects=1"); fix will work I think.

 

However - it isnt fixing the problem for me.

 

Originally I was getting the 1104 error message on every product listing sub category page that displayed the new products module but I applied the fix and it went away.

 

However, I am now getting the problem on some of the product_info pages themselves.

 

I have added the fix to product_info.php like this:

 

require('includes/application_top.php');

 

require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_PRODUCT_INFO);

 

tep_db_query("set sql_big_selects=1");$product_check_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");

$product_check = tep_db_fetch_array($product_check_query);

?>

 

 

But it is still happening. there is no product information and it is impossible for clients to make a purchase as only the 1104 error message appears where i would normally have the product details.

 

Do I need to add the fix elsewhere on the page, (or elsewhere on other pages?)

 

Is my syntax ok?

 

I'm aware of the other threads detailing this issue but as I have followed the instructions on there without success, I'm hoping someone has a newer answer or experience of this problem themselves.

 

any help much appreciated.

 

thanks

[Guest]

give us a url?

[Guest]

give us a url?

 

 

This is it: http://www.eagerbeeverphotography.co.uk/fl...21_389_390.html

[Guest]

have a look at this post it may help?

 

http://www.oscommerce.com/forums/index.php?sho...=318366&hl=

 

also what v of osC are you using?

[Guest]

have a look at this post it may help?

 

http://www.oscommerce.com/forums/index.php?sho...=318366&hl=

 

also what v of osC are you using?

 

Thanks - I see what you a driving at.

 

Remove a lot of redundant database queries from the code for modules I dont use like customer reviews , manufacturers etc?

 

Thats certainly worth a look.

 

In the meantime -does anyone have any quick fixes to get the site displaying properly . Its a live site and will obviously be losing custom if people cant view and buy a lot of the products!

[Guest]

have a look at this post they look like the may have sorted it:

 

 

 

see this post

[Guest]

what are you using:

 

for

 

osCommerce Version:

PHP version:

MySQL version:

 

??

[Jan Zonjee]

In the meantime -does anyone have any quick fixes to get the site displaying properly .

Since it is unlikely there is no description of the product my guess is it would be easily possible to avoid a join in that query by leaving out products_description:

//  $product_check_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");
 $product_check_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' ");
 $product_check = tep_db_fetch_array($product_check_query);

But then I guess the next query will have the same problem (and obviously a lot more). Then you would need to do two queries I guess. One for table products and pick up the fields needed from table products and then the next one for the fields from products description (pd.products_name, pd.products_description, and pd.products_url).

Cumbersome, but not impossible.

[Guest]

what are you using:

 

for

 

osCommerce Version:

PHP version:

MySQL version:

 

??

 

 

Its version 5.0.67 for the php

php 5

 

not sure of the oscomerce version but I think its 2.2

[Guest]

ref: not sure of the oscomerce version but I think its 2.2

 

look in ADMIN >> TOOLS >> SERVER INFO

 

it will be at the top of the page

 

if it is MS2.2 then think of upgrading to RC2.2a

 

as MS2.2 has loads of PHP5 and MSQL 5 prob's

[Guest]

From what I can gather, adding the fix to products_new.php, and products_info.php works fine and those pages will display correctly.

 

My problem seems to lie within includes/modules/product_listing.php

 

My thinking is that the only page ith the error on it now, is the page where all the products within a give category should appear, (the long list in the categroy or sub-category folder). On that basis, I'm assuming tha tthis is where the fix should go?

 

If I could work out the appropriate line to insert the tep_db_query("set sql_big_selects=1"); fix then I would asume that would sort it.

 

Problem is, I cant see where it should go.

 

I've been looking for the tep_db_query string as found on products_new.php page, but nothing like that appears in includes/modules/product_listing.php

 

Am I on completely the wrong track?

[Guest]

Thanks for that.

 

It seems I have php 4.48 and oscommerce - 2.2MS2

 

I'll take your advice and see if I can upgrade.

 

I'll set up a dummy site lnked to the existing databsase first and work out any issues that way.

[Guest]

I'm now running an RC2.2 vanilla site with no contributions, linked to the existing database.

 

I've addd the standard fix to product_info.php, products_new.php , includes/modules/new_products.php but still get the same 1104 error message.

 

I'm stumped!

[Guest]

FIXED IT!

 

The solution.

 

After adding the tep_db_query("set sql_big_selects=1"); to products_new.php and the other affected pages, you still need to add it to index.php

 

See below for an example:

 

 

	   tep_db_query("set sql_big_selects=1");$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
  } else {
// We show them all
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
  }
} else {
// show the products in a given categorie
  if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
	tep_db_query("set sql_big_selects=1");$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
  } else {
// We show them all
	tep_db_query("set sql_big_selects=1");$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
  }
}

 

 

The only problem reaining is that the page load times are quite slow, so the code could do with optimizing a bit more to reduce the work the database is having to do - but it does work and all the pages display properly.

 

thanks everyone for the help and ideas and I hope that this solution helps someone else.

 

Once I'm sure it all works i'll add the modified vanilla files and instructions as a contibution.

 

If anyone has something to add before then - please post so I can put together a reliable contri to solve this for other people.

[Guest]

I have now bundled together the solution I arrived at for this problem and added it as a contribution:

 

oscommerce 1104 error fix

 

My first contribution, so if anyone has a way to improve it or make it clearer please feel free to do so.

 

thanks for the help from all concerned.