@zero-24 Could you can prepare an update server containing 3.7.0 plus this PR?

I have tested this item ✅ successfully on 4b98d06

Using update server http://jamp.joomla.pl/update-joomla-server/list.xml
Thanks @trzepiz for help with update server.

@brianteeman @franz-wohlkoenig Please test

This has not fixed my issue. Post upgrade I still have a number of menu items unpublished that were published before the upgrade and I am unable to publish them. I am also unable to publish any new menu item I add. I'm afraid I don't understand the code well enough to figure out what is going on (although I'm working on it!). Is there anything I can provide that would help debug this issue?

@prh47bridge Show result of this query

SELECT  `published` FROM  `#__menu` WHERE `alias` = 'root';

where #__ is table prefix.

You have to execute the below sql query.
For example put this code in your front end active template on the top of file index.php

<?php JFactory::getDbo()->setQuery("UPDATE `#__menu` SET `published` = 1 WHERE `alias` = 'root'")->execute(); die("DONE"); ?>

And refresh home page to execute that code. You should see text "DONE". Revert changes. Then try again to publish you menu item in backend.

Result of query is that published = 1. I think I've got a different issue.

You can rebuild menu table by click on "Rebuild" button at 'administrator/index.php?option=com_menus&view=menus'

I'd already done that. I have, however, found the issue. Putting some messages in libraries/joomla/tables/nested.php revealed that Joomla thought a parent was unpublished. Using the SQL query the code uses identified that there is a rogue item in my menus table with title com_banners_categories, alias Categories, path joomla-license/Categories, link index.php?option=com_categories&extension=com_banners, published 0, parent_id 2, level 2, component_id 6, access 0, img class:banners-cat, lft 448, rgt 935 and client_id 1. There appear to be 8 items that are similar to this and are clearly different to any menu items I have set up. The lft and rgt values are clearly what is causing the problem on this item - they are more sensible on the other items, with rgt being lft+1 for all but one of them. JTableNested clearly thinks the rogue item is a parent to my menu items although it isn't in the hierarchy shown in the back end.

With 3.6.5 I can't see this item in the back end at all. With 3.7 I can see it if I select Administrator rather than Site menus. However, if I delete it a pile of menu items I need are deleted with it, so I'm not going to try that on the live site!

Looking at another site I manage I can see a similar set of menu items but the lft and rgt values make more sense. The one I have highlighted plus possibly one other item on the site where I have the problem have clearly been corrupted at some point.

The query used by 3.6.5 to identify unpublished parent items is slightly different to the one used in 3.7. This appears to be what makes the difference in behaviour.

As a developer, my view is that it would be risky to assume that my site is the only one that has this problem so it would be good if JTableNested could cope with rogue items like this, especially since it seemed to be ok in 3.6.5. It would also be good if there was a way to fix the underlying problem.

Any ideas as to what I should do about this apparently corrupted item?

Just to add a little...

It looks like this menu item should have menutype set to menu. It is actually set to mainmenu. On my other site this item is shown as published as are the other similar items. On this site they are all set as unpublished. I've no idea which is correct (or if it matters). It looks to me like this menu item has at some point moved to the wrong menu and lft and rgt have been set to make it become the parent of a whole load of items. I have no idea when or how this happened. The things I can see suggest it may have been when I moved a load of items from mainmenu to another menu but I may be wrong about that.

Looking at it further, I'm not convinced the different query is the issue as the only real difference appears to be that the old query selected a value from the row (whichever column is in _tbl_key) whereas the new query simply selects 1. Maybe the different way of getting the result? All I can say for sure at the moment is that this rogue menu item does not cause any problems with 3.6.5 but it clearly upsets 3.7.

It may be related to #13619

@csthomas #13619 breaks sites only with menutype = main or menutype = menu used as frontend menu.

My advice is rebuild menu before update to 3.7.

Tried rebuilding mainmenu. It makes no difference. This rogue item is untouched.

@prh47bridge Export rogue menuitems in sql format if you cen.

@prh47bridge Please copy #15806 (comment) to issue #15938 and attach requested sql file there.

@franz-wohlkoenig Please test

@wojsmol i've read your wish 7 Days ago but i don't understand test instructions, so i can't test.

Test instruction could be:

1. Install J3.6.5
2. In phpmyadmin change row with alias="root" to published="0" (= manually break menu)
3. If you go to frontend then menu should still work as expected.
4. Update to J3.7 - official channel
5. Now you see that all menu has been unpublished and you can not publish them.

Go back to point 1 and go forward.
At point 4: Update to J3.7 from channel that @wojsmol mentioned.
5. Now everything should work as expected.

@wjsmol Done as requested

@csthomas @wojsmol will test Today, thanks for Instructions.

@csthomas at Step 2: in #_menu how to change row with alias="root" to published="0"?:

@franz-wohlkoenig You are i Structure view of table, go to Browse view and edit record there.

@wojsmol thanks for Info, one step further.

After Update to 3.7.0 (Step 4):

After "Database > Fix" see Layout of 3.7.0

Didn't get unpublished Menues:

If i manually unpublished a Menu, i can't publishe it again. If thats the issue, i can test like decribed:

Go back to point 1 and go forward.
At point 4: Update to J3.7 from channel that @wojsmol mentioned.
5. Now everything should work as expected.

@franz-wohlkoenig This scrinshots are for second run where you set update server to http://jamp.joomla.pl/update-joomla-server/list.xml?

@franz-wohlkoenig Because of error after "After Update to 3.7.0 (Step 4):" your joomla did not unpublish them (menu items) on installation process.
But you can not publish menu items, so test match my requirements.

On another J3.6.5 (with root="alias" unpublished) which will be updated to http://jamp.joomla.pl/update-joomla-server/list.xml you can not have any error or warning on installation process.

@wojsmol Screenshots at Comment are from first Run.
@csthomas so i can test second rund Run?

yes

@csthomas First run dont show the error, so @franz-wohlkoenig wrongily edits the database.

@franz-wohlkoenig In a fresh Joomla! 3.6.5 place this in top of active fronend template

<?php JFactory::getDbo()->setQuery("UPDATE `#__menu` SET `published` = '-1' WHERE `alias` = 'root'")->execute(); die("DONE"); ?>

and refresh the page. You see DONE? if yes run first run
Besicly this replaces step 2 from @csthomas instruction in both runs.

@csthomas 2nd Run got same Warning like in Comment.
@wojsmol will test your Comment above.

@franz-wohlkoenig Maily check published state for menuitems

@wojsmol

• clean install of 3.6.5 done
• copy <?php JFactory::getDbo()->setQuery("UPDATE#__menuSETpublished= '-1' WHEREalias= 'root'")->execute(); die("DONE"); ?> in index.php of protostar which guess you mean by "place this in top of active fronend template"
  
• in Frontend got:
  

Preserve whites paces in:

UPDATE `#__menu` SET `published` = '-2' WHERE `alias` = 'root'

@csthomas thanks for hint, didn't get lost white Space > get "DONE" on blank Window. So i run first Run again without modified Template..

@franz-wohlkoenig See #15806 (comment) point 2 and compare with https://gist.githubusercontent.com/wojsmol/4e7ab2671613b9b46dad514e1408dee6/raw/4fe568690b714c0d510a82946f396c19a0ffacf3/gistfile1.txt In your comment there is no space after UPDATE as it was written by @csthomas #15806 (comment)

IMO, This should not be in an update script that runs once. As of my understanding it is yet unknown to us that what exactly caused them to be unpublished.

I suggest having a Health Checkup feature in maybe com_admin. Which will perform a sanity check against current installed Joomla version. We can also allow plugins to inject extensions' health test.

This may be a superset to the existing database check feature.

Pardon my ignorance if such thing already exist.

@izharaazmi That would be a very good option, but for now this PR must be enough for us.

I have tested this item ✅ successfully on 4b98d06

Using Instructions all works well. After Update root ist set back on "1".

RTC after two successful tests.