Error when deleting users since upgrade to 2.30

Doulos · Posted: Mon Dec 15, 2008 4:57 pm

Could not delete group for this user

DEBUG MODE

SQL 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 '' at line 2

DELETE FROM nuke_bbgroups WHERE group_id =

Line : 140
File : admin_users.php

I tried the fixgroup script and that did something, but new users still can't be deleted without this error.

Palbin · Posted: Mon Dec 15, 2008 7:40 pm

I don't understand exactly where this error is coming from. What exactly are you tring to do. I know it has something to do with users and groups, but I am not sure how to try and reproduce this.

Guardian2003 · Posted: Mon Dec 15, 2008 7:45 pm

Quote:

new users still can't be deleted

- why are new users being added to a group? This is not default behaviour.

Doulos · Posted: Mon Dec 15, 2008 7:47 pm

admin>forums>user admin>management>select a user>delete user

Guardian2003 · Posted: Mon Dec 15, 2008 7:58 pm

I need to look at this some more but it is 3am here and need sleep.
Looking at the code, I think the DELETE sequence is out of sync.
It deletes the user from the user table and then tries to delete the user from user group table.
I cannot off-hand see how that would work. The sql is user the user_id so once the user is removed, there can never be a match for that id in the group table.

Palbin · Posted: Mon Dec 15, 2008 8:07 pm

You can always use your_account admin to delete users untill this is resolved.

Palbin · Posted: Mon Dec 15, 2008 9:22 pm

The reason this error is happening is because the code assumes that there is a "single user group" for every user. This is not the case. A "single user group" is only created when a user needs "user specific" permissions and not "group specific" permissions.

This error appears to have been around for a while. I have several flavors of nuke, and they all appear to have this error.

Doulos, you should be using your_account to delete users. The reason is your_account could include integration with other modules, etc. There is a problem with using your_account though and it may actually be better to use the forums. See below for the reason.

Guardian2003, while looking at this I figured I would look to see what your_account did when deleting a user. It barely does anything. It does not delete nsn group info, forum post ect (no use keeping because they do not display without a userid), phpbb group info , phpbb auth_access info, and maybe more. I'm not sure if this was an oversight or was done to keep everything separate. No matter what I think a plug-in type system should be developed to allow "user info" from other modules to be deleted. Maybe have your_account read a directory for files, and if a file exists for a specific module display a check box at the confirmation screen to delete user info for that module.

I already talked to much so here is a fix for the problem at hand. Dance-Stick

Find lines 136-148 of /modules/Forums/admin/admin_users.php:

Code:

      $sql = "DELETE FROM " . GROUPS_TABLE . "
         WHERE group_id = " . $row['group_id'];
      if( !$db->sql_query($sql) )
      {
         message_die(GENERAL_ERROR, 'Could not delete group for this user', '', __LINE__, __FILE__, $sql);
      }

      $sql = "DELETE FROM " . AUTH_ACCESS_TABLE . "
         WHERE group_id = " . $row['group_id'];
      if( !$db->sql_query($sql) )
      {
         message_die(GENERAL_ERROR, 'Could not delete group for this user', '', __LINE__, __FILE__, $sql);
      }

Replace with:

Code:

      if ($row['group_id'] != '') {
         $sql = "DELETE FROM " . GROUPS_TABLE . "
            WHERE group_id = " . $row['group_id'];
         if( !$db->sql_query($sql) )
         {
            message_die(GENERAL_ERROR, 'Could not delete group for this user', '', __LINE__, __FILE__, $sql);
         }

         $sql = "DELETE FROM " . AUTH_ACCESS_TABLE . "
            WHERE group_id = " . $row['group_id'];
         if( !$db->sql_query($sql) )
         {
            message_die(GENERAL_ERROR, 'Could not delete group for this user', '', __LINE__, __FILE__, $sql);
         }
      }

montego · Posted: Tue Dec 16, 2008 5:32 am

Using the forums to manage users has always been a "no-no" (even groups since the early ways of RN). If something worked previously, it was "luck". Seriously, all user maintenance should be done through RNYA and Edit Groups. There can be a few exceptions, such as assigning Administrators in the Forums, but that is the only exception that I can think of.

montego · Posted: Tue Dec 16, 2008 5:35 am

BTW, just read Palbin's note in more detail. I just want to add that I think most would agree that there is still work to be done in the user management area and, yes, it has been Raven's desire to continue to move in the direction of removing the tight integration with phpbb2. I am sure that a better "plug-in" type interface between traditional *nuke and the forums, whatever they end up being in the end-state, would be received positively.

Palbin · Posted: Tue Dec 16, 2008 6:46 am

Actually other than removing the "_users_field_values" table information I think the forums do a much better good in deleting as user, in relation to all things user related.

montego · Posted: Tue Dec 16, 2008 6:59 am

