Page 1 of 2

MySQL (Auto Delete Old Rows)

Posted: Wed Dec 07, 2011 7:24 pm
by Hermit TL
Lets say I have a table with a column id and lastuserlogin column and on every login a user name is inserted into the table.
Such as $query = "INSERT INTO table (lastuserlogin) VALUES ('$username')";

Is it possible to add something like [if #row>100 DELETE oldestrows until #rows==100] in the query?

Re: MySQL (Auto Delete Old Rows)

Posted: Wed Dec 07, 2011 9:58 pm
by twinedev
Not a simply as you put it. how would it know what to delete?

Usually when auto purging data, I have a field that is timestamp and then once a day (or more, depending on activity), execute a PHP script that does the following to remove anything over 24 hours old.

Code: Select all

DELETE FROM `table` WHERE `timestampfield` < DATE_SUB(NOW(), INTERVAL 24 HOUR)
Note, if you are running the above, and the cron is set to run just once a day, keep in mind that right before the cron runs, you can have entries almost 48 hours old.

-Greg

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 10:29 am
by Hermit TL
how would it know what to delete?
By examining the id column (I have a primary key set on column id w/ auto increment)

I'd really rather not use cron jobs. I know how to code this into the PHP script, but I just thought I would check if MySQL had a way of doing this within a query. (Since it usually does after I program lines and lines of code to do the same thing a well constructed query could do.)

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 10:52 am
by twinedev
Well then you could set up a trigger on the server, so that every time a record is inserted, it does the following (you'd need to look how to program the stored procedure and trigger, as I don't do them enough to give it off of the top of my head:

DELETE FROM `table` WHERE `id` < (SELECT `id` FROM `table` ORDER BY `id` DESC LIMIT 100,1)

This is depending on that there will always be a 100th record. Since you are doing it as a stored procedure, then you would be better to break it up into a separate SELECT statement, and then only do the DELETE statement if the result is numeric (or some other type check to make sure there was a 100th row)

-Greg

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 11:52 am
by Hermit TL
i'm not too good with MySQL so the following is probably wrong somewhere. What do you think of something like this?

Code: Select all

DELETE FROM `table` WHERE `id` < (SELECT `id` FROM `table` ORDER BY `id` DESC LIMIT (COUNT(`id`) - (COUNT(`id`) - 100)), 1)

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 12:16 pm
by twinedev
Well, COUNT(id) - (COUNT(id) - 100) will always give you 100, so there is no need to do that.

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 12:37 pm
by Hermit TL
Well, COUNT(id) - (COUNT(id) - 100) will always give you 100, so there is no need to do that.
I don't think my math is wrong:

Code: Select all

z = (r-(r-100))
r = 50
(r-(r-100)) == (50 - (50-100)) == (50 - (-50)) == -100
r = 100
(r-(r-100)) == (100 - (100-100)) == (100 - (000)) == 100
r = 200
(r-(r-100)) == (200 - (200-100)) == (200 - (100)) == 100
r = 300
(r-(r-100)) == (300 - (300-100)) == (300 - (200)) == 100
Maybe... Something like this? (I don't know SQL :( )

Code: Select all

DELETE FROM `table`
WHERE `id` <
     (SELECT `id`
     FROM `table`
     ORDER BY `id` DESC
     LIMIT IF(COUNT(`id`) >= 100, (COUNT(`id`) - (COUNT(`id`) - 100)), IF(COUNT(`id`) < 100, 0, (`id` - 1))), 1)

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 12:40 pm
by Celauran
twinedev wrote:Well, COUNT(id) - (COUNT(id) - 100) will always give you 100, so there is no need to do that.
This is right.
Hermit TL wrote:I don't think my math is wrong:

Code: Select all

(r-(r-100)) == (50 - (50-100)) == (50 - (-50)) == -100
Your math is wrong. 50 - (-50) = 100

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 12:46 pm
by Hermit TL
Hermit TL wrote:I don't think my math is wrong:
Syntax: [ Download ] [ Hide ]
Syntax: [ Download ] [ Show ]

1. (r-(r-100)) == (50 - (50-100)) == (50 - (-50)) == -100


Your math is wrong. 50 - (-50) = 100
So it is... I'm starting over. Break time!

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 1:24 pm
by Hermit TL
I have no idea what I'm doing.
I'm going to try this, and... see what happens.

Code: Select all

DELETE * FROM `table` WHERE `id` < ((((SELECT COUNT(`id`) FROM table`) - 100) + (SELECT `id` FROM `table` ORDER BY ASC LIMIT 1)) + 1);

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 1:34 pm
by Hermit TL

Code: Select all

DELETE * FROM `table`
WHERE `id` < ((((SELECT COUNT(`id`) FROM TABLE `table` LIMIT 1) - 100) + (SELECT `id` FROM `table` ORDER BY `id` ASC LIMIT 1)) + 1);
It won't even run.

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 1:39 pm
by twinedev
So what happens when you try the original one I gave you?

Code: Select all

DELETE FROM `table` WHERE `id` < (SELECT `id` FROM `table` ORDER BY `id` DESC LIMIT 100,1)
DELETE * FROM `table`
Is invalid syntax, there should not be * in there.

-Greg

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 2:08 pm
by Hermit TL
Thanks, it's actually running now. However. I'm getting Error 1093: "You can't specify target table `table` for update in FROM clause."
Your code returns the same error as well.
What does it want?

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 2:19 pm
by twinedev
I was wondering if it might complain, the issue is that it doesn't like you selecting data from same table you are trying to delete from (as one action may affect another). IMO shouldn't matter you'd think it is selecting info then deleting, but it gets picky (for valid reasons behind the scenes I am sure). So with that you would probably need to save it as a stored procedure on the server that does them in two steps.

Re: MySQL (Auto Delete Old Rows)

Posted: Thu Dec 08, 2011 2:43 pm
by twinedev
So, like I said, don't do it much, but something like this might work:

Code: Select all

DELIMITER $$
CREATE PROCEDURE `TruncateTable`()
BEGIN
	DECLARE tr INT DEFAULT 0;
	SELECT COUNT(`id`) INTO tr FROM `table`;
	IF tr > 100 THEN
		SELECT `id` INTO tr FROM `table` ORDER BY `id` DESC LIMIT 100,1;
		DELETE FROM `table` WHERE `id` < tr;
	END IF;
END$$
DELIMITER ;
Then when you want to run it, you just need to do CALL TruncateTable(); I think

Definitely would need to be tested