Cdr is not saved to mysql

Asterisk Asterisk Support
1

Hi All,

I was using asterisk 1.6(ubuntu 10.04) and I can not remember whether i have added mysql addson or not ,

recently i have upgraded my os from ubuntu 10.04 to 11.04 and by default asterisk has been upgraded to 1.8.3

I followed the instruction of ‘adnanraza’(his first reply) to the post forums.asterisk.org/viewtopic.php?f=1&t=69484
but no cdr has been saved to the table.

How can i check whether my installed asterisk supports cdr recording into mysql db ?
Is saving cdr to mysql table is done automatically as long as you have cdr adds on or should be done manually by by adding some lines of code inside dailplan/agiscript ?

 sip set debug on
SIP Debugging enabled
  == Manager 'manager' logged on from 127.0.0.1
  == Using SIP RTP CoS mark 5
We think we can do text
Audio is at 5060
Adding codec 0x40 (slin) to SDP
Adding codec 0x2 (gsm) to SDP
Adding codec 0x4 (ulaw) to SDP
Adding codec 0x8 (alaw) to SDP
Adding codec 0x10 (g726aal2) to SDP
Adding codec 0x20 (adpcm) to SDP
Adding codec 0x80 (lpc10) to SDP
Adding codec 0x800 (g726) to SDP
Adding codec 0x1000 (g722) to SDP
Adding codec 0x8000 (slin16) to SDP
Adding codec 0x800000000000 (testlaw) to SDP
Adding non-codec 0x1 (telephone-event) to SDP
Reliably Transmitting (no NAT) to 192.168.0.2:5061:
INVITE sip:ivan@192.168.0.2:5061 SIP/2.0
Via: SIP/2.0/UDP 192.168.0.2:5060;branch=z9hG4bK548003d0
Max-Forwards: 70
From: "Anonymous" <sip:Anonymous@anonymous.invalid>;tag=as5430b682
To: <sip:ivan@192.168.0.2:5061>
Contact: <sip:Anonymous@192.168.0.2:5060>
Call-ID: 3fd86a0228e349966aaf190a727b89e2@192.168.0.2:5060
CSeq: 102 INVITE
User-Agent: Asterisk PBX 1.8.3
Date: Fri, 20 Apr 2012 21:56:46 GMT
Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY, INFO, PUBLISH
Supported: replaces, timer
Content-Type: application/sdp
Content-Length: 497

v=0
o=root 1875648370 1875648370 IN IP4 192.168.0.2
s=Asterisk PBX 1.8.3
c=IN IP4 192.168.0.2
t=0 0
m=audio 12600 RTP/AVP 10 3 0 8 112 5 7 111 9 118 101
a=rtpmap:10 L16/8000
a=rtpmap:3 GSM/8000
a=rtpmap:0 PCMU/8000
a=rtpmap:8 PCMA/8000
a=rtpmap:112 AAL2-G726-32/8000
a=rtpmap:5 DVI4/8000
a=rtpmap:7 LPC/8000
a=rtpmap:111 G726-32/8000
a=rtpmap:9 G722/8000
a=rtpmap:118 L16/16000
a=rtpmap:101 telephone-event/8000
a=fmtp:101 0-16
a=silenceSupp:off - - - -
a=ptime:20
a=sendrecv

---

<--- SIP read from UDP:192.168.0.2:5061 --->
SIP/2.0 100 Trying
CSeq: 102 INVITE
Via: SIP/2.0/UDP 192.168.0.2:5060;branch=z9hG4bK548003d0
From: "Anonymous" <sip:Anonymous@anonymous.invalid>;tag=as5430b682
Call-ID: 3fd86a0228e349966aaf190a727b89e2@192.168.0.2:5060
To: <sip:ivan@192.168.0.2:5061>
Contact: <sip:ivan@192.168.0.2:5061>
Content-Length: 0

<------------->
--- (8 headers 0 lines) ---

