mysql_cdr: Failed to insert into database

Asterisk Asterisk Support
1 

I have the following columns (for billing) :

accountcode src dst dcontext clid channel dstchannel lastapp lastdata start answer end duration billsec disposition amaflags

Why does it want to write to a column calldate ?? Where is this defined ??

Thanks for the help !
Jonas.

I have the following columns (for billing) :

accountcode src dst dcontext clid channel dstchannel lastapp lastdata start answer end duration billsec disposition amaflags

Why does it want to write to a column calldate ?? Where is this defined ??

Thanks for the help !
Jonas.

2

Asterisk logs the following field to mysql db:
calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,
billsec,disposition,amaflags,accountcode,userfield.
It does not logs starttime and endtime.
You are getting this error because you have not added the field calldate to the database table and asterisk tries to insert value into this field and does not finds the field. Thats why it is giving error.

I have installed asterisk addons and configure asterisk to write CDR to mysql db. Following are the steps I followed and it works.

  1. unzip and untar asterisk-addons-1.2.2.tar.gz

    shell> gunzip asterisk-addons-1.2.2.tar.gz
    shell> tar -xvf asterisk-addons-1.2.2.tar
    shell> cd asterisk-addons-1.2.2

  2. Execute following commands 1 by 1

  • For asterisk to log uniqueid to mysql cdr ,add the following line to the Makefile

    CFLAGS+=-DMYSQL_LOGUNIQUEID
    Also add the following to cdr/cdr_addon_mysql.c
    #define MYSQL_LOGUNIQUEID

    shell> make clean
    shell> ./configure
    shell> make
    shell> make install
    shell> make samples

  1. To enable cdr logging in Mysql DB:-

  • Login to mysql server
    → CREATE DATABASE asterisk;
    → GRANT INSERT ON asterisk.* TO asterisk@localhost IDENTIFIED BY ‘asterisk123’;
    → flush privileges;

  • USE asterisk;

  • CREATE TABLE cdr (
    calldate datetime NOT NULL default ‘0000-00-00 00:00:00’,
    clid varchar(80) NOT NULL default ‘’,
    src varchar(80) NOT NULL default ‘’,
    dst varchar(80) NOT NULL default ‘’,
    dcontext varchar(80) NOT NULL default ‘’,
    channel varchar(80) NOT NULL default ‘’,
    dstchannel varchar(80) NOT NULL default ‘’,
    lastapp varchar(80) NOT NULL default ‘’,
    lastdata varchar(80) NOT NULL default ‘’,
    duration int(11) NOT NULL default ‘0’,
    billsec int(11) NOT NULL default ‘0’,
    disposition varchar(45) NOT NULL default ‘’,
    amaflags int(11) NOT NULL default ‘0’,
    accountcode varchar(20) NOT NULL default ‘’,
    userfield varchar(255) NOT NULL default ‘’
    );

ALTER TABLE cdr ADD uniqueid VARCHAR(32) NOT NULL DEFAULT ‘’ AFTER accountcode;
ALTER TABLE cdr ADD INDEX ( uniqueid );
ALTER TABLE cdr ADD INDEX ( calldate );
ALTER TABLE cdr ADD INDEX ( dst );
ALTER TABLE cdr ADD INDEX ( accountcode );

  1. cdr_mysql.conf should look like this:-
    nano /etc/asterisk/cdr_mysql.conf
    [global]
    hostname=localhost
    dbname=asterisk
    table=cdr
    password=asterisk123
    user=asterisk
    port=3306
    sock=/var/lib/mysql/mysql.sock
    userfield=1

  2. Add following lines to cdr.conf
    nano /etc/asterisk/cdr.conf
    [mysql]
    usegmtime=no ; log date/time in GMT. Default is “no”
    loguniqueid=yes ; log uniqueid. Default is "no
    loguserfield=yes ; log user field. Default is "no

Hope this helps.
Adnan

3

I have build the correct database + table now.

But another problem arises :

[Jun 23 20:43:53] ERROR[18324]: cdr_addon_mysql.c:249 mysql_log: mysql_cdr: Failed to insert into database: (1062) Duplicate entry ‘2009-06-23 20:43:40’ for key 1

I guess this is because there is an entry for the call twinkle --> Asterisk and then for Asterisk --> 3StarsNet

So Asterisk is in between my softphone twinkle and my SIP-provider 3StarsNet.

How to overcome this ??

Another issue : the only information that is written to the MySQL-database is this :

calldate 2009-06-23 20:43:40 clid src dst s dcontext default channel SIP/3starsnet-0825d720 dstchannel lastapp lastdata duration 13 billsec 0 disposition NO ANSWER amaflags 3 accountcode userfield

No clid, no src, no dstchannel, no lastapp and no lastdata,…

4

Mysql shows duplicate entry error when there is a primary key in the table and you insert duplicate values for primary key column. I think cdr table has no primary key so it must not show this error or may be you have declared some field as primary key.
If you look at the disposition field its value is ‘NO ANSWER’, means that call landed on your asterisk server but was not answered. Call remained on the server for 13 seconds and was disconnected then. Thats why billsec is 0 and as it was not answered so it does not executed any lastapp and therefore no lastdata.

can you post your extensions.conf?

5

Indeed I have manually added a primary key… An index is enough.

When the call goes through (and is answered) the row is written as it should.

You are right when you say a connection is maid between my softphone and my Asterisk-server, but then the connection Asterisk --> callee is not answered.
Though it should write an extra line in de CDR-table, no ?! Also the CallerID is not written.

My test-extension.conf :

[globals]
3STARSNET=SIP/3starsnet

[intern]
include => gsm
exten => 10,1,Dial(SIP/grandstream)
exten => 20,1,Dial(SIP/twinkle)

[gsm]
exten => _04[7-9]XXXXXXX,1,NoOp(conversation to GSM)
exten => _04[7-9]XXXXXXX,n,Dial(${3STARSNET}/${EXTEN})

6

Hi everybody, I’m trying to follow instructions provided by Adnan and cannot find out to access the makefile ?
here is a copy of the instructions provided by Adnan and bellow my questions.
“”" Execute following commands 1 by 1

  • For asterisk to log uniqueid to mysql cdr ,add the following line to the Makefile

CFLAGS+=-DMYSQL_LOGUNIQUEID
Also add the following to cdr/cdr_addon_mysql.c
#define MYSQL_LOGUNIQUEID***

Question: how do I run these commands ?
Do I do for example:
lakeshore@lakeshore:~$ nano /etc/asterisk/makefile ?
Please help me.
And also how do I access cdr/cdr_addon_mysql.c ?
Thanks.

7

Hi lakeshore,

let me explain it a little bit more.
1- Get the asterisk-addons from asterisk.org/ .
2- unpack it in directory /usr/src/
3. Edit the file /usr/src/asterisk-addons…/cdr/cdr_addon_mysql.c , add the following line to it
#define MYSQL_LOGUNIQUEID
4- Edit the file /usr/src/asterisk-addons…/Makefile , add the following line to it
CFLAGS+=-DMYSQL_LOGUNIQUEID.

I think rest of the step are clear to you.

Regards
Adnan

8

hi,

i am trying to view CDR data from /var/log/asterisk/cdr-csv but unable to view.On using vi Master.csv in editor,it displays “Master.csv”[New File]. How to view CDR data,is there any module that is to be loaded or any service that has to be enabled ??

Urgent help required.

Thanks

9

Please don’t Hijack this thread for your own causes. If you need help on a specific issue please start a new thread. Thanks.