I wanted to make rotation inside my logs table to store last 100 rows only, and decided to use triggers, as in

create trigger rotate_log 
after insert on logs
for each row begin 
  cnt int; 
  set cnt = (select count(*) from logs); 
  if cnt >100  then 
    delete from logs limit 1; 
  end if;

But it doesn't works because of deadlock caused by insert statement. So how to rewrite this with less work?

I am using mysql Ver 15.1 Distrib 10.1.35-MariaDB, for FreeBSD11.2

  • suggest if the requirement to keep logs small, an event/stored procedure to delete records on a periodic basis and keep the insertion of log records quick. – danblack Sep 25 '18 at 19:38

You cannot alter the same table in trigger.

Use rotate (update the oldest record) instead of add and delete. Insert 100 records into your table (if there is no enought records in it) with their timestamp in far past and use

SET field1=value1, field2=value2 -- , ...
ORDER BY record_timestamp ASC

The records count will be always unchanged. To increase it simply add the amount of records you need with timestamp in far past, to decrease - delete the amount of oldest records you do not need.

And you do not need in trigger at all...

| improve this answer | |
  • Okay, but what if I want it to have 100'000 rows, and what to do before it fills up those 100 000's? – amelie Sep 25 '18 at 10:26
  • Inserting 100k "empty" rows (with timestamp set to any '2000-01-01') is fast enough. If you're afraid it is too long you may insert 100 times by 1k records each... – Akina Sep 25 '18 at 11:11
  • PS. These "starting" records (with a fake past timestamp) are inserted into the table immediately after table creation, before real table usage. – Akina Sep 25 '18 at 11:18

At this point, I decided it is better to write a procedure instead of trigger:

create PROCEDURE log_ins(usr varchar(50),msg text) begin
select count(*) into @cnt from logs;
if (@cnt > 10) then
delete from logs limit 1;
end if;
insert into logs(user, message) values (usr, msg);
end //

It checks whether it exceeds limit, and if yes, able to delte a row from table, and outside if, able to insert passed values to the table.

| improve this answer | |
  • If the records count reaches the limit you delete one random record - maybe the last. I think it is not safe. Add proper ORDER BY to DELETE query. And you do not need in @cnt variable, use simple if (select count(*) from logs) > 10 then. – Akina Sep 26 '18 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.