Pull Request for Issue #32019 .

Summary of Changes

Check if the query cache is available and enabled (variable query_cache_type = 'ON') before trying to switch it off when connecting to the database (MySQL or MariaDB) with debug on.

While MySQL have removed the query cache with version 8.0.2, MariaDB still seem to support it and have even added another mode query_cache_type = 'DEMAND' in past. In this mode, only queries with the SQL CACHE clause will be cached, see description here:
https://mariadb.com/kb/en/server-system-variables/#query_cache_type. Since Joomla doesn't set that SQL CACHE clause for its queries, there is no need to disable query cache on MariaDB if it is working in that 'DEMAND' mode, that's why this PR only checks for query_cache_type = 'ON'.

Testing Instructions

Requirements

This PR is only relevant for MySQL or MariaDB databases, not for PostgreSQL.

It needs tests for all available database drivers for these kinds of databases, which can be selected Database Settings in field "Database Type" on the "Server" tab of Global Configuration which can be one or more of these 3, depending on the available PHP extensions:

• MySQL
• MySQLi
• MySQL (PDO)

If you have 2 MySQL database servers, one with a version lower than 8, e.g. 5.7 where query cache is available if not using a special compilation, and another one with a version of 8.0.2 or later where query cache is not available, test with both of them.

If you have MariaDB where query cache is availably in any case if not using a special compilation, test with any version.

Please test with all you have, and report back which drivers you have tested with which kind of database with which version.

For reproducing the issue, it is enough to simply review the code places mentioned in the description of issue #32019 , so the following tests are all for the expected result AFTER applying this PR.

The effect of this PR is hard to test. To simplify that, it needs to add some debug log to the database drivers, as described in the following section "Preparation".

Preparation

1. If you don't have an installation of Joomla 3.9.24 or current staging or latest 3.9.x nightly build, make a new installation.
2. Make sure to have following settings in Global Configuration:

• Tab "System", Section "Debug Settings", value "Debug System" = "No".
• Tab "Server", section "Server Settings", value "Error Reporting" = "Maximum" or "Development".

3. If you have several kinds of databases available, see the above section "Requirements" for details, use a tool like e.g. phpMyAdmin to make an SQL export of the current databases used for this installation and import it into some empty database(s) on the other available database server(s). This will make it possible just to change the database connection to the other database in Global Configuration for testing, so it does not need to make a new installation and do the next steps again.
4. Apply the patch of this PR.
5. Create an empty file test-pr-32028.log in folder administrator/logs and make sure that the webserver user or group has read and write access to it.
6. For each database driver which you can test with, see the above section "Requirements" for details, edit the PHP file and add some debug output with error_log around following lines

• MySQL

  joomla-cms/libraries/joomla/database/driver/mysql.php

  Line 104 in 91a9a73

  mysql_query('SET query_cache_type = 0;', $this->connection);

• MySQLi

  joomla-cms/libraries/joomla/database/driver/mysqli.php

  Line 196 in 91a9a73

  mysqli_query($this->connection, 'SET query_cache_type = 0;');

• MySQL (PDO)

  joomla-cms/libraries/joomla/database/driver/pdomysql.php

  Line 162 in 91a9a73

  $this->connection->query('SET query_cache_type = 0;');

  
  so that it looks as follows e.g. for MySQL:

// DEBUG for PR 32028
error_log(date('Y-m-d H:i:s') . ' ' . basename(__FILE__) . ": Query cache is enabled.\n", 3, JPATH_ADMINISTRATOR . '/logs/test-pr-32028.log');

mysql_query('SET query_cache_type = 0;', $this->connection);

// DEBUG for PR 32028
if (!$this->hasQueryCacheEnabled())
{
	error_log(date('Y-m-d H:i:s') . ' ' . basename(__FILE__) . ": Query cache has been disabled.\n", 3, JPATH_ADMINISTRATOR . '/logs/test-pr-32028.log');
}

Instructions

1. Using a tool like e.g. phpMyAdmin, verify if query cache is available on your database server by executing the following SQL query.
   SHOW VARIABLES LIKE 'have_query_cache';
   If the result has Value = 'YES', the database server supports query cache, otherwise ('NO' or no result) not.
2. If the result of the previous step was 'YES', verify if query cache is enabled for the current database connection by executing the following SQL query.
   SHOW VARIABLES LIKE 'query_cache_type';
   If the result has Value = 'ON', the query cache is enabled. On a MariaDB database, if Value = 'DEMAND', the query cache is enabled in 'DEMAND' mode. Otherwise the value is 'OFF', i.e. query cache is disabled.
   Note the result so you can later restore your original server configuration.
3. If the result of step 1 was 'YES' and step 2 was 'ON' or in case of MariaDB also 'DEMAND', i.e. query cache is available and not disabled, disable query cache in your database server's configuration file and restart the database server.
   See the following documentation for how to do that:

• MySQL https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html
• MariaDB https://mariadb.com/kb/en/query-cache/
  Then connect again with tool like e.g. phpMyAdmin and repeat step 3. Make sure that the result is 'OFF'.

