Support

We're experiencing a persistent issue on a Joomla 4.4.13 site where the database schema seems to be stuck at version 4.4.4-2024-03-28, preventing Joomla core SQL updates from running correctly. We suspect a conflict involving Admin Tools and the #__user_keys table,

You do not actually have any problem.

The Database Version shown in the “Maintenance: Database” page is not the same thing as the software version. The interface even makes it explicit. The database schema version is when the database structure last changed; this is what is listed under Database version. The Joomla version is when the PHP files last changed; this is what is listed under Manifest Version. The database structure does NOT change with each software version.

The last Database Version for Joomla 4.4 at the time of this writing is 4.4.4-2024-03-28 even when the Joomla version (Manifest Version) is 4.4.12. You can confirm this by installing a new site using a fresh Joomla 4.4.12 package downloaded from download.joomla.org.

Also, to dispel any doubt, neither Admin Tools nor any other extension (core or third party) can affect Joomla Update from the moment the update ZIP file has been downloaded to the moment you are taken back to the Joomla backend. The entire update process takes place outside of Joomla, in its own standalone script. I know that for a fact because I wrote Joomla Update myself; it used to be part of Admin Tools.

 Suspected Cause: #__user_keys Table Conflict 

Admin Tools only reads and writes data to this table. It cannot change the structure of this table.

We NEVER change the structure of core Joomla table in our software. Changing datbase table structure, just like changing the content of core Joomla PHP files, is FORBIDDEN. It's called a “core hack” and only leads to trouble. I have been an outspoken advocate against core hacks ever since Joomla existed. I would never have our company release any software whatsoever which hacks core. I am not a hypocite, insane, or idiot.

Your problem is that the id column should have been AUTO_INCREMENT but it's currently not. This is something you did yourself. While you are at it, check if any other AUTO_INCREMENT columns have been squashed to non-incrementing columns. If you ever did a database export and import from XML files, yeah, that would definitely screw up your database. There's a reason I had to write Akeeba Backup (then-called JoomlaPack) back in '06 to transfer sites; it prevented many database transfer snafus like this.

ANyway. This table's history from its introduction in Joomla 3.2.0 to Joomla 4.4.12 is as follows:

• 3.2.0 Table is introduced
• 3.5.0 The series column is converted from VARCHAR(255) to a VARCHAR(191) due to the introduction of utf8mb4 support, and the restrictions in MySQL index size.
• 3.5.1 The user_id is converted from VARCHAR(255) to a VARCHAR(150) due to the introduction of utf8mb4 support, and the restrictions in MySQL index size.
• 3.8.4 The series_2 and series_3 columns are removed as they were unnecessary
• 4.0.0 The invalid column is dropped as it's no longer used

Secondary Problem: Admin Tools Critical Error => Error fixed: by removing admin tools plugin folder and reinstalling latest .zip file

When you updated Admin Tools the contents of the plugins/system/admintools folder were not updated. This is a Joomla bug which has existed since Joomla 1.5.5, but it happens very rarely. It's not specific to our software; it can happen with any extension. The correct solution is install the update ZIP package twice in a row. Nobody knows why it happens, exactly because it happens so rarely that it's practically irreproducible. We know it exists, we know how to work around its effects, but it remains unsolved for the past 15 years.

Please note that the change of the namespace from Joomla\Plugin\System\AdminTools to Akeeba\Plugin\System\AdminTools took in the 7.4.0 Admin Tools release in September of 2023. Are you telling me that you had not updated Admin Tools in nearly two years?!

I regularly upgrade the plugin (every few weeks at least) - however, the DB-Version is stuck at 7.4.5.

The database and software versions are not the same. The database version for Admin Tools 7.7.0 is 7.4.5-20231214 because the last time we modified the structure of the database tables of the extension was ahead of version 7.4.5 back in December 2023.

TIP! You can look at the update SQL of any extension to see what is the latest database version. For Admin Tools that would be administrator/components/com_admintools/sql/updates/mysql. If you are looking inside the ZIP package, extract the package, then extract the com_admintools.zip you found in the package and look into the extracted folder backend/sql/updates/mysql. This applies to any Joomla extension. For Joomla itself, the update SQL files are in administrator/componetns/com_admin/sql/updates/mysql. This way you can verify the correct DB version is used by just looking at the files of the extension / Joomla distribution ZIP file. Simple, isn't it? :) I believe this is also covered in the Joomla developer's manual, but I can't vouch for it. I know it was part of the old developers' documentation, I am not sure what has been transferred to the new Joomla Manual site.

