error due to sql hosting limitation on big select #17580
Comments
|
Or you could switch to a host that doesn't have this limit :) |
|
@mbabker - thoughts? |
|
in my case the hosting platform is a customer decision and online.net is a big hosting compagny policy that have thousands of hosted web sites. they put the limit as default on shared server to have less big request blocking a too long time the database and they say that we can insert the option configuration line in the source code of the website. |
|
Can you enable Joomla Debug option at global configuration ? Then copy paste the "Call stack" here |
|
For J3.7.x you can test this solution. Remove red lines, add green lines. diff --git a/libraries/joomla/table/nested.php b/libraries/joomla/table/nested.php
index 29c4d13d1ae..17e10593645 100644
--- a/libraries/joomla/table/nested.php
+++ b/libraries/joomla/table/nested.php
@@ -1553,21 +1553,29 @@ class JTableNested extends JTable
* -2 <= 2 THEN -2 (If archived in trashed then trashed)
*/
+ // Find node and all children keys
+ $query->select("c.$key")
+ ->from("$table AS node")
+ ->leftJoin("$table AS c ON node.lft <= c.lft AND c.rgt <= node.rgt")
+ ->where("node.$key = " . (int) $pk);
+
+ $pks = $this->_db->setQuery($query)->loadColumn();
+
// Prepare a list of correct published states.
$subquery = (string) $query->clear()
->select("c2.$key AS newId")
->select("CASE WHEN MIN($newState) > 0 THEN MAX($newState) ELSE MIN($newState) END AS newPublished")
- ->from("$table AS node")
- ->innerJoin("$table AS c2 ON node.lft <= c2.lft AND c2.rgt <= node.rgt")
+ ->from("$table AS c2")
->innerJoin("$table AS p2 ON p2.lft <= c2.lft AND c2.rgt <= p2.rgt")
- ->where("node.$key = " . (int) $pk)
+ ->where("c2.$key IN (" . implode(',', $pks) . ")")
->group("c2.$key");
// Update and cascade the publishing state.
$query->clear()
->update("$table AS c")
->innerJoin("($subquery) AS c2 ON c2.newId = c.$key")
- ->set("$published = c2.newPublished");
+ ->set("$published = c2.newPublished")
+ ->where("c.$key IN (" . implode(',', $pks) . ")");
$this->_runQuery($query, 'JLIB_DATABASE_ERROR_STORE_FAILED');
|
|
this works perfectly and solve the problem |
|
Great, I wrote the same code for J3.8 at #17679. |
|
@sthibaut can you please answer above Comment of @csthomas? This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/17580. |
|
Set to "closed" on behalf of @franz-wohlkoenig by The JTracker Application at issues.joomla.org/joomla-cms/17580 |
|
closed as stated above. This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/17580. |
|
Hello, I am using Joomla Database Type | mysql and I 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 MAX_JOIN_SIZE=# if the SELECT is okay /home4/ofixcom1/public_html/libraries/joomla/database/driver/mysqli.php:665 Call stack| Function | Location1 | () | JROOT/libraries/joomla/database/driver/mysqli.php:665 |
Steps to reproduce the issue
in some hosting services like online.net the SQL_BIG_SELECT if off and max_join_size=300000
using some request like when creating or editing a menu entry on in a big menu 200 entries produce the error:
1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
Expected result
no error and the right administrating page
Actual result
the error message
1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
System information (as much as possible)
in mysql configuration SQL_BIG_SELECT if off and max_join_size=300000
Additional comments
to fix it i just have to edit the file libraries\joomla\database\driver\mysqli.php
and to insert the line
mysqli_query($this->connection,"SET SQL_BIG_SELECTS = 1;" );
in the function connect after the line 168( mysqli_query($this->connection, "SET @@SESSION.sql_mode = '';");)
could it be include in next updates, it is easier than patching every update
The text was updated successfully, but these errors were encountered: