Page 1 of 1

Error 500 when adding menu items

Posted: Wed Oct 17, 2012 12:24 pm
by salsolo
Hi all :)
I just installed Joomla for the first time and it seems to work in most areas without a hitch. However I do have a couple of error that prevents me from using it.
When trying to add new menu items I get an error 500 - Error: 500 [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'except'.SQL=SELECT a.id, a.title, a.position, a.published, map.menuid,(SELECT COUNT(*) FROM joomla_modules_menu WHERE moduleid = a.id AND menuid < 0) AS except,ag.title AS access_title

Using newest version of Joomla 3 on Windows 2008R2 running IIS 7.5 with PHP 5.3.13 and a MS SQL 2008 R2 with SP2.

How do I troubleshoot this or does anyone have an idea what can be wrong?
My first thought is that this look a little fishy .. AS except,ag.title AS access_title ..is that part of the sql command correct?

Hope someone can help :)

Re: Error 500 when adding menu items

Posted: Wed Oct 17, 2012 3:19 pm
by sovainfo
That is certainly incorrect, hence the error.
It should read
a.published, map.menuid,
(CASE WHEN map2.menuid < 0 THEN map2.menuid ELSE NULL END) as `except`,
ag.title AS access_title

It looks like the mssql driver has a problem translating the CASE instruction.
See lines 746/765 of administrator/components.com_menus/models/item.php
Line 752 adds it to the query ( in a strange way!).

Re: Error 500 when adding menu items

Posted: Wed Oct 17, 2012 3:46 pm
by salsolo
Ok, that was helpful in regards of where the error happens thanks for that :)
I just upgraded the MSSQL for PHP driver to newest version and crossed my fingers ..but nope, the error is still there.
Quite weird why that section has issues with SQL driver when the rest of the site appears to be working fine ...but that means I still have no clue how to fix it.

Re: Error 500 when adding menu items

Posted: Wed Oct 17, 2012 4:37 pm
by sovainfo
You could try using the COUNT instead of MAP and use `except` (in backtics) as in line751
Don't forget to use the #__ as prefix!

Maybe find out whether except is a reserved word in mssql
select count(*) from joomla_modules_menu as except;

Re: Error 500 when adding menu items

Posted: Wed Oct 17, 2012 5:07 pm
by salsolo
except is indeed a reserved word in MS SQL so that must be where the problem arises .. but according to technical requirements Joomla should be fully supported? I haven't changed anything just made a clean install. I do understand I can change the specific code but if the problem is incompatibility with MS SQL then I cannot use this :( I risk building something that will be thrashed with the next update and I will probably find more code not working over time :(

Haven't anyone else installed Joomla on MS SQL?

Re: Error 500 when adding menu items

Posted: Wed Oct 17, 2012 6:16 pm
by sovainfo
Why would Joomla be different than any other piece of software? They all contain bugs. Request for moving this to 3,0 BUGS or create a trackeritem yourself. Maybe they think its worth fixing.

Re: Error 500 when adding menu items

Posted: Wed Oct 17, 2012 10:00 pm
by humvee
[Mod note: Moved from 3.0 General Forum to 3.0 Bug Reporting Forum;]
Is this a reported issue on the Bug Tracker?
Please review the CMS Bug Tracker to see if this issue has been identified. If so please quote the reference here.
If it has not then please raise a new issue with full details of the issue, the steps needed to reproduce it and your OS and server software versions.

Re: Error 500 when adding menu items

Posted: Thu Oct 18, 2012 7:23 am
by salsolo
sovainfo wrote:Why would Joomla be different than any other piece of software? They all contain bugs. Request for moving this to 3,0 BUGS or create a trackeritem yourself. Maybe they think its worth fixing.
You are absolutely right .. I guess I'm just surprised I ran into this bug as I think it's quite detrimental and something that I thought would easily be spottet. Thanks for your help - it was spot on.

I have just gone through CMS tracker and there seems to be no similar issues so I'll raise one now. Thanks again.

Re: Error 500 when adding menu items

Posted: Thu Oct 18, 2012 8:21 am
by sovainfo
Haven't been able to find the place where the CASE gets translated to a COUNT. Don't even know whether MSSQL would allow the quoted except. So, can't provide a fix other then changing the sql statement mentioned.

Understand your surprise, would make me feel uncomfortable to use as well. In the end you need to test what you need. Obviously creating a menuitem is something you need to create a site.

EDIT: Also went to the tracker and filtered on MSSQL. Found #28037 that suggests menuitem could be created.

Re: Error 500 when adding menu items

Posted: Sun Aug 18, 2013 10:47 pm
by sovainfo
Hope the provided solution works for you. Haven't found any issue in the tracker for it. Even the one I referred to is closed.

Could you provide some version info on your infra?

Re: Error 500 when adding menu items

Posted: Tue Oct 15, 2013 2:29 pm
by diana55
Hello!

This is my first post in the forum.

