EDIT Orders mod error: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows

When we click the add product after you click the edit we get this error.

1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
in:
[SELECT products_name, p.products_id, categories_name, ptc.categories_id FROM hspproducts p LEFT JOIN hspproducts_description pd ON pd.products_id=p.products_id LEFT JOIN hspproducts_to_categories ptc ON ptc.products_id=p.products_id LEFT JOIN hspcategories_description cd ON cd.categories_id=ptc.categories_id ORDER BY categories_name]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

Any thoughts?

Can't seem to reproduce the bug, but I really don't understand when this happens for you. Is the "edit" button you speak of the edit button you click to enter then edit orders page or is it the update at the bottom of the page? Does it do this for every order? Are there other scenarios where the add products works fine? What version of the mod and what version of zen cart are you using?

Any extra info would be helpful to troubleshoot this.

zencart 1.38a
edit order 1.5.3

when in the admin area you click on an order already placed and click details of the order then click edit.

below the items ordered there is a blue highliter section (ADD PRODUCT) if you click this you get the error, if i remember right this should bring up your product list to add an item.

It looks like this is a bug in SQL that was fixed. You'd probably need to check with your server to see if they can implement a newer version of SQL or reconfigure their MySQL settings so this doesn't happen. Check your version of SQL under "Version" in admin -- It looks like this issue was fixed after v5.1.23 of MySQL. If your server uses MySQL v5.1 before .23, you'd probably have this issue unless your server implemented a workaround.

Here is a link to more info on the bug:

http://bugs.mysql.com/bug.php?id=20023

The other possibility is that your server has setup a MAX_JOIN_SIZE which limits the output of any query. This is a problem that they would need to address.

Either way, call your server and let them know that their MySQL setup is broken and explain the situation.

There is one other option that may work from your end. Add the following code:

mysql_query("SET OPTION SQL_BIG_SELECTS=1");

right above this line in edit_orders.php:

$result = $db -> Execute("SELECT products_name, p.products_id, categories_name, ptc.categories_id FROM " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON pd.products_id=p.products_id LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc ON ptc.products_id=p.products_id LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON cd.categories_id=ptc.categories_id ORDER BY categories_name");

This should override the MAX_JOIN_SIZE and allow your query.

looks like mysql is

Database: MySQL 5.0.45

I will try the other option to see if it helps

FYI:

The work around worked.

Thank you....

For those that come across this problem again, adding an index to the offending table can remove the problem as well.

If MySQL has to parse through thousands of records, with no indexes defined, it decides that this is nearly impossible, and so states there are too many. If you define an index on one or more columns, MySQL then has an indexed record set to look against, instead of the thousands of individual records, and therefore finds it a little less difficult!

And - hey-presto - the SQL statement runs faster, and you have a quicker page load time! Everyone's a winner!

Absolute