Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have some trigger to save call records as blob into database. I want to add some extra function that will execute each time with trigger and will help to keep database in some size limits: function must do something like that:

Must calculate blob files' sum. while it's larger then given value, delete oldest record from table.

I don't know what I'm doing wrong. Please take a look 

WHILE ((select sum(OCTET_LENGTH(recordfile))/1000000 from callrecords)>0,1) 
DO
BEGIN
DELETE FROM callrecords ORDER BY id ASC LIMIT 1;
END;  
END WHILE;

Here is how database looks like

-- ----------------------------
-- Table structure for `callrecords`
-- ----------------------------
CREATE TABLE `callrecords` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `callerid` int(11) NOT NULL,
  `extension` int(11) NOT NULL,
  `calldate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `recordfile` longblob,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=350 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Thx in advance.

share|improve this question
So, what happpens? Does your trigger run? Does it delete records? Do you get an error? What is the error? – Jocelyn Mar 13 at 16:48

1 Answer

up vote 0 down vote

You should select the blob file size into a variable outside of the loop, and then update the variable by re-running the select after each delete.

Code example based on your initial attempt:

declare v_size int;
...
-- initialize the variable
select sum(OCTET_LENGTH(recordfile)) 
into v_size
from callrecords;

while (v_size > 1000000)
do
  DELETE FROM callrecords ORDER BY id ASC LIMIT 1;

  -- update the variable
  select sum(OCTET_LENGTH(recordfile))
  into v_size
  from callrecords;

end while;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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