Well, I respectfully must disagree, at least from the original YA module prior to 2.3.0 as we fixed all the deletion bugs and the forums are not NSN Groups aware. Now, it is possible that we missed some things with RNYA. Don't know for sure, because I have not looked.

Guardian2003 · Posted: Tue Dec 16, 2008 7:12 am

Playing devils advocate, the forums do a good job with removing the user from forum related data but that's because it is native phpBB code and that is what it was designed to do.
If you are using the forums to delete a user, it does not take into account things like NSN Groups the user might belong to, News, Weblinks, Reviews, Survey responses, or comments the user has made.
A plug-in type architecture would be great but I think it could be simplified by just changing the user name (I think this is what the forums do) or even appending something to the original user name, rather than trying to remove all data associated with a specific user and then force the users account into an inactive state.

Palbin · Posted: Tue Dec 16, 2008 7:44 am

Ok well maybe I am missing something in your_account in relation to nsn groups etc. But all I see are these three tables being modified in the removeuserconf.php
_users
_users_field_values
_users_temp_field_values

This is the reason I think the forums would be better for actually deleting a user. Now obviously if you what to deactivate a user you would do that instead. I am talking about deleting everything single thing relate to that user. When removing a user in the forums without deleting his post ect it through everything out of whack where his/her posts are involved. IE if the deleted user's post is the first post on a new page without any other post it will show as two pages when there isn't etc.

Guardian2003 wrote:

A plug-in type architecture would be great but I think it could be simplified by just changing the user name (I think this is what the forums do) or even appending something to the original user name, rather than trying to remove all data associated with a specific user and then force the users account into an inactive state.

That is called deactivation not deletion Smile

Also just an FYI the forums delete posts and everything upon deletion.

So basically all I am trying to say is it would be nice to have opinion in your_account when deleting a user to be asked if you want to deleting info from other modules (maybe have them all check by default). For example Forums, GCalendar, NSN Groups, any module with comments, and i am sure there are other modules that are not included in the default package that could benefit from this.

Palbin · Posted: Tue Dec 16, 2008 7:47 am

Just wanted to say that I do believe that all user management should be done from RNYA, but that does make it currently better IMO unless as I stated above I am missing something. Which I could be Very Happy

Guardian2003 · Posted: Tue Dec 16, 2008 8:53 am

No your aren't missing anything Wink

It looks like we did.
I will create a Mantis issue for this if there isn't one already.

Palbin · Posted: Tue Dec 16, 2008 12:22 pm

Well the fix I posted above will fix the problem that the original poster had so lets move this conversation over to the thread in the staff forums

Doulos · Posted: Tue Dec 16, 2008 12:25 pm

The only reason I was using Forums admin to delete users is because there is no "delete" option in RNYA.

I guess I could just deactivate them instead.

jakec · Posted: Tue Dec 16, 2008 12:34 pm

Once you have deactivated the user you then have the option to remove the user.

Doulos · Posted: Tue Dec 16, 2008 1:01 pm

Duh. Thanks.

kguske · Site Admin Joined: Jun 04, 2004 Posts: 6044

Only registered users can see links on this board!
Get registered or login to the forums!

a fix for this using the RN 2.2 approach for deleting / updating other data before deleting a user.

Doulos, guardian, jakec, montego and Palbin (and others), please test this so I can add to SVN prior to the next update.

Palbin · Posted: Sun Dec 28, 2008 11:32 pm

I didn't actually try anything yet just looked at the code as I am currently setting up a clean RN site.

I have a problem with the below code which basically is exactly what the comments say.

Code:

// This deletes only the first group where the user is the moderator? Why delete the group, instead of making another moderator?
$result2 = $db->sql_query('SELECT group_id FROM ' . $user_prefix . '_bbgroups WHERE group_moderator = \''.$rem_uid.'\'');
$row2 = $db->sql_fetchrow($result2);
$del_group_id = intval($row2['group_id']);
if (intval($del_group_id) > 0) {
$db->sql_query('DELETE FROM ' . $user_prefix . '_bbgroups WHERE group_id=\''.$del_group_id.'\'');
$db->sql_query('DELETE FROM ' . $user_prefix . '_bbauth_access WHERE group_id=\''.$del_group_id.'\'');
}

Its late and I am having a hard tiem thinking so I will post something more specific tomorrow.

Palbin · Posted: Sun Dec 28, 2008 11:35 pm

On a side note you have $user_prefix for every table Shocked

kguske · Site Admin Joined: Jun 04, 2004 Posts: 6044

Yeah, I copied that straight from the previous version of YA.

Palbin · Posted: Sun Dec 28, 2008 11:54 pm

Instead of trying to explain the forum groups I will just rewrite that section of code and post back.

I'll just post a link to both files. I'll fix the prefixes and stuff too.

kguske · Site Admin Joined: Jun 04, 2004 Posts: 6044

There was definitely code in earlier versions of RN to automatically add a user to a forum group of 1. I'm sure this is related to that - just not sure why this approach was used to delete that group when deleting the user.