XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Community Support forums / 
  3. XOOPS general usage questions 
  4.  / Purging notifications and PMs from the database: How
Register
1
MadFish
Purging notifications and PMs from the database: How

  • 2007/3/14 4:43

  • MadFish

  • Friend of XOOPS

  • Posts: 1056

  • Since: 2003/9/27


I need to clean out all the old user PMs and notification messages. They currently make up about 60% of my database and I need to reduce it to a more manageable size.

I have experimented with deleting the contents of two tables on a test site: priv_msgs and xoopsnotifications. It seems to work.

I am wondering if anyone else has tried this? Will this cause any unexpected problems?

Thanks
2
irmtfan
Re: Purging notifications and PMs from the database: How

  • 2007/3/14 5:34

  • irmtfan

  • Module Developer

  • Posts: 3419

  • Since: 2003/12/7


at least about pm i do it without any problem for 2 years.
i use a very simple script:
define("user", "dbuser");
define("pass", "dbpass");
define("host", "localhost");
define("db", "dbname");


$cxn = mysql_connect(host ,user, pass);
mysql_select_db(db);

$delete_time = time() - ( 30 * 24 * 60 * 60 );  // 30 days/month * 24 hrs/day
// * 60 minutes/hour * 60 seconds/min.

$sql = "DELETE from `xoops_priv_msgs` WHERE `read_msg` = '0' AND `msg_time` < '$delete_time'";
//This script would delete everything older than 30 days that has been not read.

$result = mysql_query($sql, $cxn);
?>

and run it every day with a cron.

MP manager can purge pm too but i dont use it because i need to do it automatically every day.

and about notification its in the Herve todo list for the future version of his new module xoopscare.
3
MadFish
Re: Purging notifications and PMs from the database: How

  • 2007/3/14 8:39

  • MadFish

  • Friend of XOOPS

  • Posts: 1056

  • Since: 2003/9/27


Thanks heaps for that script I'll give it a go :)
4
MadFish
Re: Purging notifications and PMs from the database: How

  • 2007/3/15 4:14

  • MadFish

  • Friend of XOOPS

  • Posts: 1056

  • Since: 2003/9/27


Just took another look at my database - it turns out that PM's make up 83% of the total database size.

Purging them will reduce my database size from 88.4MB to just 14.5 and make backup considerably easier.
5
irmtfan
Re: Purging notifications and PMs from the database: How

  • 2007/3/15 4:55

  • irmtfan

  • Module Developer

  • Posts: 3419

  • Since: 2003/12/7


just be careful using this script inside wwwroot because of db informations.
i upload it outside wwwroot and run it with a cron
6
MadFish
Re: Purging notifications and PMs from the database: How

  • 2007/3/26 0:53

  • MadFish

  • Friend of XOOPS

  • Posts: 1056

  • Since: 2003/9/27


Just to follow up on this: It works. 92,000 PMs deleted! Database is now a much more manageable size.
7
jdseymour
Re: Purging notifications and PMs from the database: How

Thanks for this irmtfan,

Just tested this on my site, worked perfectly after I changed to my custom prefix.

Now will try to clean up a site with over 5 million PMs. Thansk again.
8
rpilney
Re: Purging notifications and PMs from the database: How

  • 2007/7/10 20:52

  • rpilney

  • Just popping in

  • Posts: 76

  • Since: 2006/1/25


where would I or how do I use this script? This is what I need but not sure how to go about doing it.

Thanks!

Quote:

irmtfan wrote:
at least about pm i do it without any problem for 2 years.
i use a very simple script:
define("user", "dbuser");
define("pass", "dbpass");
define("host", "localhost");
define("db", "dbname");


$cxn = mysql_connect(host ,user, pass);
mysql_select_db(db);

$delete_time = time() - ( 30 * 24 * 60 * 60 );  // 30 days/month * 24 hrs/day
// * 60 minutes/hour * 60 seconds/min.

$sql = "DELETE from `xoops_priv_msgs` WHERE `read_msg` = '0' AND `msg_time` < '$delete_time'";
//This script would delete everything older than 30 days that has been not read.

$result = mysql_query($sql, $cxn);
?>

and run it every day with a cron.

MP manager can purge pm too but i dont use it because i need to do it automatically every day.

and about notification its in the Herve todo list for the future version of his new module xoopscare.
9
Cuidiu
Re: Purging notifications and PMs from the database: How

  • 2007/7/11 6:49

  • Cuidiu

  • Quite a regular

  • Posts: 358

  • Since: 2006/4/23


I am interested as well. I'd also like to know if we can use it in the XOOPS Care module by adding the script to the "Execute Php code" area.

For the following, I assume we add our own information?
define("user", "dbuser");
define("pass", "dbpass");
define("host", "localhost");
define("db", "dbname");

Would it be "dbuser", "dbpass", "localhost", "dbname" that needs modifying? Other than that, anything else?

Thanks...

Quote:
where would I or how do I use this script? This is what I need but not sure how to go about doing it.
[size=x-small]Working sites:
XOOPS 2.0.16 PHP 5.2.2, MySQL 5.0.24a-standard-log, Apache/2.0.54 (Unix)
XOOPS 2.2.4, PHP 4.3.10, MySQL 3.23.58, Apache/1.3.33 (Unix)[/size]
10
MadFish
Re: Purging notifications and PMs from the database: How

  • 2007/7/11 6:57

  • MadFish

  • Friend of XOOPS

  • Posts: 1056

  • Since: 2003/9/27


Quote:
where would I or how do I use this script? This is what I need but not sure how to go about doing it.


If you have cpanel on your hosting account, look for a section called 'cron jobs', you can probably run it from there. I am pretty sure you could run it with xoopscare, but haven't tried yet.

Otherwise, you can achieve the same thing manually by deleting the contents of the relevant tables, as noted above. But please don't mess around with your database until you have made a backup!
(1) 2 »

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Who's Online

388 user(s) are online (283 user(s) are browsing Support Forums)

Members: 0

Guests: 388

more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits