trigger works on 5.0.14, but not on 5.0.21
Hi there!
i install centos 4 which comes with mysql 4.1, Then i update to mysql 5.
i got this trigger, which used to work great on 5.0.13 and 5.0.14, but now that i've updated to 5.0.21, cant insert values into the table which has the trigger!
here's the error from the mysql prompt:
mysql> insert into cdr set src="200", dst="011582129770354", billsec=30;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> drop trigger cdrdata;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: asteriskcdrdb
Query OK, 0 rows affected (0.00 sec)
mysql> insert into cdr set src="200", dst="011582129770354", billsec=30;
Query OK, 1 rows affected (0.00 sec)
here's the error(from the asterisk log):
May 9 18:57:10 DEBUG[3613] cdr_addon_mysql.c: cdr_mysql: SQL command as follows: INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid) VALUES ('2006-05-09 18:56:59','\"Telefono IP\" <207>','207','201','from-internal', 'SIP/207-f614','SIP/201-b158','Dial','SIP/201|15|tr',11,9,'ANSWERED',3,'','1147215419.12')
May 9 18:57:10 ERROR[3613] cdr_addon_mysql.c: mysql_cdr: Failed to insert into database: (2013) Lost connection to MySQL server during queryMay 9 18:57:10 DEBUG[3613] chan_sip.c: update_call_counter(207) - decrement call limit counter
the trigger is fine (codewise), and after i drop the trigger, it works perfectly fine.
here's the trigger:
DELIMITER $
CREATE TRIGGER cdrdata AFTER INSERT ON cdr
FOR EACH ROW
BEGIN
DELETE FROM temp;
INSERT INTO temp (src_t,dst_t,billsec_t,sizepref_t,date_t) SELECT DISTINCT new.s rc, CAST(new.dst AS decimal), new.billsec DIV 60 + 1, 0, UNIX_TIMESTAMP(NOW()) F ROM cdr WHERE new.billsec>=3;
SET @country:=(SELECT country FROM codes);
SET @area:=(SELECT area FROM codes);
SET @ldi:=(CONCAT(@country,@area));
SET @dst_cdr:=(SELECT DISTINCT new.dst FROM cdr);
SET @dst_temp:=(SELECT dst_t from temp);
SET @zeroes:=(SELECT LEFT(@dst_cdr,2));
SET @comp1:=(SELECT @zeroes LIKE '0_');
SET @comp0:=(SELECT @zeroes LIKE '00');
IF (@comp0=0) THEN
IF (@comp1=0) THEN
UPDATE temp SET dst_t=(CONCAT(@ldi,@dst_temp));
ELSE UPDATE temp SET dst_t=(CONCAT(@country,@dst_temp));
END IF;
END IF;
SET @v=(SELECT src_t FROM temp);
IF @v IS NOT NULL THEN
BEGIN
CALL tempupdate();
CALL webupdate();
END;
END IF;
END $
DELIMITER ;
any thoughts?
thanks in advance!
Edited 2 time(s). Last edit at 05/10/2006 01:15PM by Jose Javier.