Pull Request for Issue #43151 .

Summary of Changes

This pull request (PR) changes the syntax of the INSERT statement in the 5.1.0-2024-02-24.sql update SQL script introduced with PR #42799 for changing the core update site to TUF from row wise "normal" insert syntax INSERT INTO .. VALUES (...); to the INSERT INTO ... SELECT FROM syntax, which also works when the SELECT statement returns multiple rows, which can happen on sites with a long update history.

This solves the mentioned issue where you get an SQL error no. 1242 "Subquery returns more than 1 row" on MySQL/MariaDB or "more than one row returned by a subquery used as an expression" on PostgreSQL on such a site.

When I have made my PR #42988 , which was included in 5.1.0-beta2 to solve this issue, I hadn't noticed that the SQL error happens with the INSERTstatement before the UPDATE statement fixed by that PR.

But that PR was right. It needs both fixes, the one from that PR and the one from this PR here, to finally solve that issue.

Testing Instructions

Pre-conditions

The instructions below are for the case that you don't have a site which runs into that error because it doesn't have multiple update sites for the core.

Alternatively, if you have a copy of such a site, just update it to the latest 5.1 nightly build from here https://developer.joomla.org/nightly-builds.html to get the actual result, and update to ther pakckage created by drone for this PR which you can find here https://artifacts.joomla.org/drone/joomla/joomla-cms/5.1-dev/43169/downloads/75077/ for the expected result.

The test can be executed in a tool like e.g. phpMyAdmin for MySQL or MariaDB or phpPgAdmin for PostgreSQL databases.

It just needs a database with a Joomla 5.1.0-alpha or or 5.0.x or 4.4.x installation.

In all SQL statements in the following steps, replace the #__ in all table names by your actual database prefix including the underscore.

Step 1: Prepare a 2nd update site for the Joomla core.

You should of course not do that on a production site, and if you want to continue to use the site you should clean up the new records created by this step in the #__update_sites and the #__update_sites_extensions table after the test.

With MySQL or MariaDB:

INSERT INTO `#__update_sites` (`name`, `type`, `location`, `enabled`, `last_check_timestamp`) VALUES
('Test duplicate', 'collection', 'https://update.joomla.org/core/list.xml', 0, 0);

INSERT INTO `#__update_sites_extensions` (`update_site_id`, `extension_id`)
SELECT u.`update_site_id`, e.`extension_id`
  FROM (SELECT MAX(`update_site_id`) AS `update_site_id` FROM `#__update_sites`) AS u
  JOIN `#__extensions` AS e
 WHERE e.`type`='file' AND e.`element`='joomla';

With PostgreSQL:

INSERT INTO "#__update_sites" ("name", "type", "location", "enabled", "last_check_timestamp") VALUES
('Test duplicate', 'collection', 'https://update.joomla.org/core/list.xml', 0, 0);

INSERT INTO "#__update_sites_extensions" ("update_site_id", "extension_id")
SELECT u."update_site_id", e."extension_id"
  FROM (SELECT MAX("update_site_id") AS "update_site_id" FROM "#__update_sites") AS u
  JOIN "#__extensions" AS e ON 1=1
 WHERE e."type"='file' AND e."element"='joomla';

Step 2: Run the CREATE TABLE statement of the unmodified update SQL script "5.1.0-2024-02-24.sql"

With MySQL or MariaDB see https://github.com/joomla/joomla-cms/blob/5.1-dev/administrator/components/com_admin/sql/updates/mysql/5.1.0-2024-02-24.sql#L5-L14 .

With PostgreSQL see https://github.com/joomla/joomla-cms/blob/5.1-dev/administrator/components/com_admin/sql/updates/postgresql/5.1.0-2024-02-24.sql#L5-L14 .

Step 3: Run the INSERT statement of the unmodified update SQL script "5.1.0-2024-02-24.sql"

With MySQL or MariaDB see https://github.com/joomla/joomla-cms/blob/5.1-dev/administrator/components/com_admin/sql/updates/mysql/5.1.0-2024-02-24.sql#L17-L18 .

With PostgreSQL see https://github.com/joomla/joomla-cms/blob/5.1-dev/administrator/components/com_admin/sql/updates/postgresql/5.1.0-2024-02-24.sql#L18-L19 .

Result: See section "Actual result BEFORE applying this Pull Request" below.

Step 3: Run the INSERT statement of the update SQL script "5.1.0-2024-02-24.sql" from this PR

With MySQL or MariaDB see

With PostgreSQL see

Result: See section "Expected result AFTER applying this Pull Request" below.

Actual result BEFORE applying this Pull Request

With MySQL or MariaDB SQL error #1242 Subquery returns more than 1 row, with PostgreSQL ERROR: more than one row returned by a subquery used as an expression.

Expected result AFTER applying this Pull Request

No SQL error, for each update site a record is inserted into the #__tuf_metadata table.

Link to documentations

Please select:

• No documentation changes for docs.joomla.org needed

• No documentation changes for manual.joomla.org needed