How to delete messages?

How to delete messages?

by Ian L -
Number of replies: 23

I mentioned before that I can't access my messages in Moodle. I noticed if I log in as another user I can. I am thinking maybe there are too many maybe because when I click on the upper right notification for message nothing happens for a few minutes and then I get an error.

moodle message


The same thing happens when I click on a students profile and then message. I get a blank messaging drawer and then it crashes. Mentioned this before and did a lot of searching and found a few people mentioning it but no solution. Some people said going into the database but wondering if there is an automated way or other way.kkkk

Average of ratings: -
In reply to Ian L

Re: How to delete messages?

by Ken Task -
Picture of Particularly helpful Moodlers

@Moderator ... does it appear OP has now gone full circle?

https://moodle.org/mod/forum/discuss.php?d=433340#p1754615

where is was suggested to change settings for retention of such messages.

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: How to delete messages?

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
TL;DR

But this is the confusion that's caused when (a) a message "hijacks" another thread and (b) a new thread is started about what might appear to be the same thing. Anyway....
Average of ratings: -
In reply to Ken Task

Re: How to delete messages?

by Will B -
That's a lot of messages! I'm experiencing a similar issue but with 10% of the volume of messages you have. Your DB must be huge.

Have you ran a mysql tuner? If not, do so. This fixed one of my sites but not another. The one it didn't fix, the messages loaded eventually. The one it did, the messages crashed. So there's a good chance this could fix it.

Are you storing sessions in the database? If you are, I'd recommend not.

That list of errors is relating to session lookup in the db. Eliminate sessions storing in the DB and theoretically you shouldn't get the error.

FWIW, I updated my site from shared hosting onto a VPS which has more than enough resources to run the DB at the size mine is and mine still takes an age to load. Perhaps the messaging in 4 (and late 3) isn't robust enough as I've never experienced this on prev issues.

P.S, if you try an account that doesn't have a lot of messages, does it load instantly? Mine does, which explains it's a DB issue. I'd recommend cleaning up the messages if at all possible.

Will
Average of ratings: -
In reply to Will B

Re: How to delete messages?

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
You get session errors because the previous session is still 'running' (even if crashed or disconnected from the browser), so it's not an error as such. However, agree 100%, do not use the database for sessions.
Average of ratings: -
In reply to Howard Miller

Re: How to delete messages?

by Ian L -

I checked server/session handling.

moodle/admin/settings.php?section=sessionhandling


and this was in the config.php:

//added line below for session

$CFG->sessiontimeout = 8 * 60 * 60;  // 1 day in seconds

And yes a user account without many messages loads.

Average of ratings: -
In reply to Will B

Re: How to delete messages?

by Ian L -

I don't have access to mysqltuner. How do I delete session handling in the database or where do i check for this? And what else is affected by this? Is there a way to go through and delete those messages?

Average of ratings: -
In reply to Ian L

Re: How to delete messages?

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Session handling is configured in your config.php file. Database sessions are not the default, so one would think you would know if this has been enabled. You would find a line like...

$CFG->session_handler_class = '\core\session\database';

I doubt that's the problem with your messages.

You could simply truncate the contents of the mdl_message table. Then switch off messaging. TAKE A DATABASE BACKUP FIRST
Average of ratings: -
In reply to Howard Miller

Re: How to delete messages?

by Ian L -

Yes I am in mdl_messages now wondering the best way to find and delete messages left by me. I searched "New message from my name" and also my id but not sure the best way to delete them.

Average of ratings: -
In reply to Ian L

