I have set-up a cut down version of OpenCart and incorporated it as a site search linked to and from a static Freeway built site.
I have a free module installed which imports and exports csv spreadsheets. As the database has grown, I now get an error when trying to export the database from within the back end of OpenCart - importing is fine.
The first line of the returned error - written to the csv spreadsheet is: Notice: Error: 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 Error No: 1104
Research online has made me think that this is likely a restriction on the hosting in relation to MAX_JOIN_SIZE, and the ISP of my client has sent this in reply to the issue:
"Whilst you can’t set that on a per database, or even per table level, what you can do is simply add it to the query that you’re running. That is, just add
SET OPTION SQL_BIG_SELECTS=1
to the code which forms the query - and it will then be possible to run larger queries. The setting is already very high though, in the hundreds of thousands of rows."
Do you have any way to prune the database? It sounds as though you may have some duplication in there if the site you’re searching is hand-made. That could explain the enormous result set. Do you have a utility like PHPMyAdmin on the server where you could inspect the table in question, and see how many rows it has?
Otherwise, the suggestion from your ISP would have you open up your PHP code, locate the part that is used to generate the offending query, and add that line to the query.
Walter
On Apr 17, 2012, at 1:00 PM, Jonathan Riddle wrote:
Hi all,
I have set-up a cut down version of OpenCart and incorporated it as a site search linked to and from a static Freeway built site.
The search part doesn’t touch the static Freeway site. The OpenCart elements are installed at www.mysite.co.uk/search.
It is a fairly basic cart based area of the site, no more than 400 items, when the module and hosting are geared up to accommodate 10,000+ items.
There are php and xml files with the module, and so I maybe need to see if I can find the query, and I can access and manage the site using PHPMyAdmin.
I checked through the few php files associated with the module and found where the first query to the database was. So, with fingers tightly crossed, I added the line below and tried the module again…
mysql_query(“SET OPTION SQL_BIG_SELECTS=1”);
and it worked! Thanks for the pointer, Walter.
Been a huge projct on a tight deadline but I think I have got there with this. Looks great, even if I do say so myself!