Как удалить старые темы (MySQL)

[katz]

Добрый день,

У меня есть форум в котором накопилось миллионы тем за много лет.

Кто нибудь может подсказать как можно удалить темы старее чем например 2 года через консолу MySQL?

 

Спасибо за любой ответ.

[aur3]

Hey, easy solution. This SQL command delete topics older than 2 years.

DELETE FROM forums_topics WHERE start_date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 YEAR));

If your table related with topics change as well, and if start_date different change it as well because this code is based w/ ips 4.7.15 version. 

If you don't really now let me know here I will help you


P.S Always make back-up of your database to keep it safe before doing big changes. 

[katz]

I'm using IPB 3.4 and I tried that code before, simple, just delete old rows where date > than 2 years.

It crashed the database and the forum wasn't working (over 10 million topics, 80GB database).

I found this topic, see the last reply, do you think this is a better solution (they use ID but I can use date) or do you have a better solution:

 

[aur3]

That doesn't change anything so it's the same. 
- You can set cron where it's will be delete every setted time. ( full simple example below) 
- Or you can set schedule for it ( full example below ) 
- Or you can play manually like this ( below example with how many )

Here is cron where its will be deleting every 1 hour php file delete_topics.php , so what's left to do is just add database info in to this and set up cron properly ( to set up cron you can via your hosting provider ( if the provider is not old you can do via dashboard easy..  ) but if the hosting provider do not have this function then you need to make manually

<?php
function deleteOldTopics() {
    $servername = "localhost";
    $username = "";
    $password = "";
    $dbname = "";

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = "DELETE FROM forums_topics WHERE start_date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 YEAR)) LIMIT 100";
        $stmt = $conn->prepare($sql);
        $stmt->execute();

        echo "[Success]";
    } catch(PDOException $e) {
        echo "[ERROR] : " . $e->getMessage();
    }

    $conn = null;
}

deleteOldTopics();

?>

 
Here is example of deleting 100 topics in phpmyadmin:

DELETE FROM forums_topics 
WHERE start_date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 YEAR))
LIMIT 100;

Here is simple (in phpmyadmin)  example of setting up schedule for deleting posts here every 1 hour will be deleting 100 topics ( or how many you want if needed change it ) and you can keep it forever )
I personally have similar schedule for my large databases where I delete over 20k rows every 7 minutes and it works perfectly without any crashes or something like that. 

CREATE EVENT delete_old_topics
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DECLARE rows_affected INT;
    
    START TRANSACTION;
    
    DELETE FROM forums_topics 
    WHERE start_date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 YEAR))
    LIMIT 100;
    
    SELECT ROW_COUNT() INTO rows_affected;
    
    COMMIT;
    
    IF rows_affected > 0 THEN
        SELECT CONCAT(rows_affected, ' topics deleted.') AS Message;
    ELSE
        SELECT 'No topics deleted.' AS Message;
    END IF;
END;

[katz]

Thank you for your reply, it's appreciated.

Just to confirm, if I run this 10 times, for just 1000 posts, nothing will happen to the forum? it won't crash? the numbers won't go mismatch and fatal error?

[aur3]

1 hour ago, katz said:

Thank you for your reply, it's appreciated.

Just to confirm, if I run this 10 times, for just 1000 posts, nothing will happen to the forum? it won't crash? the numbers won't go mismatch and fatal error?

Will be all good, but just to be secure make a copy of database if something happens ( but will be all good ) 
- I prefer to run automatically and there is no issue. 


- But in your case I suggest buy better VPS or Dedicated server where you can store Database of your forum. Because if you using WEB hosting loading times it's rough I assume. 

[katz]

It's a dedicated server just for the database with 128GB DDR3 + 2TB SSD.

What number of rows to delete, per run is safe in your opinion?

The MySQL configuration is untouched (installed by Plesk).

Maybe the timeouts are a config issue? (impossible to repair large table = timeout, extra long delete query = timeout)

By the way, will the DB size reduce automatically after the posts are deleted or is there something I need to do?

[aur3]

Where am having my db so is : "AMD Ryzen 7 Pro 3700 - 8 c / 16 t - 3.6 GHz / 4.4 GHz 128 GB DDR4" and I deleting 20k rows every seven minutes and I feel nothing or users either. 

- So in your case it's depends how well database is optimised.
- So to be clear and safe try from little bit like start from 100 and increase the row number till you see that db can't handle more. ( and of course always have back-up ) 

And if needed you can optimise the table it self(sql code below). 

OPTIMIZE TABLE table_name;

To say exact number is hard because I don't really know what your server is capable are but by increasing your rows number like I said before you can try to increase little by little and check it. 

[katz]

Thank you, if I use the OPTIMIZE on the table, it will still have at least 2 million rows, won't the MySQL timeout (MySQL server has gone away error)?

[katz]

I just checked and I saw the script you sent is for ibf_topics

My ibf_posts table is 50GB - Do I use the same script to delete from there?

Also these tables are over 1GB:

ibf_inline_notifications
ibf_core_share_links_log
ibf_topic_views

[aur3]

You can set up what to delete in each file, or you can change manually tables. 

For OPTIMSE so it could get timeout for you so that means you need to increase some settings. 
Look for options like "max_execution_time", "wait_timeout", and "max_allowed_packet". Increasing these settings allows the server to handle longer-running operations, like table optimization. Doing this can prevent MySQL from timing out and throwing errors during the optimization process.

[katz]

Sorry for the late reply.

I was able to significantly reduce the DB size using one of your queries, I modified it slightly but thank you very much for your help.

It took hours to OPTIMIZE the large tables but it worked.

It's an old version of IPB running on PHP5, do you have an idea on how to make it compatible with newer servers / PHP versions?

[aur3]

2 hours ago, katz said:

Sorry for the late reply.

I was able to significantly reduce the DB size using one of your queries, I modified it slightly but thank you very much for your help.

It took hours to OPTIMIZE the large tables but it worked.

It's an old version of IPB running on PHP5, do you have an idea on how to make it compatible with newer servers / PHP versions?

Upgrade your IPB to IPS And then you will have compatible PHP versions .