|
|
Joined: Oct 2007
Posts: 14
stranger
|
|
stranger
Joined: Oct 2007
Posts: 14 |
Is there a way to remove all old users from the database that either don't have validated emails, or who have never signed in? We have a few thousand of these accounts. Hopefully there is a board control panel feature to do this. However, I am proficient in MySQl, if that is the only way to manually remove the accounts. I just want to make sure I don't remove a bunch of users and then break the boards. When I run this query, I get over 20,000 rows in response, which indicates there are a lot of sign-ups that are invalid. SELECT USER_REGISTRATION_EMAIL,USER_LAST_IP,
USER_REGISTERED_ON,USER_LAST_VISIT_TIME,
USER_LAST_IP
FROM (ubbt_USERS as u, ubbt_USER_DATA as ud)
WHERE u.USER_ID=ud.USER_ID
AND USER_RULES_ACCEPTED=0
AND USER_REGISTERED_ON=USER_LAST_VISIT_TIME
AND USER_LAST_IP IS NULL
AND USER_LAST_IP IS NULL; For those users can I simply do something like this, perhaps with throwing in a check to ensure I only run this on users over 10 days old? Do other tables need cleansing as well? DELETE FROM ubbt_USERS, ubbt_USER_DATA
WHERE ubbt_USERS.USER_ID=ubbt_USER_DATA.USER_ID
AND USER_RULES_ACCEPTED=0
AND USER_REGISTERED_ON=USER_LAST_VISIT_TIME
AND USER_LAST_IP IS NULL
AND USER_LAST_IP IS NULL Many thanks.
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
There are several other tables as well. ubbt_USER_PROFILE, ubbt_PRIVATE_MESSAGE_USERS,ubbt_PRIVATE_MESSAGE_TOPICS,ubbt_PRIVATE_MESSAGE_POSTS
What you might consider is using the member management tool in the control panel. With that you could specify a search for users that haven't logged in since a certain date and have 0 posts, that might catch a lot of those users you're looking for.
From there you can specify to delete those users and it will clean up the appropriate tables.
|
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
Okay Rick, I am a little confused on this.
In the current version 7.5.3 and several past releases if you have email verification turned on. You are notified of pending users that have not validated their email yet. And you have the option to approve,resend email, or decline.
Or is there some time limit imposed that they will not display after a while?
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
No time limit involved, and that will work fine for users that you haven't been approved yet. From my understanding he's looking for users already in the database and active, but have either never logged on, or were in the database already prior to when email validation was turned on.
|
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Oct 2007
Posts: 14
stranger
|
|
stranger
Joined: Oct 2007
Posts: 14 |
What you might consider is using the member management tool in the control panel. With that you could specify a search for users that haven't logged in since a certain date and have 0 posts, that might catch a lot of those users you're looking for. Thanks Rick for the feedback. Unfortunately we often get users that go quiet for over a year and then come back. We can't have those users deleted, even if they have never posted. Perhaps a cool feature would be a 'prune bogus users'. Essentially, we want to remove all users that never officially finished their signup.
|
|
|
|
|
Joined: Oct 2007
Posts: 14
stranger
|
|
stranger
Joined: Oct 2007
Posts: 14 |
Okay Rick, I am a little confused on this.
In the current version 7.5.3 and several past releases if you have email verification turned on. You are notified of pending users that have not validated their email yet. And you have the option to approve,resend email, or decline.
Or is there some time limit imposed that they will not display after a while? Hi Ruben, Strangely enough I have never seen this queue. However, I just logged in now and see that there is one person in a queue that registered yesterday and has not approved their email. Strange that no others are showing up. Most of the dead users we have are from a few years ago, they got imported when we upgraded in 2007... or was that 2008.
|
|
|
|
|
Joined: Oct 2007
Posts: 14
stranger
|
|
stranger
Joined: Oct 2007
Posts: 14 |
Hi Rick, Just to confirm that I have my query close to being right and that no tables have been overlooked. Does this look acceptable to you? The select query: SELECT USER_REGISTRATION_EMAIL,USER_LAST_IP,
USER_REGISTERED_ON,USER_LAST_VISIT_TIME,
USER_LAST_IP
FROM ubbt_USERS as u
LEFT JOIN ubbt_USER_DATA AS ud ON ud.USER_ID = u.USER_ID
LEFT JOIN ubbt_USER_PROFILE AS uprof ON uprof.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_USERS AS upmu ON upmu.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_TOPICS AS upmt ON upmt.TOPIC_ID = upmu.TOPIC_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_POSTS AS upmp ON upmp.TOPIC_ID = upmu.TOPIC_ID
WHERE USER_RULES_ACCEPTED=0
AND USER_REGISTERED_ON=USER_LAST_VISIT_TIME
AND USER_LAST_IP IS NULL; The delete: DELETE ubbt_USERS,
ubbt_USER_DATA,
ubbt_USER_PROFILE,
ubbt_PRIVATE_MESSAGE_USERS,
ubbt_PRIVATE_MESSAGE_TOPICS,
ubbt_PRIVATE_MESSAGE_POSTS
FROM ubbt_USERS as u
LEFT JOIN ubbt_USER_DATA AS ud ON ud.USER_ID = u.USER_ID
LEFT JOIN ubbt_USER_PROFILE AS uprof ON uprof.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_USERS AS upmu ON upmu.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_TOPICS AS upmt ON upmt.TOPIC_ID = upmu.TOPIC_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_POSTS AS upmp ON upmp.TOPIC_ID = upmu.TOPIC_ID
WHERE USER_RULES_ACCEPTED=0
AND USER_REGISTERED_ON=USER_LAST_VISIT_TIME
AND USER_LAST_IP IS NULL; I will of course be backing up before hand!
|
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
Hi Ruben,
Strangely enough I have never seen this queue. However, I just logged in now and see that there is one person in a queue that registered yesterday and has not approved their email.
Strange that no others are showing up.
Most of the dead users we have are from a few years ago, they got imported when we upgraded in 2007... or was that 2008. Yep that is the way it is supposed to work. When you have email verification on or age restrictions stuff. They stay there until they reply to the email verification link or you manually approve,resend the email or reject the user.
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Oct 2007
Posts: 14
stranger
|
|
stranger
Joined: Oct 2007
Posts: 14 |
Thanks Ruben. Very cool stuff. Most of the user accounts we want to remove now are ones from a long long time ago, 3-4 years I think, that came across to the new version in an upgrade by Rick a couple of years ago.
|
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
Then I think Ricks suggestion using the member management in the control panel would be the best option.
You can build searches for what you are looking for in the advanced search options but still get to preview the list. That way the oddball stuff can be reviewed.
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
No time limit involved, and that will work fine for users that you haven't been approved yet. From my understanding he's looking for users already in the database and active, but have either never logged on, or were in the database already prior to when email validation was turned on. Okay Rick I have a rebuttal on this issue. In the language file "adduser.php" There is a field called "VERIFY". In this field is the message"You need to verify your email to activate this account. You will receive an email shortly with instructions for verifying your email. If this account isn't verified within 24 hours it will be deleted." Without looking any farther it appears something deletes the registration Que within 24 hours of the registration if not verified???
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
Okay I found something in "adduser.inc.php" 00124: // Get rid of any unverified email accounts that are over 24 hours old
00125: $deldate = $html -> get_date();
00126: $deldate = $deldate - 86400;
00127:
00128: $query = "
00129: SELECT USER_ID
00130: FROM {$config['TABLE_PREFIX']}USERS
00131: WHERE USER_IS_APPROVED <> 'no' Would this not delete users from the que after 24 hours! Of course I did not copy the whole section but you will see that it purges the registration Que after 24 hours. At least it seems so. My opinion is the Que should stay there until the Admin deletes it
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Feb 2007
Posts: 1,294 Likes: 2
Veteran
|
|
Veteran
Joined: Feb 2007
Posts: 1,294 Likes: 2 |
I would rather not be bothered on manually deleting every registration that has not been email verified in the time limit. Why not let the program just delete it?
|
|
|
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78
|
|
Joined: Dec 2003
Posts: 6,562 Likes: 78 |
Well I think it is a issue when a member has a problem with registration and gets deleted within 24 hours. I think that is to short a duration.
Rick stated something will be placed in V8. Hopefully it will be a option to set a time limit ourselves like 0 to not delete or 24,48,72 hours to self delete. That way it keeps us all happy campers.
Of course in the meantime we could hack it in.
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Oct 2007
Posts: 14
stranger
|
|
stranger
Joined: Oct 2007
Posts: 14 |
A long over due update to this thread. The original MySQL queries would delete a lot of valid users with valid posts. Here is a refined Query that will delete only users that have no posts, have not validated their email, have not accepted the board rules, are over 50 days old and have no private messages. The reason for all the checks for no this and no that, are from members that signed up back in the early days of this forum when a lot of features didn't exist. Our board dates back to 2000. Here is the query to find bogus users: SELECT USER_ID
FROM ubbt_USERS
WHERE USER_ID in (
SELECT u.USER_ID FROM ubbt_USERS as u
JOIN ubbt_USER_DATA AS ud
ON ud.USER_ID = u.USER_ID
JOIN ubbt_USER_PROFILE as uup
ON u.USER_ID=uup.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_USERS AS upmu
ON upmu.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_TOPICS AS upmt
ON upmt.TOPIC_ID = upmu.TOPIC_ID
AND upmt.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_POSTS AS upmp
ON upmp.TOPIC_ID = upmu.TOPIC_ID
AND upmp.USER_ID = u.USER_ID
WHERE USER_TOTAL_POSTS=0
AND upmu.USER_ID IS NULL
AND upmt.USER_ID IS NULL
AND upmu.USER_ID IS NULL
AND USER_LAST_VISIT_TIME < unix_timestamp(now()) - (60*60*24*50)
AND USER_REGISTERED_ON=USER_LAST_VISIT_TIME
); And the query to delete those users: DELETE u, ud, uup
FROM ubbt_USERS as u
JOIN ubbt_USER_DATA AS ud
ON ud.USER_ID = u.USER_ID
JOIN ubbt_USER_PROFILE as uup
ON u.USER_ID=uup.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_USERS AS upmu
ON upmu.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_TOPICS AS upmt
ON upmt.TOPIC_ID = upmu.TOPIC_ID
AND upmt.USER_ID = u.USER_ID
LEFT JOIN ubbt_PRIVATE_MESSAGE_POSTS AS upmp
ON upmp.TOPIC_ID = upmu.TOPIC_ID
AND upmp.USER_ID = u.USER_ID
WHERE USER_TOTAL_POSTS=0
AND upmu.USER_ID IS NULL
AND upmt.USER_ID IS NULL
AND upmu.USER_ID IS NULL
AND USER_LAST_VISIT_TIME < unix_timestamp(now()) - (60*60*24*50)
AND USER_REGISTERED_ON=USER_LAST_VISIT_TIME;
|
|
|
|
|
Joined: Jan 2004
Posts: 2,474 Likes: 3
Pooh-Bah
|
|
Pooh-Bah
Joined: Jan 2004
Posts: 2,474 Likes: 3 |
Hey Plattapuss, That's really interesting. (Thanks  ) However, I was quite shocked to see such a massive figure returned in the results. I haven't analysed the data yet but the figure is (very) high... Interesting though, to note that all bar *1* are prior to the date that I moved to Threads in January '07. Do you think that could be down to (specifically) validation of email / acceptance of board rules?
|
|
|
Bots
by Outdoorking - 04/13/2024 5:08 PM
|
|
|
|
|
|
|
|
|
|
|
2 members (Nightcrawler, Ruben),
694
guests, and
214
robots. |
|
Key:
Admin,
Global Mod,
Mod
|
|
|
|
