Possible to delete all users private messages? (empty database tables?)

Started by OCJ, November 28, 2011, 11:11:45 PM

Previous topic - Next topic

OCJ

I would like to trim the size of the database if possible. People don't delete private messages after being asked to get rid of old stuff so I would like to be able to get rid of them all every 6 months or 1 year.

I can't find anything in the admin panel or searching here but there must be at least a fairly simple sql command to empty out the database tables...with messing up the system?




Chas Large

I use phpMy Admin to edit my databases as it uses a GUI and my SQL knowledge is limited. If your SQL is good then you can probably work out the correct query but phpMyAdmin can help.

The first problem you'll encounter is that the messages are all timestamped and therefore you can't easily search for all records less than or equal to say December 31st 2010, you'd need to convert that date (and time of 00:00:00) into a timestamp first. This website will do that for you.

http://www.epochconverter.com/

It should then be a matter of searching for all the messages in the smf_personal_messages table <= that date (timestamp) and deleting them. I don't know the SQL for that, sorry. Someone else can probably chip in with that.

If you do that with the search tool in phpMyAdmin you can then checkbox all the messages and drop them from the table.

TAKE A FULL BACKUP FIRST and take the forum OFF LINE before doing any editing first.
My Modifications :)  My Forum

Please DO NOT PM me with support requests. Post the problem in the appropriate Support Board so everyone can benefit from the advice given.

OCJ

I thought about just emptying the tables (private_messages) but not sure if there are other related tables that would  throw up errors like message counts.
Its hit n miss for me too with mysql but often lucky it turns out ok ... especially wen you have backups ...  :P

Other boards do have admin options to clear out old stuff - maybe something for smf 3.0
Thanks, notice you are always busy helping out! The calendar - great feature of smf

HunterP

Quote from: igirisjin on November 29, 2011, 10:46:55 AM
I thought about just emptying the tables (private_messages) but not sure if there are other related tables that would  throw up errors like message counts.

smf_pm_recipients is related, it contains the PM's recipients.

Chas Large

The SQL to find all messages up to a certain date  would be:

SELECT *  FROM `smf_personal_messages` WHERE `msgtime` <= 1293840000

1293840000 = 01/01/2011 00:00:00

If you really want to DELETE records using the same or similar query search use

DELETE  FROM `smf_personal_messages` WHERE `msgtime` <= 1293840000

However even after running this query and doing an SMF Maintenance Recount and Find and Fix errors, the PM page number count on a users PM inbox remains incorrect. I tested this on a test DB that had 62 pages of PMs and after deleting the records, the PMs had indeed gone but the page count remained at 62 so there is an impact elsewhere. I don't know where this count is stored.
My Modifications :)  My Forum

Please DO NOT PM me with support requests. Post the problem in the appropriate Support Board so everyone can benefit from the advice given.

HunterP

Quote from: Chas Large on November 29, 2011, 03:29:03 PM
However even after running this query and doing an SMF Maintenance Recount and Find and Fix errors, the PM page number count on a users PM inbox remains incorrect. I tested this on a test DB that had 62 pages of PMs and after deleting the records, the PMs had indeed gone but the page count remained at 62 so there is an impact elsewhere. I don't know where this count is stored.

As I mentioned in a different topic, PM's can remain in smf_personal_message, without having recipients. This can occur when the sender keeps sent messages in his out-box. So, I guess that the number of messages is being calculated from smf_pm_recipients. Your number of PM's should depend on the number of occurences of your id_member in smf_pm_recipients.

So, if you really want to clean PM's, you should also cleam smf_pm_recipients. I'm not that good in SQL, but it should be a query with checks if a id_pm exists in smf_personal_messages. If not, rows containing this id_pm can be deleted from smf_pm_recipients.

OCJ

Backup and play with it then ... will try it at the end of the year - thanks!

HunterP


Chas,

Quote from: Chas Large on November 29, 2011, 03:29:03 PM
However even after running this query and doing an SMF Maintenance Recount and Find and Fix errors, the PM page number count on a users PM inbox remains incorrect. I tested this on a test DB that had 62 pages of PMs and after deleting the records, the PMs had indeed gone but the page count remained at 62 so there is an impact elsewhere. I don't know where this count is stored.

How many rows does this query return??

select * from smf_pm_recipients where id_pm not in (select id_pm from smf_personal_messages)

Chas Large

Quote from: HunterP on December 01, 2011, 03:25:27 PM

Chas,

Quote from: Chas Large on November 29, 2011, 03:29:03 PM
However even after running this query and doing an SMF Maintenance Recount and Find and Fix errors, the PM page number count on a users PM inbox remains incorrect. I tested this on a test DB that had 62 pages of PMs and after deleting the records, the PMs had indeed gone but the page count remained at 62 so there is an impact elsewhere. I don't know where this count is stored.

How many rows does this query return??

select * from smf_pm_recipients where id_pm not in (select id_pm from smf_personal_messages)

Running this before deleting any PMs on a test dB  I get zero.

Using the Delete query I deleted 11,081 rows in the messages table. This gives 62 pages total for PMs in SMF (My messages)

Running your query above I get 11,766

Changing it to:

DELETE from smf_pm_recipients where id_pm not in (select id_pm from smf_personal_messages)

It removes those 11,766 rows and reduces the pages in SMF (My Messages) to 46.

Well done, sounds like the good basis for a MOD ;)

My Modifications :)  My Forum

Please DO NOT PM me with support requests. Post the problem in the appropriate Support Board so everyone can benefit from the advice given.

ApplianceJunk

Quote from: igirisjin on December 01, 2011, 10:16:52 AM
Backup and play with it then ... will try it at the end of the year - thanks!


You could also recreate your site locally using something like mamp or wamp and experiment with your site that way instead of playing around with the live site, just a thought.

Chas Large

Quote from: ApplianceJunk on December 02, 2011, 10:50:21 AM
Quote from: igirisjin on December 01, 2011, 10:16:52 AM
Backup and play with it then ... will try it at the end of the year - thanks!


You could also recreate your site locally using something like mamp or wamp and experiment with your site that way instead of playing around with the live site, just a thought.
EasyPHP works well (Windows) and has fully integrated MySql and PhpMyAdmin functionality.
My Modifications :)  My Forum

Please DO NOT PM me with support requests. Post the problem in the appropriate Support Board so everyone can benefit from the advice given.

OCJ

Yes... I will use a copy site for that. It would be useful as a mod - sure there are a lot of bloated boards out there.

Advertisement: