MAX_JOIN_SIZE error??

[joss54]

I keep getting this error message

 

1104 - The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok

select p.products_image, pd.products_name, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '4' and p2c.categories_id = '230' order by pd.products_name limit 0, 10

 

Someone wrote that it was a bug in mysql 3.23, but I have version 4.

I also checked mysql and I saw

 

max_join_size = 10000

 

Is this the problem? should I increase it?

Please help my shop is down...

 

Thanks in advance...

[webadmin]

I too have had a similar error message when viewing product details - includes/product-info.php

 

The problem was solved by adding the "set sql_big_selects=1" statement to the database, just as the script sends its SELECT queries to get the data.

 

The revised code now looks like this

 

[ <?php

 

require('includes/application_top.php');

 

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

// SQL_BIG_SELECTS = 1 - added to solve 1104 MYSQL error message

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);

?>

]

 

Hope this helps

[breamillman]

I too have had a similar error message when viewing product details - includes/product-info.php

 

The problem was solved by adding the "set sql_big_selects=1" statement to the database, just as the script sends its SELECT queries to get the data.

 

The revised code now looks like this

 

[ <?php

 

require('includes/application_top.php');

 

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

// SQL_BIG_SELECTS = 1 - added to solve 1104 MYSQL error message

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);

?>

]

 

Hope this helps

 

 

 

WOOHOO!!

 

I love when I search for a fix and find it. & even better when it's something easy!

Thanks so much.

You're my hero today.

:thumbsup:

~BREA

[Kenja]

Ditto the last guy. I thought it would take me a while to slog through this one. You are the man!

[jackelton]

Hi FWIW

This problem has arisen for me in products attributes listing in admin/products_attributes.php. The 1104 error arose here with a clients database that is around 24mb in size. Running osC v2 rc2 on php5. After a little messing about the fix tep_db_query("set sql_big_selects=1"); worked when inserted around line 20 in products_attributes.php. Other entries here give the impression that the problem lay in products_info.php. Which led me a dance as I didn't realise their problem lay in the catalog and not in the admin area. (Woops!). Anyway, it worked, so thanks guys.

:)