Forums

SQL error when accessing sitemap after upgrade

Permalink
Hello

Could someone with a better understanding of C5's mechanics point me in the direction as to what might be causing this error.

When logged in as my named user account and visiting the sitemap I get the following error

mysqlt error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) > 0 or (p1.cPointerExternalLink !='' AND p1.cPointerExternalLink IS NOT NU' at line 2] in EXECUTE("select p1.cID, pt.ctHandle from Pages p1 left join Pages p2 on (p1.cPointerID = p2.cID) left join PagePaths on (PagePaths.cID = p1.cID and PagePaths.ppIsCanonical = 1) left join PageSearchIndex psi on (psi.cID = if(p2.cID is null, p1.cID, p2.cID)) inner join CollectionVersions cv on (cv.cID = if(p2.cID is null, p1.cID, p2.cID) and cvID = (select max(cvID) from CollectionVersions where cID = cv.cID)) left join PageTypes pt on pt.ctID = cv.ctID inner join Collections c on (c.cID = if(p2.cID is null, p1.cID, p2.cID)) left join CollectionSearchIndexAttributes on (CollectionSearchIndexAttributes.cID = if (p2.cID is null, p1.cID, p2.cID)) where 1=1 and p1.cParentID = '0' and (p1.cIsTemplate = 0 or p2.cIsTemplate = 0) and ((select count(cID) from PagePermissionAssignments ppa1 inner join PermissionAccessList pa1 on ppa1.paID = pa1.paID where ppa1.cID = if(p2.cID is null, p1.cInheritPermissionsFromCID, p2.cInheritPermissionsFromCID) and pa1.accessType = 10 and pa1.pdID in (0) and pa1.peID in (2,3,7,4) and (if(pa1.peID = 4 and p1.uID <>5, false, true)) and (ppa1.pkID = )) > 0 or (p1.cPointerExternalLink !='' AND p1.cPointerExternalLink IS NOT NULL)) and ((select count(cID) from PagePermissionAssignments ppaExclude inner join PermissionAccessList paExclude on ppaExclude.paID = paExclude.paID where ppaExclude.cID = if(p2.cID is null, p1.cInheritPermissionsFromCID, p2.cInheritPermissionsFromCID) and accessType = -1 and pdID in (0) and paExclude.peID in (2,3,7,4) and (if(paExclude.peID = 4 and p1.uID <>5, false, true)) and (ppaExclude.pkID = )) = 0) and p1.cIsActive = '1' and (p1.cIsSystemPage = 0 or p2.cIsSystemPage = 0) ")


If I log in as admin I don't see the error. This lead me to conclude the error might be permission related. I tried removing all my user accounts, groups etc and resetting all my page permissions etc. I have rebuilt my search index, cleared cache etc and am still getting nowhere.

The error appeared after upgrading to 5.6.2.1 so I'm guessing something changed but I can't see from the error text what the cause might be.

Any help would be appreciated as always.

Thanks
digirunt
 
Remo replied on at Permalink Reply
Remo
It looks like a part of the permission system is missing.
This part of the SQL causes the error: 
(ppa1.pkID = )


If you check the table PpermissionKeys, do you find all these pkHandle's:

access_group_search
access_page_defaults
access_sitemap
access_task_permissions
access_user_search
activate_user
add_block
add_block_to_area
add_file
add_layout_to_area
add_stack
add_stack_to_area
add_subpage
approve_basic_workflow_action
approve_page_versions
assign_user_groups
backup
copy_file
copy_file_set_files
delete_area_contents
delete_block
delete_file
delete_file_set
delete_file_set_files
delete_page
delete_page_versions
delete_user
edit_area_contents
edit_area_design
edit_area_permissions
edit_block
edit_block_custom_template
edit_block_design
edit_block_permissions
edit_file_contents
edit_file_permissions
edit_file_properties
edit_file_set_file_contents
edit_file_set_file_properties
edit_file_set_permissions
edit_groups
edit_page_contents
edit_page_permissions
edit_page_properties
edit_page_speed_settings
edit_page_theme
edit_page_type
edit_user_properties
empty_trash
install_packages
move_or_copy_page
notify_on_basic_workflow_approve
notify_on_basic_workflow_deny
notify_on_basic_workflow_entry
preview_page_as_user
schedule_area_contents_guest_access
schedule_guest_access
schedule_page_contents_guest_access
search_file_set
sudo
uninstall_packages
view_area
view_block
view_file
view_file_in_file_manager
view_file_set_file
view_newsflow
view_page
view_page_in_sitemap
view_page_versions
view_user_attributes
digirunt replied on at Permalink Reply
digirunt
Thanks Remo

it looks like "view_page_in_sitemap" is missing. I've checked some of my other installations and they don't appear to have this key either?

If it is likely this is causing the problem please could you post the field values for that key so I can add it manually.

Any ideas as to why this is missing?

Thanks
Remo replied on at Permalink Reply
Remo
I assume there was an upgrade problem.

I'm not sure if adding that entry will fix your problem, but here's the missing entry from my system:
INSERT INTO `PermissionKeys` VALUES (3, 'view_page_in_sitemap', 'View Page in Sitemap', 0, 0, 'Controls whether a user can see a page in the sitemap or intelligent search.', 1, 0);
tabercreative replied on at Permalink Reply 2 Attachments
tabercreative
Thank you sooooo much for this Remo!! This info solved it for me.

I inserted a new line (72 in my case) in the table for view_page_in_sitemap. Then added "Administrators" in the permissions for the Homepage and now I can access the sitemap again.

So it's a combo of your solution and this one:https://www.concrete5.org/community/forums/usage/user-cant-view-site...
digirunt replied on at Permalink Reply
digirunt
It turns out it was an upgrade issue. I re-visited the link...

/index.php/tools/required/upgrade?source=dashboard_update

and used the re-run script link. Whatever hadn't happened the first time must have sorted itself second time around.

Problem solved.