MySQL (Auto Delete Old Rows)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

MySQL (Auto Delete Old Rows)

Post 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?
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: MySQL (Auto Delete Old Rows)

Post 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
Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

Re: MySQL (Auto Delete Old Rows)

Post 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.)
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: MySQL (Auto Delete Old Rows)

Post 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
Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

Re: MySQL (Auto Delete Old Rows)

Post 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)
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: MySQL (Auto Delete Old Rows)

Post by twinedev »

Well, COUNT(id) - (COUNT(id) - 100) will always give you 100, so there is no need to do that.
Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

Re: MySQL (Auto Delete Old Rows)

Post 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)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: MySQL (Auto Delete Old Rows)

Post 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
Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

Re: MySQL (Auto Delete Old Rows)

Post 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!
Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

Re: MySQL (Auto Delete Old Rows)

Post 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);
Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

Re: MySQL (Auto Delete Old Rows)

Post 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.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: MySQL (Auto Delete Old Rows)

Post 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
Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

Re: MySQL (Auto Delete Old Rows)

Post 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?
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: MySQL (Auto Delete Old Rows)

Post 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.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: MySQL (Auto Delete Old Rows)

Post 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
Post Reply