I'm having an issue where whenever I try to click on any of the links in the menu manager I see this error:

"500 - An error has occurred.
Unknown column 'a.ordering' in 'field list' SQL=SELECT a.id, a.menutype, a.title, a.alias, a.note, a.path, a.link, a.type, a.parent_id, a.level, a.published as apublished, a.component_id, a.ordering, a.checked_out, a.checked_out_time, a.browserNav, a.access, a.img, a.template_style_id, a.params, a.lft, a.rgt, a.home, a.language, a.client_id,CASE a.type WHEN 'component' THEN a.published+2*(e.enabled-1) WHEN 'url' THEN a.published+2 WHEN 'alias' THEN a.published+4 WHEN 'separator' THEN a.published+6 END AS published,l.title AS language_title, l.image as image,u.name AS editor,c.element AS componentname,ag.title AS access_level,e.name AS name FROM `cc3o6_menu` AS a LEFT JOIN `cc3o6_languages` AS l ON l.lang_code = a.language LEFT JOIN `cc3o6_users` AS u ON u.id = a.checked_out LEFT JOIN `cc3o6_extensions` AS c ON c.extension_id = a.component_id LEFT JOIN cc3o6_viewlevels AS ag ON ag.id = a.access LEFT JOIN cc3o6_extensions AS e ON e.extension_id = a.component_id WHERE a.id > 1 AND a.client_id = 0 AND a.published = 1 AND a.menutype = 'mainmenu' ORDER BY a.lft asc Unknown column 'a.ordering' in 'field list' SQL=SELECT a.id, a.menutype, a.title, a.alias, a.note, a.path, a.link, a.type, a.parent_id, a.level, a.published as apublished, a.component_id, a.ordering, a.checked_out, a.checked_out_time, a.browserNav, a.access, a.img, a.template_style_id, a.params, a.lft, a.rgt, a.home, a.language, a.client_id,CASE a.type WHEN 'component' THEN a.published+2*(e.enabled-1) WHEN 'url' THEN a.published+2 WHEN 'alias' THEN a.published+4 WHEN 'separator' THEN a.published+6 END AS published,l.title AS language_title, l.image as image,u.name AS editor,c.element AS componentname,ag.title AS access_level,e.name AS name FROM `cc3o6_menu` AS a LEFT JOIN `cc3o6_languages` AS l ON l.lang_code = a.language LEFT JOIN `cc3o6_users` AS u ON u.id = a.checked_out LEFT JOIN `cc3o6_extensions` AS c ON c.extension_id = a.component_id LEFT JOIN cc3o6_viewlevels AS ag ON ag.id = a.access LEFT JOIN cc3o6_extensions AS e ON e.extension_id = a.component_id WHERE a.id > 1 AND a.client_id = 0 AND a.published = 1 AND a.menutype = 'mainmenu' ORDER BY a.lft asc LIMIT 0, 20 Unknown column 'a.ordering' in 'field list' SQL=SELECT a.id, a.menutype, a.title, a.alias, a.note, a.path, a.link, a.type, a.parent_id, a.level, a.published as apublished, a.component_id, a.ordering, a.checked_out, a.checked_out_time, a.browserNav, a.access, a.img, a.template_style_id, a.params, a.lft, a.rgt, a.home, a.language, a.client_id,CASE a.type WHEN 'component' THEN a.published+2*(e.enabled-1) WHEN 'url' THEN a.published+2 WHEN 'alias' THEN a.published+4 WHEN 'separator' THEN a.published+6 END AS published,l.title AS language_title, l.image as image,u.name AS editor,c.element AS componentname,ag.title AS access_level,e.name AS name FROM `cc3o6_menu` AS a LEFT JOIN `cc3o6_languages` AS l ON l.lang_code = a.language LEFT JOIN `cc3o6_users` AS u ON u.id = a.checked_out LEFT JOIN `cc3o6_extensions` AS c ON c.extension_id = a.component_id LEFT JOIN cc3o6_viewlevels AS ag ON ag.id = a.access LEFT JOIN cc3o6_extensions AS e ON e.extension_id = a.component_id WHERE a.id > 1 AND a.client_id = 0 AND a.published = 1 AND a.menutype = 'mainmenu' ORDER BY a.lft asc"

I'm not sure what to do - please help. The menu manager was working wonderfully about a week ago.

It might be useful to know that about a week ago I try to test out joomla 3 locally but b/c I'm a fairly new at this I forgot to point the database to a different location other than the one I'm currently using with 2.5. I was able to restore the my joomla using a backup from a month ago. Everything else is working just fine.

What should I do? Is this something I'll be able to fix on my own?

All help is appreciated!

Re: Error 500 when adding menu items

Posted: Wed Feb 26, 2014 9:29 pm
by Shizzle512
I am having the same issue with the main menu in my dashboard throwing a 500 error when i go to add menu items or edit the menu's that are there. Can anyone explain how to fix this? :o