search

joomla-framework / database

Joomla Framework Database Package
GNU General Public License v2.0
28 stars 35 forks source link

[3.x-dev] New option to set sql_big_selects on MySQL/MariaDB #293

Closed richard67 closed 6 months ago

richard67 commented 7 months ago

Pull Request for CMS issues https://github.com/joomla/joomla-cms/issues/39479 and https://github.com/joomla/joomla-cms/issues/41156 .

Alternative to PR #266 .

Replaces PR #285 .

Same as PR #292 for the 2.0-dev branch, but in case if no new features will be accepted for the 2.0-dev branch or no upmerges from 2.0-dev into 3.x-dev will be made, this PR here can be used.

Summary of Changes

This Pull Request (PR) adds a new option sqlBigSelects to the MySQLi and MySQL (PDO) drivers to set the sql_big_selects session variable after connecting to the database.

When the new option is not used, i.e. not set in the connection parameters, then nothing is done, i.e. any value of the corresponding session variable will not be changed, and no additional SQL statement will be executed when connecting to the database.

This will allow Joomla CMS to set sql_big_selects to ON when they run into the SQL 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..

A PR for the CMS to use the new option has been created for the 4.4-dev branch of the CMS, see https://github.com/joomla/joomla-cms/pull/42557 .

This will only allow to fix the mentioned CMS issues. For providing methods for the CMS to display the value of the sql_big_selects in system information I have created PR #294 for the 3.x-dev branch.

Testing Instructions

Same as for PR #292, i.e. follow the testing instructions of the 4.4-dev CMS PR https://github.com/joomla/joomla-cms/pull/42557 , with the difference that you are testing on a 5.0-dev version of the CMS, and in step 5 use the following URL to find zip packages and a custom update URL with the changes from that CMS PR applied on the latest 5.0.2-dev nightly build of the CMS plus the changes from this PR here: https://test5.richard-fath.de/db-framework-pr-293/ .

Documentation Changes Required

None.

richard67 commented 6 months ago

Meanwhile I was able to identify the critical SQL query in the CMS core, and a pull request is ready for being merged: https://github.com/joomla/joomla-cms/pull/42576 . So for the CMS core the issue will be solved, and it will not need the changes from this PR here.

As it has turned out, the SQL error happens only on MariaDB but not on MySQL databases. It seems they handle the "max_join_size" differently.

That makes me think that the change proposed with this PR here is not really necessary, so I close it.