4. Now as the query cache is either not available or switched off, go to Global Configuration, Tab "System", Section "Debug Settings" and change "Debug System" to "Yes" and then save while watching the file JPATH_ADMINISTRATOR . '/logs/test-pr-32028.log' which you have created in step 5 of the preparation.
   Use the "Save" button a few times.
   Result: Nothing happens with that log file.
5. If you can use several database divers, change "Database Type" in Global Configuration, tab "Server", section "Database Settings" and repeat step 4.
   Result: Nothing happens with that log file.
6. If the result of step 1 was not 'YES', so the database server doesn't support query cache, you are done with the test for this database and can skip the following steps. If you have another database server available, import the database export created in step 3 of the preparation into that other database, change the database connection in Global Configuration to that other database and repeat the test beginning with step 1.
7. Go to Global Configuration, Tab "System", Section "Debug Settings", change back "Debug System" to "No" and save.
8. Enable query cache in your database server's configuration file and restart the database server. See step 3 for links to documentation.
9. Check again if query cache is enabled as described in step 2 and make sure that the result is 'ON'.
10. Go to Global Configuration and use the save button a few times while watching the file JPATH_ADMINISTRATOR . '/logs/test-pr-32028.log' which you have created in step 5 of the preparation.
    Result: Nothing happens with that log file.
11. Now change "Debug System" to "Yes" and then save while watching the file JPATH_ADMINISTRATOR . '/logs/test-pr-32028.log' which you have created in step 5 of the preparation.
    Result: You get 2 messages.

2021-01-17 16:04:02 DEBUG from pdomysql.php: Query cache is enabled.
2021-01-17 16:04:02 DEBUG from pdomysql.php: Query cache has been disabled.

12. Use the "Save" button a few times.
    Result: Every time saving the configuration both debug messages are produced 2 times, e.g.

2021-01-17 16:08:02 DEBUG from pdomysql.php: Query cache is enabled.
2021-01-17 16:08:02 DEBUG from pdomysql.php: Query cache has been disabled.
2021-01-17 16:08:02 DEBUG from pdomysql.php: Query cache is enabled.
2021-01-17 16:08:02 DEBUG from pdomysql.php: Query cache has been disabled.

13. If you can use several database divers, change "Database Type" in Global Configuration, tab "Server", section "Database Settings" and save while watching the log file.
    Result: You can see both messages, one time from the old driver and one from the current driver, e.g. for a change from "MySQL (PDO)" to "MySQLi":

2021-01-17 16:09:11 DEBUG from pdomysql.php: Query cache is enabled.
2021-01-17 16:09:11 DEBUG from pdomysql.php: Query cache has been disabled.
2021-01-17 16:09:11 DEBUG from mysqli.php: Query cache is enabled.
2021-01-17 16:09:11 DEBUG from mysqli.php: Query cache has been disabled.

14. Repeat step 13 so you have tested for each database diver you can use and have come back to the first tested one.
    Result: See previous step 13.
15. Go to Global Configuration, Tab "System", Section "Debug Settings", change back "Debug System" to "No" and save while watching the log file.
    Result: You can see both messages only one time, i.e. like in step 11.
16. Use the save button a few times, and finally at the end "Save and close", while watching the log file.
    Result: Nothing happens anymore in the log file.
17. Extra test if you have a MariaDB database: Change the query cache type to 'DEMAND' in your database server's configuration file and restart the database server. See step 3 for links to documentation. Then repeat steps 4 and 5.
    Result: Nothing happens in the log file.

Don't forget to restore your database server's original configuration if you have modified it during the test.

If you have another database server available, import the database export created in step 3 of the preparation into that other database, change the database connection in Global Configuration to that database and repeat the test beginning with step 1.

Actual result BEFORE applying this Pull Request

When connecting to a MySQL or MariaDB database with "Debug System" switched on in Global Configuration, the database driver attempts to disable the query cache in any case, as you can see in the description of issue #32019 at the mentioned places in the database drivers' code.

Expected result AFTER applying this Pull Request

When connecting to a MySQL or MariaDB database with "Debug System" switched on in Global Configuration, the database driver only attempts to disable the query cache if the query cache is available and enabled and in case of MariaDB also not in 'DEMAND' mode.

On MySQL databases with server versions 8.0.2 or later, query cache is not available, so it will for sure not be touched by the driver.

Additional information

While working on this PR I've seen that function hasProfiling for the check if profiling is available of the drivers is wrong.

The function checks only the isset but not the value of the variable, so the driver would try to switch on profiling if the have_profiling variable has value 'NO'. See following documentation:

• MySQL https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_have_profiling
• MariaDB https://mariadb.com/kb/en/server-system-variables/#have_profiling

This is a bug which has to be fixed with another PR.

In J4 thinks are completely different, so we don't have that problem with query cache handled by this PR, but we have the problem with not checking the value of have_profiling here: https://github.com/joomla/joomla-cms/blob/4.0-dev/plugins/system/debug/debug.php#L444-L467 . This needs also to be fixed with another PR.

Another thing which may confuse people who do code review for this PR is that the check with SHOW VARIABLES LIKE is done for a string result ('ON'), but the variable is then set with a numeric value 0 when the cache is disabled. But this is normal behaviour for (session) variables of type ENUM in both MySQL and MariaDB.

Documentation Changes Required

None.