mysql_cdr: Failed to insert into database

Get help with installing, upgrading and running Asterisk.

Moderators: muppetmaster, Moderator, Support

mysql_cdr: Failed to insert into database

Postby jonaskellens » Tue Jun 23, 2009 2:41 am

Code: Select all
[Jun 22 17:51:48] ERROR[13726]: cdr_addon_mysql.c:249 mysql_log: mysql_cdr: Failed to insert into database: (1054) Unknown column 'calldate' in 'field list'  == Spawn extension


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.
jonaskellens
Oldsterisk
 
Posts: 81
Joined: Wed Apr 08, 2009 2:01 pm

Postby adnanraza » Tue Jun 23, 2009 4:31 am

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

3) 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` );

4) 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

5) 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
adnanraza
Oldsterisk
 
Posts: 52
Joined: Thu Nov 15, 2007 11:10 am
Location: Islamabad(Pak)

Postby jonaskellens » Tue Jun 23, 2009 12:48 pm

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 :

Code: Select all
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,...
jonaskellens
Oldsterisk
 
Posts: 81
Joined: Wed Apr 08, 2009 2:01 pm

Postby adnanraza » Tue Jun 23, 2009 10:12 pm

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?
adnanraza
Oldsterisk
 
Posts: 52
Joined: Thu Nov 15, 2007 11:10 am
Location: Islamabad(Pak)

Postby jonaskellens » Wed Jun 24, 2009 2:30 am

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})
jonaskellens
Oldsterisk
 
Posts: 81
Joined: Wed Apr 08, 2009 2:01 pm

How to access the makefile ?

Postby lakeshore » Fri Aug 28, 2009 9:51 pm

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.
lakeshore
Newsterisk
 
Posts: 5
Joined: Thu May 21, 2009 11:08 pm

Postby adnanraza » Mon Aug 31, 2009 12:06 am

Hi lakeshore,

let me explain it a little bit more.
1- Get the asterisk-addons from http://www.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
adnanraza
Oldsterisk
 
Posts: 52
Joined: Thu Nov 15, 2007 11:10 am
Location: Islamabad(Pak)

CDR

Postby Vibhor Singhal » Fri Sep 04, 2009 1:37 am

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
Vibhor Singhal
Oldsterisk
 
Posts: 60
Joined: Thu Aug 07, 2008 1:21 am

Postby Mark_Logan » Fri Sep 04, 2009 12:24 pm

Please don't Hijack this thread for your own causes. If you need help on a specific issue please start a new thread. Thanks.
Mark_Logan
Oldsterisk
 
Posts: 179
Joined: Thu Dec 21, 2006 10:56 pm
Location: Edmonton Canada


Return to Asterisk Support

Who is online

Users browsing this forum: Google [Bot] and 1 guest

cron