richard, didn't test, just noticed something: shouldn't the updates be new sql files for 3.7.3 with todays date,
instead of changing existing sql update files that already run in 3.x to 3.7.0 site updates?

Added explanation why this PR fixes the existing schema updates and does not provide new ones, see end of section "Summary of Changes".

ok right sorry

@andrepereiradasilva Just when I was typing to add an explanation about that, I received the notification about your comment above. Seems I am getting old and so too slow ;-)

@andrepereiradasilva P.S.: The SQL in the old schema updates had to be corrected anyway or commented out, otherwise people again would run into a mess during the update.

Please be patient with testing instructions. I need time to build an update container with update package to current staging + this PR and a custom update URL for that.

Added testing instructions, updated description by link to German forum with manual method to fix menus before updating to 3.7.x without my patch.

PR is ready for test now. Don't be scared of the long description - I only want to be informing and precise.

@richard67 will test this Weekend.

@richard67 did you mean https://update.joomla.org/core/nightlies/next_patch_list.xml in "Step 2" of your Instructions?

Given URL gave Warning Update: :Collection: Could not parse.

@franz-wohlkoenig Yes, I meant the description for 3.7 on https://developer.joomla.org/nightly-builds.html, which tells to use https://update.joomla.org/core/nightlies/next_patch_list.xml. If that does not work for some reason, we have to find someone who can help with that.

@franz-wohlkoenig I just tested using custom URL https://update.joomla.org/core/nightlies/next_patch_list.xml for updating a clean 3.7.2 to 3.7.3-dev nightly build. I did not get any warning, but I have error loggging on in php so if there was some I should have seen it in the log file. Same with my custom URL from step 4, works without warning. Maybe there is something wrong with your testing system?

@richard67 i installed a clean 3.6.5 but haven't tested more. Will do it this Weekend.

Maybe there are problems with updating a 3.6.5 but not with updating a 3.7.2? I haven't tested it with 3.6.5 yet, to be honest.

I use 3.6.5 as wrote in Test Instructions. If another Version should be tested, please write and correct Instructions.

@franz-wohlkoenig
The test instructions are correct, but because I did not have a 3.6.5 and was lazy, I patched the database to fulfill the starting conditions regarding the menu types and moved the sql statements handled with this PR into some schema update starting with 3.7.3, so I could see my SQL has no syntax errors. But it is not a real life test and so maybe not acceptable for those who had the issues which this PR tries to correct. And so I did not propose this method for testing.

If I find time I will test also with 3.6.5 today, but if you did before then let me know about if there are problems with the update xmls because in this case it will also affect my custom URL for test step 4.

@richard67 will test on 3.6.5.

@mydutchtouch @jasonscottmontoya @bolando @schultz-it-solutions @shanenefdt @isherwood @inspry @affectit Since you all had the issue which this PR here wants to fix: Does anybody having a backup of the situation (Joomla files and database) before update to 3.7 which could be used for testing this PR here, and a testing site? If so, please test this PR.

good Idea @richard67 to let People test who reported Issue thie PR is for.

i've tested upgrading from 3.6.5 patchting the zip
with 2 menutype (main,menu) with 2 menus of menutype(main,menu)
after upgrading the component list is composed by on two component (com_postinstall,com_associations)

the step 3 don't do the right work in my scenario
AND (SELECT COUNT("id") FROM "#__menu_types" WHERE "menutype" = 'menu') = 0;
this is not true i have a menu_types item menu

i've used this one instead and it worked
UPDATE '#___menu' SET 'menutype' = 'main' WHERE 'menutype' = 'menu' AND 'client_id' = 1

@alikon Are your 2 menu types for the site (client_id 0) or the admin (client id 1)? As my testing instructions say they shall be for the site.

This PR shall not rename any existing, user-created menu ot menutype "menu", because that is not reserved. What @rdeutz wanted to do is to rename the menutype for the standard joomla admin menu from "menu" to "main" if it is for some reason "menu". The standard joomla admin menu does not have any record in the menu type table. So the 3rd step will only apply in that case, which is not cour case.
In your case, the "main" menu type shall be renamed and the "menu" menu type shall be untouched.
If you do not have a user-defined menu type and menu items and modules for a menu type "menu" but accidently some or all of the standard admin menu items have value for menutype = "menu" instead of "main", then the 3rd statement will have an effect. If you have both, i.e. a user-defined menu type "menu" with user-defined menu items but also the standard ones beloning to it, you are already in a mess which cannot be cleaned up by this PR.

