Pull Request for #39333 (comment) .

Summary of Changes

With Pull Request (PR) #36506 , the possibility to add an installer hint /** CAN FAIL **/ to SQL statements in update SQL scripts has been added, so that the installer ignores the failure and doesn't abort the SQL updates but continues with the next statement.

This makes it possible to handle e.g. SQL errors when trying to add columns which already exist, which can happen if an update SQL scripts is executed multiple times or the update history of a component is complicated. The reason why this is needed in that use case is because "IF EXISTS" or "IF NOT EXISTS" clauses on adding or removing columns is only supported by MariaDB but not by MySQL.

However, depending on the database type (MySQL or MariaDB) and the version and which database client (MySQLi or PostgreSQL) are used, and also on the kind of SQL statement, this doesn't work in all cases since the database client throws different kinds of exceptions depending on all that. In case of the MySQLi driver, the client doesn't return false but raises an exception when executing the SQL statement fails.

See the discussion beginning with this comment: #39333 (comment) .

This PR here fixes that by adding \RuntimeException to the try catch where we check the installer hint.

Testing Instructions

This test needs to be executed with a MySQL or MariaDB database with the MySQLi database driver and with PHP 8.1.

With PDO or with PHP 7.4 and the MySQLi driver, everything works without this PR.

1. Download and install the following extension zip package: https://test5.richard-fath.de/com_testupdatesql_1.0.0.zip
   This will install version 1.0.0 of a dummy component named "Test Update SQL" (com_testupdatesql), which has a database table named #__testupdatesql_table_1 (with #__ equal to your database prefix).
   The table has an id and a title column and one record with id=1 and title='Record 1'.

2. In phpMyAdmin, execute the following SQL statement to add a new columd description to that table:

ALTER TABLE `#__testupdatesql_table_1` ADD COLUMN `description` varchar(255) NOT NULL DEFAULT '';

(replace #__ by your database prefix).

This will simulate the situation after a failed update attempt to the next version 1.0.1 (see next step below) or the situation having a remainder from a previously installed version 1.0.1 where the database table has not been properly removed on uninstallation, or the situation when the extension developer had not provided SQL scripts in the right way so.

3. Update to version 1.0.1 of that component by downloading and installing the following extension zip package: https://test5.richard-fath.de/com_testupdatesql_1.0.1.zip
   This update will add a new column description to the database table, then update the existing first record by a suitable value for that column, and then insert a new, 2nd record into the table.
   Result:
   When this PR is not applied, the update fails with SQL error "Duplicate column name 'description'", see section "Actual result BEFORE applying this Pull Request"
   below.
   When this PR is applied, the update succeeds, see section "Expected result AFTER applying this Pull Request"
   below.

4. Verify the table structure and content in phpMyAdmin.

5. Examine the installation and update SQL scripts of the 2 versions mentioned above and make sure you understand them and that they are doing everything right like it should be in update SQL scripts for Joomla 4.

Hint: I could have created the 2nd zip in a way so it causes the error immediately without the need for executing step 2, but I wanted the scenario to use correct packages so testers understand what happens.

Actual result BEFORE applying this Pull Request

The update fails with SQL error "Duplicate column name 'description'".

The SQL statement to add the column and all statements after that haven't been executed, so the table still has one record only and no description column.

Expected result AFTER applying this Pull Request

The update succeeds.

All database changes have been applied, so the table has two records with the right description values.

Link to documentations

Please select:

• Documentation link for docs.joomla.org:

• No documentation changes for docs.joomla.org needed

• Pull Request link for manual.joomla.org:

• No documentation changes for manual.joomla.org needed

Additional remarks on documentation

It doesn't need to update any existing documentation because we don't have any which tells extension developers in detail how their update SQL scripts should be so that they are accepted by the database checker, too, and how to use the "/** CAN FAIL **/" installed hint or "INSERT IGNORE".

We should create such a documentation, but this should not stop this PR here.