<--- SIP read from UDP:192.168.0.2:5061 --->
SIP/2.0 180 Ringing
CSeq: 102 INVITE
Via: SIP/2.0/UDP 192.168.0.2:5060;branch=z9hG4bK548003d0
User-Agent: Ekiga/3.2.7
From: "Anonymous" <sip:Anonymous@anonymous.invalid>;tag=as5430b682
Call-ID: 3fd86a0228e349966aaf190a727b89e2@192.168.0.2:5060
To: <sip:ivan@192.168.0.2:5061>;tag=14518c64-a189-e111-8257-000e7ba2889e
Allow: INVITE,ACK,OPTIONS,BYE,CANCEL,SUBSCRIBE,NOTIFY,REFER,MESSAGE,INFO,PING
Content-Length: 0

<------------->
--- (9 headers 0 lines) ---

<--- SIP read from UDP:192.168.0.2:5061 --->
SIP/2.0 200 OK
CSeq: 102 INVITE
Via: SIP/2.0/UDP 192.168.0.2:5060;branch=z9hG4bK548003d0
User-Agent: Ekiga/3.2.7
From: "Anonymous" <sip:Anonymous@anonymous.invalid>;tag=as5430b682
Call-ID: 3fd86a0228e349966aaf190a727b89e2@192.168.0.2:5060
To: <sip:ivan@192.168.0.2:5061>;tag=14518c64-a189-e111-8257-000e7ba2889e
Contact: <sip:ivan@192.168.0.2:5061>
Allow: INVITE,ACK,OPTIONS,BYE,CANCEL,SUBSCRIBE,NOTIFY,REFER,MESSAGE,INFO,PING
Content-Type: application/sdp
Content-Length: 213

v=0
o=- 1334959006 1 IN IP4 192.168.0.2
s=Opal SIP Session
c=IN IP4 192.168.0.2
t=0 0
m=audio 5062 RTP/AVP 0 101
a=sendrecv
a=rtpmap:0 PCMU/8000/1
a=rtpmap:101 telephone-event/8000
a=fmtp:101 0-16,32,36
<------------->
--- (11 headers 10 lines) ---
Found RTP audio format 0
Found RTP audio format 101
Found audio description format PCMU for ID 0
Found audio description format telephone-event for ID 101
Capabilities: us - 0x80030c7fffff (g723|gsm|ulaw|alaw|g726|adpcm|slin|lpc10|g729|speex|speex16|ilbc|g726aal2|g722|slin16|jpeg|png|h261|h263|h263p|h264|mpeg4|red|t140|siren7|siren14|testlaw|g719), peer - audio=0x4 (ulaw)/video=0x0 (nothing)/text=0x0 (nothing), combined - 0x4 (ulaw)
Non-codec capabilities (dtmf): us - 0x1 (telephone-event|), peer - 0x1 (telephone-event|), combined - 0x1 (telephone-event|)
Peer audio RTP is at port 192.168.0.2:5062
list_route: hop: <sip:ivan@192.168.0.2:5061>
set_destination: Parsing <sip:ivan@192.168.0.2:5061> for address/port to send to
set_destination: set destination to 192.168.0.2:5061
Transmitting (no NAT) to 192.168.0.2:5061:
ACK sip:ivan@192.168.0.2:5061 SIP/2.0
Via: SIP/2.0/UDP 192.168.0.2:5060;branch=z9hG4bK38d50219
Max-Forwards: 70
From: "Anonymous" <sip:Anonymous@anonymous.invalid>;tag=as5430b682
To: <sip:ivan@192.168.0.2:5061>;tag=14518c64-a189-e111-8257-000e7ba2889e
Contact: <sip:Anonymous@192.168.0.2:5060>
Call-ID: 3fd86a0228e349966aaf190a727b89e2@192.168.0.2:5060
CSeq: 102 ACK
User-Agent: Asterisk PBX 1.8.3
Content-Length: 0