For testing all these possibilities it would need to add a 2nd test with different starting conditions to this PR. But the focus was to avoid that the original statements kill user-defined menu types "main" and "menu" for the site, and this is achieved.

Before Joomla 3.7 it was not possible to create user-defined menut for the admin, correct me if I am wrong. So what we can have is user-defined menus of such menutype for the site, and the standard admin menu items using the wrong menu type. If we have a combination of both, i.e. we have a user-defined type "menu" for the site and also the admin menu items belonging to that, and those also have the wrong client id (that was what @rdeutz wanted to fix) then we have a problem we cannot easily solve.

But I could add a step between step 2 and step 3 to catch that case if the client ids are set correct, and with the now 3rd and then 4th step hit only those which are remaining and not having the right client id. I will think about that and provide a commit soon if useful.

But @alikon, let me know which was the client id of your 2 menu types "main" and "menu" so I understand your test result better .

@alikon I have made some corrections (or better, extensions) and updated the patched update package behind the custom URL for test step 4. Can you restore to the starting conditions and test again?

the weird thing is that in < 3.7.0 install\sql\mysql\joomla.sql we have:

INSERT INTO #__menu ('id', 'menutype', 'title', 'alias', 'note', 'path', 'link', 'type', 'published', 'parent_id', 'level', 'component_id', 'checked_out', 'checked_out_time', 'browserNav', 'access', 'img', 'template_style_id', 'params', 'lft', 'rgt', 'home', 'language', 'client_id') VALUES

### menutype = menu, client_id = 1 (admin)

(15, 'menu', 'com_newsfeeds_categories', 'Categories', '', 'News Feeds/Categories', 'index.php?option=com_categories&extension=com_newsfeeds', 'component', 0, 13, 2, 6, 0, '0000-00-00 00:00:00', 0, 0, 'class:newsfeeds-cat', 0, '', 26, 27, 0, '*', 1),
(16, 'menu', 'com_redirect', 'Redirect', '', 'Redirect', 'index.php?option=com_redirect', 'component', 0, 1, 1, 24, 0, '0000-00-00 00:00:00', 0, 0, 'class:redirect', 0, '', 29, 30, 0, '*', 1),
(17, 'menu', 'com_search', 'Basic Search', '', 'Basic Search', 'index.php?option=com_search', 'component', 0, 1, 1, 19, 0, '0000-00-00 00:00:00', 0, 0, 'class:search', 0, '', 31, 32, 0, '*', 1),
(18, 'menu', 'com_finder', 'Smart Search', '', 'Smart Search', 'index.php?option=com_finder', 'component', 0, 1, 1, 27, 0, '0000-00-00 00:00:00', 0, 0, 'class:finder', 0, '', 33, 34, 0, '*', 1),
(19, 'menu', 'com_joomlaupdate', 'Joomla! Update', '', 'Joomla! Update', 'index.php?option=com_joomlaupdate', 'component', 1, 1, 1, 28, 0, '0000-00-00 00:00:00', 0, 0, 'class:joomlaupdate', 0, '', 35, 36, 0, '*', 1),

### menutype = main, client_id = 1 (admin)

(20, 'main', 'com_tags', 'Tags', '', 'Tags', 'index.php?option=com_tags', 'component', 0, 1, 1, 29, 0, '0000-00-00 00:00:00', 0, 1, 'class:tags', 0, '', 37, 38, 0, '', 1),
(21, 'main', 'com_postinstall', 'Post-installation messages', '', 'Post-installation messages', 'index.php?option=com_postinstall', 'component', 0, 1, 1, 32, 0, '0000-00-00 00:00:00', 0, 1, 'class:postinstall', 0, '', 39, 40, 0, '*', 1),

so we have 2 menu items with menutypes IN ('main' 'menu')
both these are with client_id = 1 (admin)

furthermore we have only 1 item in the #__menu_types

INSERT INTO '#__menu_types' ('id', 'menutype', 'title', 'description') VALUES
(1, 'mainmenu', 'Main Menu', 'The main menu for the site');

now create the scenario

0. install 3.6.5

1. create 2 new menu items
   first one oldmain
   second one oldmenu

2. add a menuitem (ex single article)
   to oldmain and to oldmenu

3. assign a menu module to each menu

4. hack the db

UPDATE '#__menu_types' 
SET 'menutype'='main'
WHERE  'menutype'='oldmain';

UPDATE '#__menu_types' 
SET 'menutype'='menu'
WHERE  'menutype'='oldmenu';

UPDATE '#__menu' 
SET 'menutype'='main'
WHERE  'menutype'='oldmain';

UPDATE '#__menu' 
SET 'menutype`='menu'
WHERE  'menutype`='oldmenu';

