@alikon Ready for test.

@SharkyKZ @Quy @jwaisner and whoever else has time: Please test ... the more testers for MySQL and PostgreSQL. the better. I really want to be sure that it doesn't break anything.

Why are you changing existing sql update files? they should be immutable

@brianteeman No they aren't. We have to do that in the way I did in order not to let the database checker/fixer report errors. I had explained that in past, and I also explained in a comment here: #17860 (comment). Trust me, I know what I'm doing here.

@rdeutz @wilsonge @HLeithner If one of you has time: Please confirm my comment above. It has been explained to Brian several times but he doesn't want to understand it.

You are making the changes required in this file 3.9.16-2020-02-15.sql so why would you need to make them in a previous file

You are making the changes required in this file 3.9.16-2020-02-15.sql so why would you need to make them in a previous file

@brianteeman Because the database checker/fixer will report errors for these old files if I don't change them. Then you fix it, and then in the next step it will repost errors for the same columns of the new update sql, and if you fix them it will again report errors for the old script and so on and so on, so you end in an endless fixing loop.

P.S.: The checker goes through all present update sql files from old version to new version and checks if column definition in database matches what he finds in that particular sql file. It always starts e.g. with the 2.5 file, if present. So you can do a change for a column only in 1 file. If you later have to change the same column in a later file, you either have to comment out the change in the old file, or adapt it if it is in a create table statement.

@brianteeman P.P.S.: If we would throw away the (almost) useless db ckecker and fixer it would all as you say.

I am sure you are wrong because the only time these changes will be seen is after you have upgraded the site and therefore already have the correct database.

Anyway this PR cannot be truly tested without testing an upgrade using the joomlaupdate component

@brianteeman You are wrong, I think. Asfar as I know, the db checker "Extensions -> Manage -> Database" checks all the old files again, regardless if after new installation or after an update.

P.S.: But of course I can build an update container and provide a custom update URL to check updating with the Joomla Update Component. Will do so in a few minutes.

I have tested this item ✅ successfully on 993254e

New install works with no issues and database is up to date. All table updating functions work as well.

Update function works on an existent install then applying the PR. All table updating functions work as well.

This was tested on MySQL database.

@jwaisner Thanks for testing. I have added a third test with Joomla Update Component. Could you test that, too, and report back the result?

@brianteeman I have added a 3rd test for updating the with the Joomla Update component, as you suggested. Regarding what you show in your video: Maybe something is wrong on MySQL then with the db checker? On PostgreSQL you can see in my 2nd test that it is necessary to update the old scripts. Otherwise if not updated you would end in the endless fixer loop as I described before.

Steps to reproduce why the old updates do not need to be changed

1. Add just this file 3.9.16-2020-02-15.sql
2. Go to DB checker
3. It should look like this
   
4. Apply db fix
5. It will now look like this
   

So please show me why you need to change the old files

@brianteeman Do following: On a clean staging apply from this PR only the changed files joomla.sql for nex installations and only add the new update SQL files 3.9.16-2020-02-15.sql. Then make a new installation. Then login to backend and check "Extensions -> Manage -> Database". It will show what happens when making a new installation with the joomla.sql modified by this PR and not having the old sql update files modified.

So I can now confirm the issue on new installs only and NOT on upgrades. To me thats an issue in the installer that should be fixed. You shouldn't even have the update sql files on a new install.

So I can now confirm the issue on new installs only and NOT on upgrades. To me thats an issue in the installer that should be fixed. You shouldn't even have the update sql files on a new install.

Agree, but thats a larger, conceptional issue not touched by this PR. I agree with you about how it should be. And I hope I ever have the time and the courage to fix that.

@brianteeman and @jwaisner Please waith with testing the 3rd test with the update component .. I have to fix the xml so Joomla Update finds an update.

I stand by my comment that old update sql files should be immutable. The fact you are changing them is because of a bug elsewhere. Fix the problem, don't use a band aid.

@jwaisner @brianteeman Update custom URL XML is fixed, you can text updating from any 3.9 version, but not from current staging or nightly because these already have same version as my update container. But you can test with any 3.9.x

I stand by my comment that old update sql files should be immutable. The fact you are changing them is because of a bug elsewhere. Fix the problem, don't use a band aid.

@brianteeman I promise you now to fix that during our both lifetime, but it's not a small thing so it really needs a bit time, and the issue solved with this PR (and the preceeding one) has to be fixed before.

I have tested this item ✅ successfully on 993254e

Tested only MySQL.

Following exactly the test instructions, everything works as described.

Since our db fix can't handle it correctly we don't have another option.

I have tested this item ✅ successfully on 993254e

tested on postgresql 11.5

RTC

the main point here is:

• to remove wrong DDL a field type like TEXT cannot have default

plus we need to do the same on J4 as an example
https://github.com/joomla/joomla-cms/blob/4.0-dev/installation/sql/mysql/joomla.sql#L747

@brianteeman One more explanation for you, not because I want to argue who is right or wrong but because I want you and other readers understand. Regarding your gif: The db checker checks only database stuff which appears in any update sql script. Furthermore, for create table statements or create index it doesn't check the table or index details, it checks only if the table or index with that name exists or not. That means when you change in db the type of a table column which doesn't appear in any update sql script in any "alter table ... modify ..." or "alter table ... change ..." statement, the db checker will not notice it. I think this is what your animated gif shows. This just for your and others' understanding.

plus we need to do the same on J4 as an example
https://github.com/joomla/joomla-cms/blob/4.0-dev/installation/sql/mysql/joomla.sql#L747

Yes, this here has to be merged up into 3.10-dev and 4.0-dev after merged into staging, and it might have to be completed in J4 then by handling columns having been added in J4. I will keep an eye on it and if no bus hits me before will do that work then.

Important for maintainers: When merging, please don't do a squash commit. I don't want the commit history and so the credits for the author of the original PR being lost.

Last commit was only a grammar change in a comment, so reviews and tests and RTC should still be valid.

I think we all agree the db fixer tool needs to be adapted to an approach that makes it immutable. But given we don't have that agree modification the old files is an unfortunate reality :(