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.

@sthibaut

Can you enable Joomla Debug option at global configuration ?
then go at the page throwing the error
and you will get a "Call stack" with more information

Then copy paste the "Call stack" here
This way we will know which query is causing this

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.
If you think the patch is useful then please mark it as tested successful at https://issues.joomla.org/tracker/joomla-cms/17679
You could add your test instruction in comment.

@sthibaut can you please answer above Comment of @csthomas?

@franz-wohlkoenig

2 successful tests for @csthomas PR that fixes this issue (1 by me and 1 by @sthibaut)
i think this can be closed

Set to "closed" on behalf of @franz-wohlkoenig by The JTracker Application at issues.joomla.org/joomla-cms/17580

closed as stated above.

Hello, I am using Joomla

Database Type | mysql
Database Version | 5.6.41-84.1
Database Collation | utf8_general_ci
Database Connection Collation | utf8mb4_general_ci
PHP Version | 7.3.18
Web Server | Apache
WebServer to PHP Interface | cgi-fcgi
Joomla! Version | Joomla! 3.9.18 Stable [ Amani ] 21-April-2020 19:30 GMT
Joomla! Platform Version | Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT

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 | Location

1 | () | JROOT/libraries/joomla/database/driver/mysqli.php:665
2 | JDatabaseDriverMysqli->execute() | JROOT/libraries/joomla/database/driver.php:1550
3 | JDatabaseDriver->loadColumn() | JROOT/administrator/components/com_virtuemart/helpers/vmmodel.php:864
4 | VmModel->exeSortSearchListQuery() | JROOT/administrator/components/com_virtuemart/models/product.php:799
5 | VirtueMartModelProduct->sortSearchListQuery() | JROOT/components/com_virtuemart/views/category/view.html.php:288
6 | VirtuemartViewCategory->display() | JROOT/components/com_virtuemart/controllers/category.php:54
7 | VirtueMartControllerCategory->display() | JROOT/libraries/src/MVC/Controller/BaseController.php:710
8 | Joomla\CMS\MVC\Controller\BaseController->execute() | JROOT/components/com_virtuemart/virtuemart.php:120
9 | require_once() | JROOT/libraries/src/Component/ComponentHelper.php:402
10 | Joomla\CMS\Component\ComponentHelper::executeComponent() | JROOT/libraries/src/Component/ComponentHelper.php:377
11 | Joomla\CMS\Component\ComponentHelper::renderComponent() | JROOT/libraries/src/Application/SiteApplication.php:194
12 | Joomla\CMS\Application\SiteApplication->dispatch() | JROOT/libraries/src/Application/SiteApplication.php:233
13 | Joomla\CMS\Application\SiteApplication->doExecute() | JROOT/libraries/src/Application/CMSApplication.php:196
14 | Joomla\CMS\Application\CMSApplication->execute() | JROOT/index.php:49