SQL error when accessing sitemap after upgrade
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 18.104.22.168 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.
This part of the SQL causes the error:
(ppa1.pkID = )
If you check the table PpermissionKeys, do you find all these pkHandle's:
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?
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);
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...
and used the re-run script link. Whatever hadn't happened the first time must have sorted itself second time around.