Empty a MySQL table hourly

Question

I have a chat script written on PHP Ajax and MySQL. It keeps the message logged on a table "usermsg".

My problem is that I have only a MySQL db of 100mb and I can't keep all the messages, as I have 55 users and my db quota is not enough to stock all and when it's full my chat stops automatically. So I perform truncat from PhpMyAdmin each time I feel that it is almost full. What I want to do is somehow perform truncat hourly so it can clear my table every hour.

Can somebody help with a PHP script or something to do that? Thanks.

Something that will help is making sure the database is designed properly, so it takes up as little space as possible. Can you post the table schema? — John V., Oct 24 '13 at 02:22

Tamil Selvan C · Answer 1 · 2013-10-24T02:38:47.803

Use cronjobs to execute the php for an hourly or use mysql events to achieve this task

Method 1:

create a php and do cron jobs for it

<?php
// write database connection code
mysqli_query("TRUNCATE TABLE tablename");
?>

and call this php in cron

php /path/to php file

Method 2:

create mysql EVENT. give privilege to accesss event for table

SET GLOBAL EVENT_SCHEDULER = ON;
CREATE EVENT e_truncate
    ON SCHEDULE
      EVERY 1 HOUR
    DO
      TRUNCATE TABLE tablename

score 3 · Answer 2 · answered Oct 24 '13 at 02:31

I feel doing it hourly ignores a simpler option.

Another option is to remove the oldest entry every time a new entry is made. This removes the need for a cron job which dumps the entire table, as that could interrupt the chat by removing entries made only moments ago that are still relevant to the chat.

So in your "addMessage" sql, simply remove the oldest entry before you submit the new one.

score 3 · Answer 3 · answered Oct 24 '13 at 02:38

You can use event scheduler for this.

In phpmyadmin run following queries.

Enable it using

SET GLOBAL EVENT_SCHEDULER = ON;

And create an event like this

  CREATE EVENT delete_messages

    ON SCHEDULE EVERY 3 DAY

    DO

    TRUNCATE TABLE 'tableName';

Here's a nice tutorial on this.

Phpmyadmin event scheduler

Empty a MySQL table hourly

3 Answers3

Linked