Cann't Store cdr information in MySql database

[smbakhtiar]

Hi

For testing i have to configure two test account using sip protocol with my 127.0.0.1. What is happening when i call to account1 to account2 then the call information is generating but i cannot store it in mysql db . So here is my configuration what i made in below:

1.create 2 account in regfile.conf
2.in register.conf changes are
     [general]
    expires=30
   stoperror=busy
   user.auth=on
   user.register=on
  user.unregister=on
  engine.timer=on
call.preroute=on
call.route=on
call.cdr=om

[default]
priority=50
account=yate   
[call.cdr]
 -- Usage of the function in register.conf, only difference in cdr_insert/update and cdr_finalize is the last value for "ended"
cdr_insert=SELECT cdr_upsert(${time}, '${billid}', '${chan}', '${address}', '${caller}', '${callername}', '${called}', ${billtime}, ${ringtime}, ${duration}, '${direction}', '${status}', '${reason}', false);
cdr_update=SELECT cdr_upsert(${time}, '${billid}', '${chan}', '${address}', '${caller}', '${callername}', '${called}', ${billtime}, ${ringtime}, ${duration}, '${direction}', '${status}', '${reason}', false);
cdr_finalize=SELECT cdr_upsert(${time}, '${billid}', '${chan}', '${address}', '${caller}', '${callername}', '${called}', ${billtime}, ${ringtime}, ${duration}, '${direction}', '${status}', '${reason}', true);

also create mysql table using below way

CREATE  TABLE IF NOT EXISTS `cdr` (
  `idcdr` BIGINT NULL AUTO_INCREMENT,
  `sqltime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `yatetime` INT(11) NULL,
  `billid` VARCHAR(55) NOT NULL,
  `chan` VARCHAR(20) NULL,
  `address` CHAR(15) NULL,
  `addressport` CHAR(5) NULL,
  `caller` VARCHAR(55) NULL,
  `callername` VARCHAR(255) NULL,
  `called` VARCHAR(55) NULL,
  `billtime` FLOAT NULL,
  `ringtime` FLOAT NULL,
  `duration` FLOAT NULL,
  `direction` ENUM('incoming','outgoing') NULL,
  `status` VARCHAR(11) NULL,
  `reason` VARCHAR(55) NULL,
  `ended` TINYINT(1) NULL,
PRIMARY KEY (`idcdr`),
INDEX `ix_cdr_sqltime` (`sqltime` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- A primary key to prevent duplicates on billid and chan
CREATE UNIQUE INDEX uq_cdr_billid_chan ON cdr (billid, chan);


-- A function to use for CDR operations
delimiter $$
CREATE FUNCTION  cdr_upsert (p_yatetime INT(11), p_billid varchar(20), p_chan varchar(20), p_address varchar(21), p_caller varchar(55), p_callername varchar(255), p_called varchar(55), p_billtime float, p_ringtime float,
  p_duration float, p_direction enum('incoming','outgoing'), p_status varchar(11), p_reason varchar(55), p_ended tinyint(1))  RETURNS SMALLINT(1)
BEGIN

  DECLARE i_ended TINYINT(1);
  DECLARE i_address_port VARCHAR(5);

  -- extracting IP and Port
  SET i_address_port = (SUBSTRING(p_address, POSITION(':' IN p_address) + 1));
  SET p_address = (SUBSTRING(p_address, 1, POSITION(':' IN p_address) - 1));

  -- Checking current CDR state, when ended is true, then no update should be done, because cdr_finalize was already called
  SELECT ended INTO i_ended FROM cdr WHERE billid = p_billid AND chan = p_chan FOR UPDATE;

  IF i_ended IS NULL OR i_ended = 0 THEN

    -- perform an UPSERT meaning: try to INSERT the data, if there is a DUPLICATE KEY (uq_cdr_billid_chan which we created ealiert) then do an update on that record instead
    INSERT INTO cdr (sqltime, yatetime, billid, chan, address, addressport, caller, callername, called, billtime, ringtime, duration, direction, status ,reason, ended)
    VALUES (FROM_UNIXTIME(p_yatetime), p_yatetime, p_billid, p_chan, p_address, i_address_port, p_caller, p_callername, p_called, p_billtime, p_ringtime, p_duration,
    p_direction, p_status, p_reason, p_ended) ON DUPLICATE KEY UPDATE sqltime = VALUES(sqltime), yatetime = VALUES(yatetime), address = VALUES(address), addressport = VALUES(addressport),
    caller = VALUES(caller), callername = VALUES(callername), called = VALUES(called), billtime = VALUES(billtime), ringtime = VALUES(ringtime), duration = VALUES(duration),
    direction = VALUES(direction), status = VALUES(status), reason = VALUES(reason),
    ended = VALUES(ended);   

  END IF;

  RETURN 1;
END;
$$
delimiter ;

------------

3. in mysqldb.conf changes are

[yate]
host=localhost
database=test
user=root
password=
port=87

4. in yate.conf  changes

[module]
mysqldb.yate=true
register.yate=true

so here is my configuration . Please help me .if done any mistake.

THANKS

[asymetrixs]

Maybe it is just a typo, but it must be

call.cdr=on
(with N instead of M)


instead of

call.cdr=om

[asymetrixs]

Moreover why is your mysql-port 87?
Default MySQL Post is 3306, check your mysql configuration and check on which interfaces mysql is listening, maybe "localhost" causes a problem but 127.0.0.1 is just fine. So try changing localhost to 127.0.0.1.

[smbakhtiar]

Firstly thanks for your replay

i did as you said that is

1. change "om" to on
2. change localhost to 127.0.0.1
3.not change port number because another server already running mysql default port.

till now not save data in mysql

I also connect mysql and select table data , store data creating test php file test.php and it will connected and show the table data.

Please help me.

Thanks 

[vankooch]

Hi,

you do not need to add this line on yate.conf

Code: [Select]

[module]
mysqldb.yate=true
register.yate=true

Yate loads every module which is in the modules dir.

You also need to have cdrbuild module for cdrs, make sure it is loaded and you have cdrbuild.conf configured.

And it is not call.cdr = on it is call.cdr=yes for all handlers in register.conf so you need this:

Code: [Select]

; engine.timer: bool: Activate handler on the "engine.timer" message
engine.timer=yes

; call.preroute: bool: Activate handler on the "call.preroute" message
call.preroute=no

; call.route: bool: Activate handler on the "call.route" message
call.route=no

; call.cdr: bool: Activate handler on the "call.cdr" message
call.cdr=yes

It is a big confusing with the bool values in yate, but mostly it is yes or enable and not on/off
If you still have errors please post the yate log output, too.

[smbakhtiar]

Thanks for the replay

Finally it solved.

I am following the steps as you said and also i am not using mysql db port no . Then problem is solved.


Thanks again