|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
Due to usergroup issues from the upgrade, I need to update everyone's usergroup who is a guest to user
UPDATE ubbt_USER_GROUPS SET GROUP_ID = '4' WHERE GROUP_ID = '5'
Except for USER_ID #1. :help:
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
add 'AND USER_ID > 1' to the clause bada bing!
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
thank you, I had something similar, but of course it was erroring out.
|
|
|
|
Joined: Aug 2006
Posts: 1,358
Veteran
|
Veteran
Joined: Aug 2006
Posts: 1,358 |
Statement can still error out if a user was already in group 4 also.
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
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.
|
|
|
|
Joined: Aug 2006
Posts: 1,358
Veteran
|
Veteran
Joined: Aug 2006
Posts: 1,358 |
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.
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
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.
delete from ubbt_USER_GROUPS where GROUP_ID='5' and USER_ID > 1
and then:
insert ubbt_USER_GROUPS(
select ubbt_USERS.USER_ID, 4
from ubbt_USERS
where ubbt_USERS.USER_ID not in (
select ubbt_USER_GROUPS.USER_ID
from ubbt_USER_GROUPS
)
and ubbt_USERS.USER_ID <>1
)
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.
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
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.
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
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.
|
|
|
|
Joined: Jul 2006
Posts: 2,143
Pooh-Bah
|
Pooh-Bah
Joined: Jul 2006
Posts: 2,143 |
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.
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
version 4.1.21-standard
I understand what you're saying... for some reason I can import a hundred sites and not have a problem, but this one always gives me issues - not sure what it is.
|
|
|
|
Joined: Jan 2004
Posts: 56
journeyman
|
journeyman
Joined: Jan 2004
Posts: 56 |
I am using 5.0.45 are there any known issues with version 5+?
(sorry not trying to Hijack the thread)
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
nope -- i'd highly recommend going to latest (release) of mysql.. same for apache and php..
i run all of em on my sites.
|
|
|
|
Joined: Nov 2006
Posts: 3,095 Likes: 1
Carpal Tunnel
|
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095 Likes: 1 |
Agreed - no problems for me either. I had my hosting provider put me on another server that was running the latest versions and all works for me.
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
Former Developer
|
Former Developer
Joined: Apr 2007
Posts: 3,940 Likes: 1 |
i'd also recommend Linux box vs Windows box, if at all possible.. many reasons for this..
some peeps may not be able to change OSes, but if you are starting fresh, go *Nix
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
Server 2008 is looking pretty suite
|
|
|
1 members (Ruben),
1,277
guests, and
207
robots. |
Key:
Admin,
Global Mod,
Mod
|
|
|
|