SQL error when editing or removing role "Something has gone wrong."

websavers · March 2020

Using Vanilla Forum 3.3 updated a few months ago from 2.5.x.

Everything else about the forum is working flawlessly. When visiting /dashboard/role and then either selecting the pencil or the trashcan icon beside any role, we get "Something has gone wrong." The PHP error in the back-end looks like this:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' p.Name as `Name` from `c` `c` left join p on c. = p.ID'

It's pretty obvious that the issue is with c. not specifying a field like c.ID or c.NAME, however because this error appears to be using obfuscated or simplified table naming, I have no idea how to troubleshoot this further because 'c' is not an actual table in the DB. Is there a way to get the real SQL query with the real table name?

In a search of this forum, I have found three other instances of this error. In two of the three the OP wound up reinstalling fresh, which I cannot do. In the final case, a commenter indicated that editing the database fixed it, but did not specify what they edited.

Does anyone have an idea how I can determine why it's failing to get the right field name to use in the query? Or perhaps something to look for in the permissions or roles tables that might be amiss?

Thanks in advance for any help.

Jordan @ ws

Kaspar · March 2020

"reinstalling fresh, which I cannot do"

Install a test setup and try it in that, that you way you find if it happens there aswell.

If it works then database feom your live and test again.

That way you figure out whether it is server setup, forum install or database.

R_J · March 2020

I assume you already have run /utility/structure?

There has been a problem with the role table in some updates in Vanilla 2.x