---
       > Channel SIP/ivan-00000001 was answered.
  == Manager 'manager' logged off from 127.0.0.1
    -- Executing [567@test:1] Answer("SIP/ivan-00000001", "") in new stack
    -- Executing [567@test:2] NoOp("SIP/ivan-00000001", "") in new stack
    -- Executing [567@test:3] SayUnixTime("SIP/ivan-00000001", "-1,UTC") in new stack
    -- <SIP/ivan-00000001> Playing 'digits/day-3.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/mon-11.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/30.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/h-1.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/19.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/60.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/9.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/at.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/11.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/50.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/9.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/p-m.gsm' (language 'en')
    -- Executing [567@test:4] SayUnixTime("SIP/ivan-00000001", ",CET,kMbdY") in new stack
    -- <SIP/ivan-00000001> Playing 'digits/20.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/3.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/50.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/7.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/mon-3.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/h-20.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/2.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/thousand.gsm' (language 'en')
    -- <SIP/ivan-00000001> Playing 'digits/12.gsm' (language 'en')
    -- Executing [567@test:5] Playback("SIP/ivan-00000001", "vm-goodbye") in new stack
    -- <SIP/ivan-00000001> Playing 'vm-goodbye.gsm' (language 'en')
    -- Executing [567@test:6] Hangup("SIP/ivan-00000001", "") in new stack
  == Spawn extension (test, 567, 6) exited non-zero on 'SIP/ivan-00000001'
Scheduling destruction of SIP dialog '3fd86a0228e349966aaf190a727b89e2@192.168.0.2:5060' in 32000 ms (Method: INVITE)
set_destination: Parsing <sip:ivan@192.168.0.2:5061> for address/port to send to
set_destination: set destination to 192.168.0.2:5061
Reliably Transmitting (no NAT) to 192.168.0.2:5061:
BYE sip:ivan@192.168.0.2:5061 SIP/2.0
Via: SIP/2.0/UDP 192.168.0.2:5060;branch=z9hG4bK6071a5ce
Max-Forwards: 70
From: "Anonymous" <sip:Anonymous@anonymous.invalid>;tag=as5430b682
To: <sip:ivan@192.168.0.2:5061>;tag=14518c64-a189-e111-8257-000e7ba2889e
Call-ID: 3fd86a0228e349966aaf190a727b89e2@192.168.0.2:5060
CSeq: 103 BYE
User-Agent: Asterisk PBX 1.8.3
X-Asterisk-HangupCause: Normal Clearing
X-Asterisk-HangupCauseCode: 16
Content-Length: 0


---

<--- SIP read from UDP:192.168.0.2:5061 --->
SIP/2.0 200 OK
CSeq: 103 BYE
Via: SIP/2.0/UDP 192.168.0.2:5060;branch=z9hG4bK6071a5ce
From: "Anonymous" <sip:Anonymous@anonymous.invalid>;tag=as5430b682
Call-ID: 3fd86a0228e349966aaf190a727b89e2@192.168.0.2:5060
To: <sip:ivan@192.168.0.2:5061>;tag=14518c64-a189-e111-8257-000e7ba2889e
Content-Length: 0

<------------->
--- (7 headers 0 lines) ---
Really destroying SIP dialog '3fd86a0228e349966aaf190a727b89e2@192.168.0.2:5060' Method: INVITE
2

asterisk -rx "cdr show status"
asterisk -rx “module show like mysql” ( check if cdr_addon_mysql.so is installed or loaded )

3

Thanks omid.mohajerani for your reply ,

here is the output of above 2 commands :

test-machine*CLI> cdr show status

Call Detail Record (CDR) settings
----------------------------------
  Logging:                    Enabled
  Mode:                       Simple
  Log unanswered calls:       No

* Registered Backends
  -------------------
    csv
    cdr-custom

test-machine*CLI> module show like mysql
Module                         Description                              Use Count 
0 modules loaded
test-machine*CLI>

by the way I have checked menuselect but I have found that the ‘cdr_mysql’ already checked (x) which means that it has been already installed

