Error 1104 on update attempt

[Nibbik]

When I try to update, Joomla will give me a 1104 database-error: "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".
One could say that this is not a problem caused by Joomla, it's just that the CMS runs on too many add-ons (is not the case: nearly clean install) or that the provider has set the limit too low (max_join_size=67108864 - IMHO a reasonable limit).
It has been discussed quite a lot on the internet over the last few years, seems to be a problem to many users, with advised hacks like allways do SEL-query "SET SQL_BIG_SELECT=1" after establishing every database connection e.g. editing the MysqliDriver.php-file, which is then discouraged by others for several obvious reasons.

I woud state that if a Joomla update requires a SELECT statements to search for over 6.7 million (joined) database entries, it might be wise to either try to economise the SELECT statements for the updates, or - easiest solution - give the option to SET SQL_BIG_SELECT=1 for the Joomla update module only.

[richard67]

I woud state that if a Joomla update requires a SELECT statements to search for over 6.7 million (joined) database entries

On https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html they say:

MySQL 8.0.30 and earlier: Do not permit statements that probably need to examine more than max_join_size rows (for single-table statements) or row combinations (for multiple-table statements) or that are likely to do more than max_join_size disk seeks. By setting this value, you can catch statements where keys are not used properly and that would probably take a long time. Set it if your users tend to perform joins that lack a WHERE clause, that take a long time, or that return millions of rows.

So on the mentioned MySQL versions, the parameter doesn't refer to the number of rows, it refers also the the cumber of row combinations or disk seeks. Nevertheless we should check if the issue is caused by a JOIN which has the the mentioned problems.

@Nibbik When exactly do you get the issue? When a Joomla update was found and you start the update? Or also when you start an update for an extension?

[Nibbik]

Thank you for your reply
I get this error when I open the page for updating Joomla core:
.../administrator/index.php?option=com_joomlaupdate
Funny is that the main administrator page ("Home dashboard") can tell me in the Notifications area if Joomla needs updating. But when I hit the tile to bring me to the update page, the error hits in and the page shows me nothing but the error message.

[Nibbik]

Is there any way I can retrieve the SQL-statement that causes this 1104-error? To see if we can simplify the JOIN, for instance by limiting the results in a subquery before the JOIN kicks in? It should be possible to create a SQL-statement that requires less than 67108864 (=67 million, not 6.7 as I mentioned earlier) possible hits to be evaluated...

[richard67]

The database server log should show which statement causes the error. On a hosting company you might not be able to access that log, but e.g. on a local Ubuntu Linux it is /var/log/mysql/error.log.

[Nibbik]

Out of my reach on my host, sadly.
Any way to make Joomla log queries that cause errors?

[Nibbik]

It is, by the way, a frequently reported problem:
https://issues.joomla.org/tracker/joomla-cms/41156
https://issues.joomla.org/tracker/joomla-cms/17580
https://issues.joomla.org/tracker/joomla-cms/39479
https://forum.joomla.org/viewtopic.php?f=811&t=995872
https://joomlanl.nl/forum/4-x-installatie-update-upgrade-backup-en-verhuizing/58564-foutmelding-joomla-4-26-4-27
https://forum.joomla.de/thread/19318-es-ist-ein-fehler-aufgetreten-1104-the-select-would-examine-more-than-max-join-s/
etc.
Everywhere the BIG_SELECT workaround, but I would prefer an improvement of the culprit SQL-statement

[richard67]

@Nibbik For the issue when trying to update the core I have created a pull request which fixes it. Please test #42576 .