Make sure your role table has a "Type" column and that it is filled (guest, unconfirmed, applicant, member, administrator, moderator). That's how a GDN_Role table looks in a clean install:

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `GDN_Role`;
CREATE TABLE `GDN_Role` (
  `RoleID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Description` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Type` enum('guest','unconfirmed','applicant','member','moderator','administrator') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Sort` int(11) DEFAULT NULL,
  `Deletable` tinyint(4) NOT NULL DEFAULT '1',
  `CanSession` tinyint(4) NOT NULL DEFAULT '1',
  `PersonalInfo` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`RoleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `GDN_Role` (`RoleID`, `Name`, `Description`, `Type`, `Sort`, `Deletable`, `CanSession`, `PersonalInfo`) VALUES
(2,	'Guest',	'Guests can only view content. Anyone browsing the site who is not signed in is considered to be a \"Guest\".',	'guest',	1,	0,	0,	0),
(3,	'Unconfirmed',	'Users must confirm their emails before becoming full members. They get assigned to this role.',	'unconfirmed',	2,	0,	1,	0),
(4,	'Applicant',	'Users who have applied for membership, but have not yet been accepted. They have the same permissions as guests.',	'applicant',	3,	0,	1,	0),
(8,	'Member',	'Members can participate in discussions.',	'member',	4,	1,	1,	0),
(16,	'Administrator',	'Administrators have permission to do anything.',	'administrator',	6,	1,	1,	0),
(32,	'Moderator',	'Moderators have permission to edit most content.',	'moderator',	5,	1,	1,	0);

If your role table looks different, than this is the problem.

If you change anything in the GDN_Role table, please ensure that GDN_UserRole still contains old values and that this table must be corrected, too!

Quite long ago, there has been an additional problem with some plugins defining new permissions in a wrong way. Check the GDN_Permission table that there is no column with a silly name like "1".

websavers · March 2020

Hey @R_J

Thanks so much for the help! Yep I utility/structure and utility/update -- did not help with this particular issue.

`RoleID`, `Name`, `Description`, `Type`, `Sort`, `Deletable`, `CanSession`, `PersonalInfo`

All of those fields exist in the Role table, however some roles have that field set to NULL. I've set them manually (most to "Member") to ensure all Roles have a Type set.
There was a RoleID 1 with Name "Banned" which I removed as it's not in the defaults you showed.
I adjusted the configuration for each default role so they match exactly what's shown in your INSERT INTO `GDN_Role statement
I checked the UserRole table and found no entries there with RoleID set to 1 (the one I removed: Banned).

Unfortunately I'm still getting the same error when attempting to edit any of the roles!

Does the table prefix need to be GDN_ for this to work? We don't have any table prefixes. The tables are literally just Role and UserRole.

Quite long ago, there has been an additional problem with some plugins defining new permissions in a wrong way. Check the GDN_Permission table that there is no column with a silly name like "1".

I checked for this too because one of the other forum threads on this topic lists this issue as a potential problem, however I found no such columns in the Permission table. My fields in that table are:

PermissionID RoleID JunctionTable JunctionColumn JunctionID Garden.Settings.Manage Garden.Settings.View Garden.SignIn.Allow Garden.Applicants.Manage Garden.Users.Add Garden.Users.Edit Garden.Users.Delete Garden.Users.Approve Garden.Activity.Delete Garden.Activity.View Garden.Profiles.View Garden.Profiles.Edit Garden.Moderation.Manage Garden.Curation.Manage Garden.PersonalInfo.View Garden.AdvancedNotifications.Allow Garden.Community.Manage Garden.Tokens.Add Garden.Uploads.Add Vanilla.Discussions.View Vanilla.Discussions.Add Vanilla.Discussions.Edit Vanilla.Discussions.Announce Vanilla.Discussions.Sink Vanilla.Discussions.Close Vanilla.Discussions.Delete Vanilla.Comments.Add Vanilla.Comments.Edit Vanilla.Comments.Delete Garden.Email.View Conversations.Moderation.Manage Conversations.Conversations.Add Vanilla.Approval.Require Vanilla.Comments.Me Plugins.Debugger.View Plugins.Debugger.Manage Vanilla.Tagging.Add

R_J · March 2020

Now I have taken a closer look at the sql above. What looks strange to me is "c.ID"

ID columns in vanilla are not simply called ID, they are named UserID, CommentID etc - TableID is to be expected. What plugins do you have enabled?

websavers · March 2020

Hey @R_J

Thanks so much for the help! Yep I utility/structure and utility/update -- did not help with this particular issue.

`RoleID`, `Name`, `Description`, `Type`, `Sort`, `Deletable`, `CanSession`, `PersonalInfo`

All of those fields exist in the Role table, however some roles have that field set to NULL. I've set them manually (most to "Member") to ensure all Roles have a Type set.
There was a RoleID 1 with Name "Banned" which I removed as it's not in the defaults you showed.
I adjusted the configuration for each default role so they match exactly what's shown in your INSERT INTO `GDN_Role statement
I checked the UserRole table and found no entries there with RoleID set to 1 (the one I removed: Banned).

Unfortunately I'm still getting the same error when attempting to edit any of the roles!

Does the table prefix need to be GDN_ for this to work? We don't have any table prefixes. The tables are literally just Role and UserRole.

Quite long ago, there has been an additional problem with some plugins defining new permissions in a wrong way. Check the GDN_Permission table that there is no column with a silly name like "1".

I checked for this too because one of the other forum threads on this topic lists this issue as a potential problem, however I found no such columns in the Permission table. My fields in that table are:

PermissionID RoleID JunctionTable JunctionColumn JunctionID Garden.Settings.Manage Garden.Settings.View Garden.SignIn.Allow Garden.Applicants.Manage Garden.Users.Add Garden.Users.Edit Garden.Users.Delete Garden.Users.Approve Garden.Activity.Delete Garden.Activity.View Garden.Profiles.View Garden.Profiles.Edit Garden.Moderation.Manage Garden.Curation.Manage Garden.PersonalInfo.View Garden.AdvancedNotifications.Allow Garden.Community.Manage Garden.Tokens.Add Garden.Uploads.Add Vanilla.Discussions.View Vanilla.Discussions.Add Vanilla.Discussions.Edit Vanilla.Discussions.Announce Vanilla.Discussions.Sink Vanilla.Discussions.Close Vanilla.Discussions.Delete Vanilla.Comments.Add Vanilla.Comments.Edit Vanilla.Comments.Delete Garden.Email.View Conversations.Moderation.Manage Conversations.Conversations.Add Vanilla.Approval.Require Vanilla.Comments.Me Plugins.Debugger.View Plugins.Debugger.Manage Vanilla.Tagging.Add

Do you think one of these fields is to blame?

elton · April 2020

I ran into this issue today and I managed to get it back up and running. I also followed the updates on the Role table to no avail. What I ended up doing was resetting the Permission table with a copy from a fresh install. That got the edit role page working again. I could then set the permissions back as I needed.

Hope that helps!