Announcement

Collapse
No announcement yet.

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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.

    Code:
    DELETE FROM `user` WHERE `usergroupid`=6 and `posts`=0 and ???
    Who can help me with the last part (inactive since 1 year) ?
    Thanks

  • #2
    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.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API - Full / Mobile
    Vote for your favorite feature requests and the bugs you want to see fixed.

    Comment


    • #3
      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;
      Last edited by germaninfl; Tue 1 Oct '19, 10:57am.

      Comment


      • #4
        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.
        Translations provided by Google.

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud demonstration site.
        vBulletin 5 API - Full / Mobile
        Vote for your favorite feature requests and the bugs you want to see fixed.

        Comment


        • #5
          The problem is that I'm getting above Error message
          Click image for larger version  Name:	first.PNG Views:	0 Size:	54.4 KB ID:	4423423


          results in:



          Click image for larger version  Name:	2nd.PNG Views:	0 Size:	30.8 KB ID:	4423424

          is there perhaps a Space missing between the 2AND

          Comment


          • #6
            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.
            Translations provided by Google.

            Wayne Luke
            The Rabid Badger - a vBulletin Cloud demonstration site.
            vBulletin 5 API - Full / Mobile
            Vote for your favorite feature requests and the bugs you want to see fixed.

            Comment

            Related Topics

            Collapse

            Working...
            X