Due to usergroup issues from the upgrade, I need to update everyone's usergroup who is a guest to user


Except for USER_ID #1.

:help:

add 'AND USER_ID > 1' to the clause

bada bing!

thank you, I had something similar, but of course it was erroring out.

Statement can still error out if a user was already in group 4 also.

I'm getting the same error:

SQL Error: Duplicate entry '6-4' for key 1

I've tried this:

UPDATE ubbt_USER_GROUPS SET GROUP_ID = '4' WHERE GROUP_ID = '5' AND USER_ID > '1'

and this:

UPDATE ubbt_USER_GROUPS SET GROUP_ID = '4' WHERE GROUP_ID = '5' AND USER_ID > 1

and get the same error.

The query goes wrong because there are already people in group 4 AND in group 5. 5 wants to change to 4, but 4 is already there, so it errors out because of the index settings.

Do this one before:

delete from ubbt_USER_GROUPS where GROUP_ID = 5 and USER_ID in (select USER_ID from ubbt_USER_GROUPS where GROUP_ID = 4)

Not exactly sure about this one, backup the table first please This would remove anyone from group 5 if they are already in group 4.

Best way would probably just to get rid of everyone in group 5 and then if you have MySQL 4.1+ you can add everyone to the user's group that isn't already in a group.



and then:




On another note, I need to figure out why this is happening for some people. Out of all the ugprades I've done, I haven't had this happen.

Thank you, I ended up using phpmyadmin to backup the table, edit it in ultraedit then truncate and re-install.

This particular site shows MySQL:

Client API version 3.23.58

The web host claims the server install is 4.something, but maybe the difference is the issue? I always see errors here not seen on other sites when I update it - just when I think it's safe to upgrade the bottom falls out and something simple isn't.

Hmm, the first thing I'd do is verify the version of MySQL itself. If you run the following SQL command you can get it:

show variables

Towards the bottom you'll see the version variable that will give you the actual version running.

AA, that is the MySQL client built into your php, NOT the version of MySQL itself. Rick's show variables will give you the version of MySQL. Note that the older MySQL client you have in your php might limit some of the things you can do with MySQL because that client doesn't know how to ask MySQL to do them.