Support Forum
Support Forum
Offline
Yellow Swordfish said
Sorry about that. The thread got hijacked and i think we both missed the post. It is annoying when that happens.That is a pretty simplistic query. I assume it is creating a temp table because of the number of members but I see no reason why it should lock up the database. Either the db can create and work with temp tables or it can't so that is a bit odd.
OK - let's look at this in another way. Do you know you way around the database and SQL queries?
No problem it happens!
I know how to run SQL queries and modify them to fit my database but I'm no expert at it, I can't write the query from scratch.
Offline
Bump! Yellow Swordfish did you want me to try and run a query?
Offline
Oh dear. I wrote a post asking some questions and posted it... and that was probably about the time my ISP decided to crash their network this afternoon which took a couple of hours to come back... Sorry about that. So - let me see if I can remember...
The first was a recap to make 100% sure I understand the position. So - as I understand it - you have X number of members who are NOT in ANY user group and who should ALL be in one, specific user group. Is that correct?
Other questions were - are you the only forum admin? Are all the members who ARE in their user group correctly allocated? and there may have been one or two others but that is probably enough to work with...
 |
YELLOW
SWORDFISH
|
Offline
Let's assume that the above recap of the situation was correct. Then you should be able to get your data up to date with the following 3 queries - run one at a time. Of course make a database backup before trying this.
Query 1:
CREATE VIEW no_membership_view AS SELECT DISTINCT wp_sfmembers.user_id FROM wp_sfmemberships RIGHT JOIN wp_sfmembers ON wp_sfmembers.user_id = wp_sfmemberships.user_id WHERE wp_sfmemberships.user_id IS NULL AND wp_sfmembers.admin = 0;
Query 2:
INSERT INTO wp_sfmemberships (user_id, usergroup_id) SELECT no_membership_view.user_id, 111 AS usergroup_id FROM no_membership_view;
Query 3:
DROP VIEW no_membership_view;
Now - a few things you will need to change here, First up the table prefix. These queries above all use the default 'wp_' and if yours is different that will need to be changed in all instances.
Also - in Query 2 you will see the 111 - this needs to be changed for the User Group ID of the target user group you want these 'floating' members to belong to. You can get this ID from the manage User Grups admin panel.
OK?
|
YELLOW
SWORDFISH
|
Offline
Yellow Swordfish said
Sorry about that. The thread got hijacked and i think we both missed the post. It is annoying when that happens.
So you'd prefer that users who seem to be experiencing a similar situation and just may have some additional insight and information to offer the OP just stay quiet. It's annoying when that happens....
Offline
Yellow Swordfish said
Let's assume that the above recap of the situation was correct. Then you should be able to get your data up to date with the following 3 queries - run one at a time. Of course make a database backup before trying this.CODE
Now - a few things you will need to change here, First up the table prefix. These queries above all use the default 'wp_' and if yours is different that will need to be changed in all instances.
Also - in Query 2 you will see the 111 - this needs to be changed for the User Group ID of the target user group you want these 'floating' members to belong to. You can get this ID from the manage User Grups admin panel.
OK?
I ran this and it imported the members that were missing so now "sfmembers" & "sfmemberships" have the same amount of users. BUT the majority of users in "sfmemberships" have their "usergroup_id" set as "-1". The correct usergroup id for members usergroup should be "2".
Offline
Well unless you specified a value of -1 there is no possible way that the SQL above could dispense that value into that column. Nor does that column have a default value to be used if not set in the insert or update statements.
But it is easy enough to run an update query to change all the -1 values to 2.
|
YELLOW
SWORDFISH
|
Offline
No it was not the SQL above that did it, it has been like that since this problem first started I believe.
But now after I changed all -1 to 2 it seems to be working correct by the first look of things. 
But I wonder how all this happened?
Offline
An important question but not one I can answer. Well - the timeout of your mysql query I can understand and I have raised this as an issue to be investigated in our bug log.
But the -1 value? I see no way that can have arisen to be honest.
|
YELLOW
SWORDFISH
|
Offline
Yellow Swordfish said
An important question but not one I can answer. Well - the timeout of your mysql query I can understand and I have raised this as an issue to be investigated in our bug log.
But the -1 value? I see no way that can have arisen to be honest.
Regarding the timeout, at first I couldn't run the SQL query #2 that you posted above either, I had the same "timeout" error where it locks up mysql. Only after I added an index to the "user_id" (if I remember correct) I could run it successfully.
All RSS1 Guest(s)