test@test-machine:~$ sudo make menuselect


                                                                                    Asterisk Module and Build Option Selection
                                                                                **************************************************

                                                                                                Press 'h' for help.

                                                                                               XXX app_mysql
                                                                                               [ ] app_saycountpl
                                                                                               XXX cdr_mysql
                                                                                               XXX chan_mobile
                                                                                               [ ] chan_ooh323
                                                                                               [ ] format_mp3
                                                                                               XXX res_config_mysql
4

The CDR mysql module isn’t being loaded. See if you can load it by hand at the Asterisk console.

module load cdr_mysql.so

See what errors you get. My guess is that it will either tell you that it can’t find cdr_mysql.so itself because it didn’t get built/installed right in the original compile or that it won’t load because it can’t load required libraries (like libmysqlclient.so.15 or something like that).

5

thanks jpsharp alot for your reply ,

I have executed the command , mysql adds on was not installed , any how i have installed ,

now am facing another problem

[Apr 22 01:19:02] ERROR[3112]: cdr_mysql.c:201 mysql_log: Cannot connect to database server localhost: (2002) Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

the above file does not exist in the specified path ,

I have found it in another path

but if i replace the following line in cdr_mysql.conf

with

sock=/var/run/mysqld/mysqld.sock

I will get the following errors

$ sudo asterisk -rvvvvvvvvvvv Unable to connect to remote asterisk (does /var/run/asterisk/asterisk.ctl exist?)

$ sudo asterisk -vvvvvvvvvvv .... ... [Apr 22 01:26:57] ERROR[3340]: cdr_mysql.c:566 my_load_module: Unable to query table description!! Logging disabled.

I tried to make a symbolic link

$ sudo ln -s /var/lib/mysql/mysql.sock /var/run/mysqld/mysqld.sock[sudo] password for ahmed: ln: creating symbolic link `/var/run/mysqld/mysqld.sock': File exists

but no link will be created

6

Is your mysql server actually running? Can you connect to it from the command line?

Have you created your databases & database tables?

7

This error is usually because of sopped Mysql service .
If you are not configuring asterisk and creating tables yet I suggest you to use cdr_adaptive_odbc method instead of cdr_mysql
cdr_mysql is depreciated .

8

Thanks for all replies

[quote]Is your mysql server actually running? Can you connect to it from the command line?

Have you created your databases & database table[/quote]

mysql works fine , am able to connect to it from the command line/mysql query browser …and from many other tools.

I have already created the database and table related to cdr

[quote]This error is usually because of sopped Mysql service .
If you are not configuring asterisk and creating tables yet I suggest you to use cdr_adaptive_odbc method instead of cdr_mysql
cdr_mysql is depreciated . [/quote]

I have already installed and configured mysql_cdr , I have already created the database and the table related to cdr ,

Do you mean that cdr_mysql wont work with asterisk 1.8.3 ?

Could you please solutions to fix this issue ?

9

Hi All,

Any suggested solutions !!!

If we have only one way to go for cdr_adaptive_odbc . then how i can checked whether cdr_adaptive_odbc has been installed and configured ? what are the configurations steps ?

Thank you

10

Thanks all for the replies.

I have configured it successfully using cdr_mysql,

but I really feel shy to say the cause of the issue :cry:
It was because of the db user ‘asterisk’ does not have access on the table cdr (I was connecting to my db through command line using ‘root’ user who has full access rights , I was silly!!! I have never checked to connect to my db through command line or any other db tools using ‘asterisk’ user )

The following is the modification i have made

Grant the user ‘asterisk’ full privileges on ‘asterisk’ db

mysql> revoke all privileges on asterisk.* from 'asterisk'@'localhost'; mysql> grant select,insert,delete,update ON asterisk.* TO 'asterisk'@'%'; mysql> flush privileges;

Other configurations are the same :

/etc/asterisk/cdr_mysql.conf

[global] hostname=localhost dbname=asterisk table=cdr password=asterisk123 user=asterisk ;password=root ;user=root port=3306 ;sock=/var/lib/mysql/mysql.sock sock=/var/run/mysqld/mysqld.sock ;sock=/tmp/mysqld.sock userfield=1

/etc/mysql/my.cnf

socket = /var/run/mysqld/mysqld.sock