Would be good to identify the code in core where we rely on the db id. For me it makes also no sense to hardcode the ids.

• The arbitrary assumption that all core extensions have extension_id < 10000
• The use of a files extension to track updates for core Joomla (ID 700)
• Inserting new extensions in update SQL files to try and match the new install ordering (this is where it all blows up if auto increment gets reset on a table)
• The use of those hardcoded extension IDs in the core update script (i.e. inserting new menu items for admin components, because the menu table has a FK to extensions)

I don't think there's much use of the IDs beyond that, but it's just enough that if things aren't exactly where you expect them to be it creates issues.

Just took inventory. With where we're at with the privacy tools then the 4.0 work the plugins are at ID 497. So sooner or later something needs to be figured out because these reserved "blocks" are about to need re-thinking at least in the context of plugins.

I couldnt see where the code is that defines the blocks but cant we just add a new block for core id

There is no code defining reserved blocks, it's all procedural aside from the statement in our install SQL files setting autoincrement to 10000. Aside from extension ID 700 (the core files extension used to push core update checks through the update library), there is nothing in the code that requires that we have reserved ID ranges, or even known extension IDs, and as pointed out trying to create reserved ID ranges or force known IDs creates more problems than it solves (the current procedure is IDs 400-499 are reserved for core plugins and 500-599 reserved for core template styles, and we don't reuse IDs of things that have been dropped from the core package so the template styles are "inflated" by the styles of the 2.5 and 3.x templates in relation to the 4.0 branch).

So we just need a new procedure to say that after 499 the next block reserved for core extensions is 1000-1499

?

Sure, but it still avoids addressing the underlying question of why we make the core extensions have known identifiers. What does it matter if the "Fields - Color" plugin has ID 464 or 2873? The "well I want to filter core versus third party" argument holds little value as it relates to the IDs because as a lot of us are aware that gets tossed out the window as soon as autoincrement gets reset on the extensions table, supporting evidence being every time there's a post about an update going bad when we have an INSERT INTO #__extensions statement in an update SQL file.

The only value that actually matters outside the install SQL is the files extension for core, ID 700. For the rest of it, because we're loading what is in essence a database backup means at install time we need to be able to create the correct relations for data (so menu item entries reference the correct component, or modules are assigned to the right menus, etc. etc.), but that's really it.

What does it matter if the "Fields - Color" plugin has ID 464 or 2873?
It doesnt
The "well I want to filter core versus third party" argument
No longer relevant as we have an array of core extensions

The problem/question as I see it is can we have an installer that uses fixed id for some extensions and increment for newer ones.

If we only have increment then we probably will hit issues with upgrades AND extensions coded to a specific ID.

Isn't it just easier and an option of least resistance to add a new reserved block

At least it would be good if we have a way to indentify a core extension, then it doesn't really matter what id the extension has.

we do

https://github.com/joomla/joomla-cms/blob/staging/libraries/src/Extension/ExtensionHelper.php#L20

We already have everything below 10000 reserved. So it's not a problem of adding more blocks, we "own" every 1-4 character number.

Even if you take away all this discussion about reserved blocks and whatnot, we still have the autoincrement problem to address. Production sites, for whatever reason (you can argue it's valid or not) get the table autoincrement reset. If these sites I'm looking at are correct, that means the first time it happens it's going to reset to 21, as that's the first gap in our ID sequence. So, new extensions are added after that reset happens, and we go to 3.9 and add com_privacy with ID 35. But because the autoincrement reset, another extension may have used that ID, and the update fails because we're trying to insert a duplicate primary key.

For all philosophical and practical matters, of the ~160 extensions in a core install, only one ID matters. 700, the one for core to work with the update system (since the update system is coupled to extensions). There is no reason com_privacy must always be extension ID 35. There is no reason plg_fields_color must always be extension ID 464. Because of how we write our update SQLs, we try to enforce that, and it leaves our update system entirely in the hands of whomever is maintaining those SQL databases to make sure that autoincrement NEVER resets. As it relates to generating and maintaining joomla.sql, we do need to work with a set of known identifiers to be able to build the initial data state correctly (and that's just because we're using a SQL file, if we tried to seed the database with our PHP APIs that would be even less of a concern).

In my PR #17537 I have already removed the hardcoded 700, surely this can be done in other places. We only need unique identifiers, the ID is one of them but there are also combinations of fields that achieve the same thing.

Closing, consensus seems to be "updates should always match joomla.sql even if it is known to break things".

Please test #22374. Very likely that I missed something, but the obvious cases are covered.