Add option to update all database tables/columns to current settings

[EricdeWaal]

I recently came upon a site upgraded over the years step by step all the way from J1.5 up to J5.
And apparently that has not always been done right, so many database tables/columns settings differed from the current J5 ones.

The 'Repair database' option does not correct that.
I propose to extend it's functionality by creating an ALTER TABLE query that takes all current settings from the install files and add the option to run that (with a bunch of warnings, obviously).

[richard67]

The problem is that after installation, the installation folder is removed, and so are the installation SQL files. Therefore the database checker does not have any information about how the complete schema should be, it only has the information from the update SQL scripts.

[EricdeWaal]

The problem is that after installation, the installation folder is removed

Yes that is true. So the ALTER TABLE query has to be generated and stored during installation, or, preferably, should already be present from the beginning.
A good location would be administrator/components/com_admin/sql/updates/mysql, this is where the current (very limited) 'Fix database' files are located.

[richard67]

A good location would be administrator/components/com_admin/sql/updates/mysql, this is where the current (very limited) 'Fix database' files are located.

This is absolutely the wrong place because there the files are located which are run by the Joomla Update component when doing an update, and which files are run is determined by the version in their name and the schema version in database. These files are not only used by the database checker, and by the way, just unpacking the files and then running the database checker is not a valid update method anymore since Joomla 3.4 or so. For updating the Joomla Update component has to be used, nothing else.

[EricdeWaal]

Hmm, yes. Although it would not be a bad idea though (IMHO) to run this during all updates. There must be hundreds of sites out there with outdated settings.
Most do no harm and go unnoticed, but for example in the _users table
lastvisitDate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
where the default should be NULL, for a new user who never logged in it became visible in the backend showing a crippled date rather that 'never'.

[richard67]

There must be hundreds of sites out there with outdated settings.

Do you have any evidence for that? As I said, the Joomla update components runs these update SQL scripts when they should be run. This also includes the user table and the null dates. If that will fail, the update will not succeed and you fallback to the backup which you have made before the update.

The situation which you describe can happen only when an update fails and people try to fix that instead or restoring the backup from before the update, possibly because they haven’t made a backup.

If there really were so many sites out which have what you describe, we would have a gazillion of issues and forum posts about that, it we haven’t.

I suggest you first investigate how updates work before making suggestions on how to change the database checker.

[EricdeWaal]

Do you have any evidence for that?

Actually, no. Just (and maybe a bit too enthousiastically) extrapolating from a site that has literally hundreds of these differences. It was a site that started before Joomla 1.5 and was updated all the way to Joomla 5. No idea how these updates were done and which problems they encountered.

[brianteeman]

Personaly I would look at this being a standalone application/script

[richard67]

Well, I would assume these site haven't been updated right, or when the update failed for some reason people have tried to repair them somehow.

Don't get me wrong: In principle I like the idea to have a check for the complete schema.

But it is not as easy as you think it might be when not being deep into the subject, and it would need a longer text to explain all the details why this has not bee implemented yet.

One reason is that not in all cases you want to have the same structure after an update as you would have with a new installation. There can be reasons to have differences. Both would work, but updating to the same structure like it would be with a new installation would not be possible or destroy data. We had that for example with the utf8mb4 conversion where the character set conversion could lead to the conversion of data types (.e.g longer text columns) when the database was converted during the update.

Another reason is that in some cases structure changes and data changes have to happen in a timely coordinated way, and just changing the structure by going through all tables in a different order than in a handwritten script would make problems.