Latest Announcemnent
New thread

DLE Huge database deleting outdated post problem

Status
Not open for further replies.
D

downfreak

Active Member
94
2010
0
0
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
 
11 comments
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...
 
Loonycgb2 said:
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...
Click to expand...

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
 
[strike]You wanted to delete the news by last posted news or first posted news first?[/strike]

just coded a quick script to do the task:

PHP: 
<?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:
Code: 
1 * * * * /filepathhere.php

This will set the cron to run that script every 1 minute
Hope it helps 8-)
 
Last edited:
soft2050 said:
[strike]You wanted to delete the news by last posted news or first posted news first?[/strike]

just coded a quick script to do the task:

PHP: 
<?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:
Code: 
1 * * * * /filepathhere.php
This will set the cron to run that script every 1 minute
Hope it helps 8-)
Click to expand...

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
 
downfreak said:
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
Click to expand...
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 :sun:
 
PHP: 
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

PHP: 
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.

PHP: 
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.
 
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);

?>
Click to expand...
but its not working...
 
just run a query in phpmyadmin

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

PHP: 
 DELETE FROM `dle_post` WHERE id < 1000

If it times out it will continue in the background, dont re-start mysql
 
ah yeah i set the proper ID. now everything is crashed.. lol i cant access the website its saying internal error...
or i need to wait...

thats why i wanted to do it 1 by 1..
 
Status
Not open for further replies.
Back
Top