DLE Huge database deleting outdated post problem

[downfreak]

Hi,
I am using DLe on my website and the sql database is now 6GB with more than 1 million posts.

I am having a problem when trying to delete outdated posts or when trying to delete about 100 posts at once the server keep sending "INTERNAL ERROR" message.. and i cant do anything..

I can only delete posts if i want to do 1 by 1 which will take me for ever to do.

Anyone know any alternative for it?

What i was thinking is if any script than exist than i can run and it will delete 1 post per 30 seconds starting from the oldest one? there i can get about (30* 2 * 60) = 3600 posts deleted per hour


cheers

downfreak Reviewed by downfreak on 9th Oct 2011. DLE Huge database deleting outdated post problem Hi, I am using DLe on my website and the sql database is now 6GB with more than 1 million posts. I am having a problem when trying to delete outdated posts or when trying to delete about 100 posts at once the server keep sending "INTERNAL ERROR" message.. and i cant do anything.. I can only delete posts if i want to do 1 by 1 which will take me for ever to do. Anyone know any alternative for it? What i was thinking is if any script than exist than i can run and it will delete 1 Rating: 5

[Loonycgb2]

a script will have the same issue.. this has to do with your mysql database...

Best thing to do is find out how dle deletes the tables from mysql... if its all coming from just one table then go into phpmyadmin and change it so it shows the oldest table and delete tables from there...

[downfreak]

a script will have the same issue.. this has to do with your mysql database...

Best thing to do is find out how dle deletes the tables from mysql... if its all coming from just one table then go into phpmyadmin and change it so it shows the oldest table and delete tables from there...

If the scrip can execute itself every 30 seconds to delete only 1 at a time will not be a problem as it allow me to delete 1 - 2 posts but when i try to delete more than 5, i just keep on getting the internal error message..

Also am not a php expert so a bit hard for me to find out where all the tables..
i know it should be in editnews.php but dont know the rest lol

[soft2050]

You wanted to delete the news by last posted news or first posted news first?

just coded a quick script to do the task:

<?php
/**
 * Developer: Soft2050
 * IDE Used: JetBrains PhpStorm.
 * Date: 10/9/11
 * Time: 2:07 PM
 */

$dbhost = ''; // Enter your db host here
$dbuser = ''; // Enter db username here
$dbpass = ''; // Enter db password here'
$dbname = ''; // Enter your db name here

$connection = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysqli_error());

mysql_select_db($dbname, $connection);

mysql_query("DELETE FROM `dle_post` LIMIT 1");

mysql_close($connection);

?>

Set that to cron job and it will take care of the rest!

Execute this in ssh:

1 * * * * /filepathhere.php

This will set the cron to run that script every 1 minute
Hope it helps

[downfreak]

You wanted to delete the news by last posted news or first posted news first?

just coded a quick script to do the task:

<?php
/**
 * Developer: Soft2050
 * IDE Used: JetBrains PhpStorm.
 * Date: 10/9/11
 * Time: 2:07 PM
 */

$dbhost = ''; // Enter your db host here
$dbuser = ''; // Enter db username here
$dbpass = ''; // Enter db password here'
$dbname = ''; // Enter your db name here

$connection = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysqli_error());

mysql_select_db($dbname, $connection);

mysql_query("DELETE FROM `dle_post` LIMIT 1");

mysql_close($connection);

?>

Set that to cron job and it will take care of the rest!

Execute this in ssh:

1 * * * * /filepathhere.php

This will set the cron to run that script every 1 minute
Hope it helps

hey soft2050, how you been mate?
haven't seen you for ages?

by the way from the code that u just coded, is it going to delete the post which was first posted in terms of time when it was posted?

also it it going to delete infos attributed to the post like number of viewings and comments?

cheers mate

[soft2050]

hey soft2050, how you been mate?
haven't seen you for ages?

by the way from the code that u just coded, is it going to delete the post which was first posted in terms of time when it was posted?

also it it going to delete infos attributed to the post like number of viewings and comments?

cheers mate

I am fine dude!
The code is going to delete the post which was posted first

It will delete the info except the total news shown in user profile! It will still be a bug there! But you can fix it by DLE Database Optimization in admin cp to fix those bugs

[Mr Happy]

mysql_query("DELETE FROM `dle_post` LIMIT 1"); 

You are running a script every single minute to delete one topic. This doesn't make sense. Put it on a 5 minute cron script and change the LIMIT to 5 which will be far more efficient on the server.

You should really order them as well by time. Yes it should remove older posts first but you want to make sure. I don't use DLE so don't know the name of the time column but you'll need something like this

mysql_query("DELETE FROM `dle_post` ORDER BY time ASC LIMIT 5"); 

the time needs to be changed to whatever the time column is called. Another thing you could do to prevent posts which are ranked high in Google from being removed which could seriously affect your traffic is to only DELETE posts with views lower than eg 20.

mysql_query("DELETE FROM `dle_post` WHERE views < 20 ORDER BY time ASC LIMIT 5"); 

Again you need to check the correct name for the views column.

[downfreak]

i tried to run

<?php
/**
* Developer: Soft2050
* IDE Used: JetBrains PhpStorm.
* Date: 10/9/11
* Time: 2:07 PM
*/

$dbhost = ''; // Enter your db host here
$dbuser = ''; // Enter db username here
$dbpass = ''; // Enter db password here'
$dbname = ''; // Enter your db name here

$connection = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysqli_error());

mysql_select_db($dbname, $connection);

mysql_query("DELETE FROM `dle_post` LIMIT 1");

mysql_close($connection);

?>

but its not working...

[downfreak]

Any "Respected Developer" was to work on this?
cheers

[Gavo]

just run a query in phpmyadmin

delete all posts below ID 1000, adjust the number to suit you

 DELETE FROM `dle_post` WHERE id < 1000 

If it times out it will continue in the background, dont re-start mysql