MySQL trigger : is it possible to delete rows if table become too large ?

Question

Hi,

When inserting a new row in a table T, I would like to check if the table is larger than a certain threshold, and if it is the case delete the oldest record (creating some kind of FIFO in the end).

I thought I could simply make a trigger, but apparently MySQL doesn't allow the modification of the table on which we are actually inserting :

Code: 1442  Msg: Can't update table 'amoreAgentTST01' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Here is the trigger I tried :

Delimiter $$ 
CREATE TRIGGER test 
       AFTER INSERT ON amoreAgentTST01
       FOR EACH ROW
BEGIN
    DECLARE table_size INTEGER;
    DECLARE new_row_size INTEGER;
    DECLARE threshold INTEGER;
    DECLARE max_update_time TIMESTAMP;

SELECT SUM(OCTET_LENGTH(data)) INTO table_size FROM amoreAgentTST01;
SELECT OCTET_LENGTH(NEW.data) INTO new_row_size;
SELECT 500000 INTO threshold;
select max(updatetime) INTO max_update_time  from amoreAgentTST01;

IF (table_size+new_row_size) > threshold THEN
   DELETE FROM amoreAgentTST01 WHERE max_update_time = updatetime; -- and check if not current
END IF;
END$$
delimiter ;

Do you have any idea on how to do this within the database ?

Or it is clearly something to be done in my program ?

Answer 1

Ideally you should have a dedicated archive strategy in a separate process that runs at off-peak times.

You could implement this either as a scheduled stored procedure (yuck) or an additional background worker thread within your application server, or a totally separate application service. This would be a good place to put other regular housekeeping jobs.

This has a few benefits. Apart from avoiding the trigger issue you're seeing, you should consider the performance implications of anything happening in a trigger. If you do many inserts, that trigger will do that work and effectively half the performance, not to mention the lock contention that will arise as other processes try to access the same table.

A separate process that does housekeeping work minimises lock contention, and allows the work to be carried out as a high-performance bulk operation, in a transaction.

One last thing - you should possibly consider archiving records to another table or database, rather than deleting them.