Jump to content

Mass delete PMS?


RSAC
 Share

Recommended Posts

  • 2 weeks later...
  • 2 weeks later...
  • 6 months later...
On 6/12/2017 at 9:48 AM, CP_User said:

Do you have the SQL command, which would be helpful :D

 

Write about a 20 line php script to do the sql. There are three sql message tables that interact with each other. Do a select on one of the tables based on your criteria. Then loop through the results array and remove entries from all three tables with the matching private message topic id. Study the tables a little and understand what the fields and mappings are. It should take about an hour or two to write.

Obviously, give members some advance notice of this, do a backup, and test with manual sql lines, debug output lines and small limits before operating on live data. Of course, if there's something else I'm missing that's linked in another table somewhere I don't know. I've already done it. It seems to work. You'll need to write it on your own though. I don't mind the risk on my own board. I have mine set for 36 months retention from the time of the last post in a PM topic except for the bot account which is 3 months.

Edited by prupdated
Link to comment
Share on other sites

4 hours ago, prupdated said:

Write about a 20 line php script to do the sql. There are three sql message tables that interact with each other. Do a select on one of the tables based on your criteria. Then loop through the results array and remove entries from all three tables with the matching private message topic id. Study the tables a little and understand what the fields and mappings are. It should take about an hour or two to write.

Obviously, give members some advance notice of this, do a backup, and test with manual sql lines, debug output lines and small limits before operating on live data. Of course, if there's something else I'm missing that's linked in another table somewhere I don't know. I've already done it. It seems to work. You'll need to write it on your own though. I don't mind the risk on my own board. I have mine set for 36 months retention from the time of the last post in a PM topic except for the bot account which is 3 months.

That’s not the best way to go. There are other tables to update like core_members. A plugin is the best thing to do, by looping data, comparig the groups or conversation last post, loading the conversation and then simply using a $conversation->delete():. It will take care of all related data. 

Ps: totally untested and it is just a guess without look any line of code.

Link to comment
Share on other sites

9 hours ago, Adriano Faria said:

That’s not the best way to go. There are other tables to update like core_members. A plugin is the best thing to do, by looping data, comparig the groups or conversation last post, loading the conversation and then simply using a $conversation->delete():. It will take care of all related data. 

Ps: totally untested and it is just a guess without look any line of code.

Great ideas so far. Maybe you can write one? Many people here would surely love to see it. It doesn’t seem too complex. It almost seems like this should be built in.

So far what I did seems to be working though in trimming off about half a million private messages from over 3 years ago. I can’t really wait another several years because it’s a struggle sometimes to keep the board running smoothly.

The problem is with bloated tables like this, notifications, and search, some forums such as mine (several million posts) can really start bogging down. And I can’t really justify throwing more hardware at it. Once these tables are trimmed up and reoptimized, along with severely limiting search and notification history, and sticking mostly to core functionality, the site runs at about half the cpu / io or less than it would otherwise.

Edited by prupdated
Link to comment
Share on other sites

 Share

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...

Important Information

We use technologies, such as cookies, to customise content and advertising, to provide social media features and to analyse traffic to the site. We also share information about your use of our site with our trusted social media, advertising and analytics partners. See more about cookies and our Privacy Policy