Re: How to delete messages?

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I would search/select on the field 'useridfrom'. I assume you can work out your own userid in Moodle (If you look at your user profile page, it's the xxx id id=xxx in the URL)
Average of ratings: -
In reply to Howard Miller

Re: How to delete messages?

by Ian L -
I'd like to know what is the equivalent of doing this in the database?


Like what action will do the same in phpmyadmin?
Average of ratings: -
In reply to Ian L

Re: How to delete messages?

by Ken Task -
Picture of Particularly helpful Moodlers

mdl_messages table

Field name    Type    Allow nulls?    Key    Default value    Extras
id     bigint(10)     No     Primary     NULL     auto_increment
useridfrom     bigint(10)     No     Indexed     NULL    
conversationid     bigint(10)     No     Indexed     NULL    
subject     longtext     Yes     None     NULL    
fullmessage     longtext     Yes     None     NULL    
fullmessageformat     tinyint(1)     No     None     0    
fullmessagehtml     longtext     Yes     None     NULL    
smallmessage     longtext     Yes     None     NULL    
timecreated     bigint(10)     No     None     NULL    
customdata     longtext     Yes     None     NULL    
fullmessagetrust     tinyint(2)     No     None     0

If one deletes all message from a user by useridfrom, then aren't there now some
orphaned 'conversations' left in the table?

For the sanity of OP's DB, think it best to change settings at URL:


/admin/settings.php?section=messages

Clips of a couple of options located in link above:

Delete read notifications messagingdeletereadnotificationsdelay
default is 1 week

Delete all notifications messagingdeleteallnotificationsdelay
Read and unread notifications can be deleted to save space. How long after a notification is created can it be deleted?
Default is 1 months
Least amount of time is 1 day, other higher options are 3 months, 6 months, never.

There doesn't appear to be any scheduled task for messages cepr for Messages digest mailings

If set to 1 day, let cron job/whatever do it's thing for 2 days - giving it plenty of time to 'clean up', then check to see the situation of messages has improved.

If 'cleaned up', one could reset to 1 months again until the next time it gets to be too much then reset to 1 day again.

One could be rid of the issue by truncating mdl_messages table ... starting over again.

But thought OP can made statements to the effect messages were crucial to his moodle.

My 2 cents.


Average of ratings: -
In reply to Ian L

Re: How to delete messages?

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
This ^^ isn't the error. This is the result of some previous problem killing the database.

I anticipate that you have Debugging turned up full and checked all possible error logs?
Average of ratings: -
In reply to Ian L

Re: How to delete messages?

by Ian L -
For anyone else who has the message problem. I finally got the message drawer to open up by deleting bulk user messages in phpmyadmin. I went from 3454 in the private messages to 1784. And it now works. I can see the messages I've sent from my profile and I can message users by clicking on their profile.

I did something like:

SELECT * FROM `mdl_messages` WHERE useridfrom = 2 ORDER BY `mdl_messages`.`fullmessagehtml` DESC

And then scrolled down and deleted 100-250 at a time. I was thinking of deleting older personal messages too but it's working now.



Average of ratings:Useful (1)
In reply to Ian L

Re: How to delete messages?

by Ken Task -
Picture of Particularly helpful Moodlers

Comment: good ... you are teaching yourself how to fish rather than being 'fed fish'!

'Something like' ... and then 'scrolling to ' and removing x-x at a time ... and how did you do that?   Specifically.   The table is built with a timestamp on each row.   So when you did that, unknowingly, hopefully, you were deleting message exchanges between you and a student.

But ... question: do you now have 'orphaned messages exchange'?   Your contributions to a 'conversation' are gone, but what does the student see of old messages?

No, I don't have a fix for that ... if it's not significant in your opinion, just ignore this ...which am sure you will! smile

For others ... might be a partial solution to get by viewing, but a fix that doesn't leave orphaned messages?


Average of ratings:Useful (1)
In reply to Ken Task

Re: How to delete messages?

by AL Rachels -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
There will almost certainly be something "orphaned" as my latest Moodle 4.0.x db shows 19 different tables related to messaging. Since the above "fix" was only for the mdl_messages table, all the others that may have had links to those deleted entries are now sitting there taking up storage space, and with the potential to cause problems with other messages.

Probably the correct way, but much slower, would be to go into your messages and select, Delete conversation. I am assuming the person who wrote the code, would make it so that it cleaned out all the pertinent tables.
Average of ratings:Useful (1)
In reply to Ian L

Re: How to delete messages?

by Esdras Caleb Oliveira Silva -
Picture of Plugin developers
Hi moodle never delete messages and in some cases this can generate a problem that the professor can never see his messagens again.

I did a plugin that delete conversations that both participants already deleted in a task


But I guess this system need an overhaul. Because the previus one did not had this problems


TO use this in your case you would need and script like this(to delete all your conversations  change the param YOURUSERID)


global $DB;

$individualmessage = \core_message\api::MESSAGE_CONVERSATION_TYPE_INDIVIDUAL;//1

$sql = "SELECT c.id  FROM {message_conversations} c

JOIN {messages} m on m.conversationid =c.id

and c.type={$individualmessage}

JOIN {message_user_actions} ua on ua.messageid=m.id

where  ua.userid={$YOURUSERID}";

$deletedconversation = $DB->get_records_sql($sql);

foreach ($deletedconversation as $conversation) {

\core_message\api::delete_all_conversation_data($conversation->id);

}

Never delete things inside moodledb
Average of ratings:Useful (2)
In reply to Esdras Caleb Oliveira Silva

Re: How to delete messages?

by Helen Foster -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Thanks Esdras for sharing your plugin tool for deleting messaging conversations.

For anyone having problems with lots of old messages, you may wish to vote for adding a feature to delete messages older than a specified date, MDL-18749, or a feature enabling users to delete all messages in one action, MDL-66998.

Average of ratings:Useful (1)
In reply to Helen Foster

Re: How to delete messages?

by Esdras Caleb Oliveira Silva -
Picture of Plugin developers
I will update my plugin to add this option too. Thx
Average of ratings:Useful (1)
In reply to Esdras Caleb Oliveira Silva

Re: How to delete messages?

by Esdras Caleb Oliveira Silva -
Picture of Plugin developers
Hi I updated my plugin in this branch you have the option to delete old messages that was read or all old messages

It will only hard delete when all messages of conversation are deleted
Average of ratings:Useful (1)
In reply to Esdras Caleb Oliveira Silva

Re: How to delete messages?

by Esdras Caleb Oliveira Silva -
Picture of Plugin developers
This can help
Average of ratings:Useful (1)
In reply to Esdras Caleb Oliveira Silva

Re: How to delete messages?

by Andreas Schenkel -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Maybe this problems also happens after updating MariaDB-Server.
You may manually force ANALYSE TABLE by using mysqlcheck -a -Z on the DB-Server.
After that very long lasting database requests finished in milliseconds and the messages could be read again.
See https://moodle.org/mod/forum/discuss.php?d=446712

But MDL-18749 MDL-66998 should be implemented to add a better deletion for messages.
Average of ratings:Useful (1)