how to delete thousands of old inactive users via SQL phpmyadmin ?

[germaninfl]

I'm cleaning up my database and I want to get rid of all user accounts that never posted and are inactive since 1 year.

DELETE FROM `user` WHERE `usergroupid`=6 and `posts`=0 and ???

Who can help me with the last part (inactive since 1 year) ?
Thanks

[Wayne Luke]

You shouldn't have thousands of users in usergroupid=6. That is the Administrator usergroup.

You should delete them in the AdminCP under Users -> Prune/Move Users. Deleting users using a query will cause irreparable harm to your site.

[germaninfl]

sorry, typo ... it is usergroup 2 (new users) where I wanted to apply this. I have thousands there that signed up but never posted anything

and when I use the Prune/Move User function in the admincp I get this error from vBulletin

Error Invalid SQL: SELECT DISTINCT user.userid, username, email, posts, lastactivity, joindate, user.usergroupid, moderator.moderatorid, usergroup.title FROM user AS user LEFT JOIN moderator AS moderator ON(moderator.userid = user.userid) LEFT JOIN usergroup AS usergroup ON(usergroup.usergroupid = user.usergroupid) WHERE user.usergroupid = 2AND lastactivity < 1538420145AND joindate < 1569906000AND posts < 1 GROUP BY user.userid ORDER BY username ASC;

[Wayne Luke]

That is possible, just not sure what it would occur. A default install has Administrators as group 6 for the last 15 years.

My advice to delete them in the AdminCP stands. You can choose one or more usergroups to delete.

[germaninfl]

The problem is that I'm getting above Error message



results in:





is there perhaps a Space missing between the 2AND

[Wayne Luke]

This is fixed in vBulletin 5.5.5 Alpha 4. Sorry. this issue slipped my mind. You can either upgrade to the Alpha or wait about three weeks for the full release.