Hi, Try changing the type of cdr_id field from varchar to int(11) not null default 0
Regards, Iulia Gavin Henry wrote: > Hi, > > > Can anyoen help debug the stored procedure for the cdrs table? > > mysql> desc acc; > +------------+------------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +------------+------------------+------+-----+---------+----------------+ > | id | int(10) unsigned | NO | PRI | NULL | auto_increment | > | method | char(16) | NO | | | | > | from_tag | char(64) | NO | | | | > | to_tag | char(64) | NO | | | | > | callid | char(64) | NO | MUL | | | > | sip_code | char(3) | NO | | | | > | sip_reason | char(32) | NO | | | | > | time | datetime | NO | | NULL | | > | cdr_id | varchar(32) | YES | | NULL | | > | from_uri | varchar(64) | NO | | NULL | | > | to_uri | varchar(64) | NO | | NULL | | > +------------+------------------+------+-----+---------+----------------+ > 11 rows in set (0.00 sec) > > mysql> desc cdrs; > +-----------------+------------------+------+-----+---------------------+----------------+ > | Field | Type | Null | Key | Default > | Extra | > +-----------------+------------------+------+-----+---------------------+----------------+ > | cdr_id | bigint(20) | NO | PRI | NULL > | auto_increment | > | call_start_time | datetime | NO | | 0000-00-00 > 00:00:00 | | > | duration | int(10) unsigned | NO | | 0 > | | > | sip_call_id | varchar(128) | NO | | > | | > | sip_from_tag | varchar(128) | NO | | > | | > | sip_to_tag | varchar(128) | NO | | > | | > | created | datetime | NO | | 0000-00-00 > 00:00:00 | | > +-----------------+------------------+------+-----+---------------------+----------------+ > 7 rows in set (0.00 sec) > > We are using the > /var/www/opensips-cp/web/tools/cdrviewer/opensips_cdrs_1_5.sql from > version 2.0 which has: > > USE opensips ; > DROP PROCEDURE IF EXISTS `opensips_cdrs_1_5` ; > DELIMITER // > CREATE PROCEDURE opensips_cdrs_1_5() > BEGIN > DECLARE done INT DEFAULT 0; > DECLARE bye_record INT DEFAULT 0; > DECLARE v_callid,v_from_tag, v_to_tag VARCHAR(64); > DECLARE v_inv_time, v_bye_time DATETIME; > DECLARE inv_cursor CURSOR FOR SELECT time, callid, from_tag, to_tag > FROM opensips.acc where method='INVITE' and cdr_id='0'; > DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; > OPEN inv_cursor; > REPEAT > FETCH inv_cursor INTO v_inv_time, v_callid, v_from_tag, v_to_tag; > IF NOT done THEN > SET bye_record = 0; > SELECT 1, time INTO bye_record, v_bye_time FROM opensips.acc > WHERE method='BYE' AND callid=v_callid AND ((from_tag=v_from_tag AND > to_tag=v_to_tag) OR (from_tag=v_to_tag AND to_tag=v_from_tag)) ORDER > BY time ASC LIMIT 1; > IF bye_record = 1 THEN > INSERT INTO cdrs > (call_start_time,duration,sip_call_id,sip_from_tag,sip_to_tag,created) > VALUES > (v_inv_time,UNIX_TIMESTAMP(v_bye_time)-UNIX_TIMESTAMP(v_inv_time),v_callid,v_from_tag,v_to_tag,NOW()); > UPDATE acc SET cdr_id=last_insert_id() WHERE callid=v_callid > AND ( (from_tag=v_from_tag AND to_tag=v_to_tag) OR (from_tag=v_to_tag > AND to_tag=v_from_tag)); > END IF; > SET done = 0; > END IF; > UNTIL done END REPEAT; > END > // > DELIMITER ; > > > There is data with BYE and INVITE in our acc table. > > Thanks. > > _______________________________________________ > Users mailing list > Users@lists.opensips.org > http://lists.opensips.org/cgi-bin/mailman/listinfo/users > > _______________________________________________ Users mailing list Users@lists.opensips.org http://lists.opensips.org/cgi-bin/mailman/listinfo/users