UPDATE '#__modules'
SET 'params' = REPLACE('params','"menutype":"oldmain"','"menutype":"main"')
WHERE 'client_id' = 0;

UPDATE '#__modules'   
SET 'params' = REPLACE('params','"menutype":"oldmenu"','"menutype":"menu"')
WHERE 'client_id' = 0;

the scenario

#__menu_types in < 3.7.0 there is not client_id

#__menu client_id = 0 (frontend) menutypes IN ('main' 'menu')

#__menu client_id = 1 (admin) menutypes IN ('main' 'menu')

upgrade to 3.7.2

dowload the package Update from Joomla! 2.5, 3.0, 3.1, 3.2, 3.3, 3.4, 3.5 or 3.6 https://github.com/joomla/joomla-cms/releases/download/3.7.2/Joomla_3.7.2-Stable-Update_Package.zip

in the zip
change the original administrator/components/com_admin/sql/updates/mysql/3.7.0-2017-01-17.sql
with the one provided by this PR

and update using the Upload & Update

in these test condition with the current PR both

• (frontend) OLDmenu and OLDmain works as expected
• (admin) menus works as expected

I have tested this item ✅ successfully on 4451e20

@alikon Thanks for figuring that out, and for testing and the lots of work on it before. Finally it seems we both made it. I hope we find one more (successful) tester.

@alikon Hmm, I just ask myself why my SQL does not cause SQL errors on a 3.6.5 because in 3.6.5 the table #__menu_types does not have a client_id column, but I use it in my SQL.

@alikon Ah, ok, no problem, false alarm. The column is added with a schema update "3.7.0-2016-11-19.sql" which runs before those of this PR. All OK.

exactly

p.s.
@richard67 looking at this issue i've discovered this one #16625

@mydutchtouch @jasonscottmontoya @bolando @schultz-it-solutions @shanenefdt @isherwood @inspry @affectit @montellsp

Since you all had the one of the issues which this PR here wants to fix (#15671 , #15719 and #16643 ):

If you can, please test this PR here. If merged before release of 3.7.3, it will help people not to come into the same trouble as you had.

@andrepereiradasilva I don't know whom else to ask. Do you think you can find time to test this PR here? There is a good test instruction here: Alikon's comment.

The guys who reported the issues which this PR solves do not react.

And it has to be fixed as soon as possible, otherwise update to 3.7.3 will again cause issues and rumors in support forums.

And you are a reliable tester according to my experience.

I have tested this item ✅ successfully on 4451e20

@franz-wohlkoenig Plese set RTC after 2 successfull test.
@rdeutz Plese merge for 3.7.3

RTC after two successful tests.

What is that PR waiting for? It has 2 good tests and so is RTC, it fixes an ugly bug which caused much rumor in support forums, and it does not change any language strings ... so why is it not merged???

For some reasons the issue tracker shows 2 successful tests but it counts only 1 of them.

I need time to check the PR before I press the merge button

Well, if check is ok it should definitely go into 3.7.3.

Finally. Well, thanks to everybody who was involved in testing and discussing and improving.

Just one comment, it might be a non issue but what is when someone has menus named "main_to_be_deleted" or "main_is_reserved"? Shouldn't we add some hash at the end to be 100000% save?

theoretically yes. the "hash" could be a to_char of the result from "select count(id) + 1 from #__menu_type where menutype like 'main_is_reserved%'" (or so). But it is very unlikely that there is such problem.

I would simply add a "LKJDSF982342"

That does not keep someone to have a menu type with such name!
And the max length of the menutype column is as far as i remember 25 chars.

something dynamic has to be added to be sure, and the easiest things is to use the count of the existing recods which have the value like the common part in the value.

We can do this after RC, it is a bit paranoid :-)

well i have to check manuals of all 3 db types to do it the right way.
but as i say, "main_to_be_deleted_" concatenated with the count + 1 of the records in the menu type table where menutype like "main_to_be_deleted%" will be easy and safe.

ah, and the "main_to_be_deleted" we can avoid by deleting instead of renaming. i just was too scared of that. but it should be no problem to delete that menuy type record.

on weekend i will find time do do that improvement

Thanks!

@rdeutz I implemented the improvements which we discussed. See PR #16852 . Please check.

Hello everyone, I had a call from a client to do an update on their Joomla! 3.2 to the most recent version Joomla! 3.7.4, so when I did the menu under Components when down, to fix it I followed @alikon instructions at https://github.com/joomla/joomla-cms/pull/16577#issuecomment-307611339 and it worked.