My comment about you not having updated in two years came as a response to the error message which was referencing the namespace Joomla\Plugin\System\AdminTools which was replaced in September 2023. Therefore, the code that threw this error was from between March 2021 (when that namespace was first used) to September 2023 (when that namespace stopped being used). It follows that you were updating from a version of Admin Tools that is anywhere between 22 months and 4 years old.

In other words, I did not look at versions; I based my reply on the fact that your error message includes a reference to a namespace that was only used for a short period of time, between March 2021 and September 2023.

The bug did only happen on my local XAMPP, in PROD the error did not occur. :)

I assume you mean the update issue in Joomla. It can happen anywhere. I've seen it on all sorts of local, testing, development, and production servers. If it was as easy as using a local server we'd have reproduced it and fixed it 15 years ago ;)

Autoincrement was added later to the table (not in the create statement itself).

Unfortunately, you are wrong. The AUTO_INCREMENT property has been there ever since this table was first created on October 10th, 2013.

I also saw your insightful book about the com_api solution, which was probably already introduced before the J4 Core APIs / Webservices some time ago:

I have explicitly stated that the book was written after Joomla 4 was released (in fact, a year after). It's based on my experience updating all of our software for Joomla 4. It tells you how to adapt your Joomla 3 component to work on Joomla 4 and later versions, as well as introduce you to the new features in Joomla 4. One of these features is Joomla's API application.

There is no such thing as "com_api", nor do I make any such claim. The URL slug for the book's chapter is short for "Chapter 2. Components. Section: API application".

If you read the entire section you will see that I take you by the hand and guide you through creating the api section of your component, and the webservices plugin you need to route API requests to your component.

For what it's worth, the Joomla API application was written mostly by George Wilson, and he had me implement software for it to make sure that it fits the needs of third party developers before Joomla 4 was released. I provided feedback for it, and contributed the user authentication scheme which is still in use (George only had a temporary authentication scheme using a raw username and password he knew was wrong and shouldn't be used, but had run out of time to implement something better, and I happened to already have the authentication solution ready as part of another project I was working on at the time). Fun stuff.

Would you consider the Joomla Core API the right approach for using APIs to external applications today or any other approach?

Yes, absolutely. In fact, it's the ONLY approach you should consider for this purpose!

The authentication token is necessary to tell Joomla which user is accessing the API. It's the equivalent of a username and password.

If you want to make your API accessible to guest users, i.e. when nobody is logged in, what you are trying to implement is unauthenticated (public) access.

As I mentioned in the book, that's possible, and it's up to the webservices plugin. I also explicitly stated that this is how the media manager (com_media) does it. I also linked to https://www.dionysopoulos.me/book/com-api-plugin.html where I talk about it. Look at $publicGets. Set it to true to allow the GET request (and ONLY the GET request!) to work without authentication.

Also note that I explicitly state you will have to override the displayItem and displayList methods in the respective controller to make sure that you return data to unauthenticated users, and that this data is appropriately sanitized.

Also, when creating the route you will need to set ['public'=>true] in $defaults if you want your route to be accessible to guest users. The caveat is that only guest users will be able to access the route.

So, yes, what you want is possible but you need to make a few changes in both the plugin and the component to get there.

Finally, if you really want to authenticate users with their username and password, instead of a token, it is possible and Joomla does provide an API authentication plugin to do that. However, I would strongly advise AGAINST using it as it's insecure; it sends the username and password as a base64-encoded string which is as good as plain-text. It also doesn't (or at least shouldn't) work with user accounts that have MFA enabled. The API token is a much better solution, and something that is used throughout the industry; Joomla didn't invent it. Actually, since I wrote that code myself, I can tell you that it's 100% inspired by API best practices throughout the industry and a lot of thinking went behind it to make sure it's not going to be something that can easily be subverted even if you have a data breach; it requires both file and database data to work.