CDR with ODBC

Asterisk Asterisk General
1

I have problems with writing to my cdr-table, which I created in postgresql (8.3.5). I connect through odbc. Asterisk is 1.6.0.1, running as non-root.

This is my table description:

asterisk=> \dt asterisk_cdr
List of relations
Schema | Name | Type | Owner
--------±-------------±------±---------
public | asterisk_cdr | table | asterisk
(1 row)

cdr_odbc.conf is configured as follows:

[global]
dsn=asterisk-connector
username=asterisk
password=something
loguniqueid=yes
table=asterisk_cdr

This is the output for “cdr show status”:

*CLI> cdr show status
CDR logging: enabled
CDR mode: simple
CDR output unanswered calls: no
CDR registered backend: ODBC
CDR registered backend: cdr-custom
CDR registered backend: csv
CDR registered backend: Adaptive ODBC

When I make a test call, I get a registration in Master.csv, but concerning ODBC I get following error message when hanging up, without registration of the call into the database:

*CLI>
== Using SIP RTP CoS mark 5
– Executing [6002@users:1] Dial(“SIP/6001-082b8bd8”, “SIP/6002”) in new stack
== Using SIP RTP CoS mark 5
– Called 6002
– SIP/6002-082c2420 is ringing
– SIP/6002-082c2420 answered SIP/6001-082b8bd8
– Packet2Packet bridging SIP/6001-082b8bd8 and SIP/6002-082c2420
[Jan 29 00:29:47] ERROR[3400]: cdr_odbc.c:133 odbc_log: Unable to retrieve database handle. CDR failed.
== Spawn extension (users, 6002, 1) exited non-zero on ‘SIP/6001-082b8bd8’

I don’t know what is meant by “Unable to retrieve database handle”. Probably I am missing something obvious, but I can’t put my finger on the trouble spot. Does anybody has any advice?

Thanks in advance
Toni

2

starting from version 1.6.0.3 i am getting the same error, i just installed this new version… DB and my configuration files are all kept the same during the new update…

When i issue “odbc show all” I get the following

Name: ast-conf
DSN: asterisk-odbc
Pooled: No
Connected: Yes

issuing cdr show status prints

CDR logging: enabled
CDR mode: simple
CDR output unanswered calls: no
CDR registered backend: cdr-custom
CDR registered backend: ODBC
CDR registered backend: Adaptive ODBC

But after a call is completed I get the infamous "odbc_log: Unable to retrieve database handle. CDR failed. "

any ideas?

Thanks,

Vito

3

Why don’t you try with MySql, is very simple. Here are the steps

Asterisk cdr mysql
Asterisk can store CDR records in a MySQL database, as an alternative to CSV text files and other database formats.
How to download cdr_mysql
Due to Mysql client libraries licensing, the Mysql billing application is no longer an integrated part of the Asterisk standard distribution. It is now located in the asterisk-addons CVS directory.

Follow the instructions on asterisk.org/index.php?menu=download for Subversion download

cd /usr/src

svn checkout svn.digium.com/svn/asterisk-addons/branches/1.2 asterisk-addons-1.2

You must have mysql and mysql-devel packages installed.
• In an RPM-based Linux, you can check presence of MySQL like this:
o rpm -qa | grep mysql
• For debian or other dpkg-based systems, check like this:
o dpkg -l mysql-server libmysqlclient*dev
• In FreeBSD, you’ll find MySQL in the ports library, /usr/ports/databases/mysql4-server

Compile

cd asterisk-addons-1.2

make clean

make

make install

Check that in make stage that there are no mysql.h errors, which mean you are missing mysql-devel package

Table definitions for Asterisk cdr_mysql
This is the database definition you use to install in Mysql to support billing.
Create the database

mysql --user=root --password=password [-h dbhost]

CREATE DATABASE asterisk;

GRANT INSERT ON asterisk.* TO asterisk@localhost IDENTIFIED BY ‘controladmin’;

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 ‘’;
ALTER TABLE cdr ADD INDEX ( calldate );
ALTER TABLE cdr ADD INDEX ( dst );
ALTER TABLE cdr ADD INDEX ( accountcode );

I hope this can help you

4

Thanks for the message. There are couple reasons I’d like to keep using ODBC. First it’s a more general approach to the database and it supports more than just mysql. Second it’s also used more, so tested more and probably more stable than mysql addon.

If something has changed in the latest version (1.6.0.5) causing this problem, I think me switching to mysql won’t fix it :smile: Whatever the problem is needs to be fixed anyways :smile: (If there is one) Or maybe something has changed in the new version so that I need to change my configuration and there is not really a bug; i don’t know.

I checked the update files but didn’t see anything that i need to change for odbc or mysql. That’s why i am seeking help here in this forum :smile:

someone must have figured out something ? or not? :frowning:

5

Hi,

I got odbc to work with voicemail, but not with cdr. I went over a lot of documentation, and finally decided to try to connect pgsql directly to asterisk. It really isn’t such a big deal:

put into res_pgsql.conf
[general]
dbhost=127.0.0.1
dbport=5432
dbname=asterisk
dbuser=asterisk
dbpass=some_password

put into modules.conf
load=>res_config_pgsql.so

put into cdr_pgsql.conf
[global]
hostname=localhost
port=5432
dbname=asterisk
password=some_password
user=asterisk
table=ast_cdr

Of course you have to compile pgsql into asterisk.
I’m still in testing phase, but apparently it works fine for me.

Toni

6

It should be noted that in Asterisk 1.6, ODBC connections for all subsystems (Voicemail, CDR,…) is defined in res_odbc.conf. This means that cdr_odbc.conf should refer to a DSN in res_odbc.conf; and res_odbc.conf should refer to a DSN in odbc.ini:

odbc.ini:

[asterisk] Description = MySQL ODBC Driver Driver = /usr/lib/libmyodbc3.so Socket = /var/lib/mysql/mysql.sock Server = localhost Database = asterisk Option = 3

res_odbc.conf:

[mysqlpool] enabled => yes dsn => asterisk username => someone password => something pre-connect => yes

cdr_odbc.conf:

[global] dsn=mysqlpool loguniqueid=yes dispositionstring=yes table=cdr

Regards,
Fadishei

7

Got the following error when I followed the above directions.

NOTICE[14101]: res_odbc.c:834 odbc_obj_connect: Connecting mysqlpool WARNING[14101]: res_odbc.c:859 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified WARNING[14101]: res_odbc.c:750 ast_odbc_request_obj: Failed to connect to mysqlpool ERROR[14101]: cdr_odbc.c:133 odbc_log: Unable to retrieve database handle. CDR failed.

/etc/odbc.ini

[asterisk] Description = MySQL ODBC Driver Driver = /usr/lib/odbc/libmyodbc.so Socket = /var/run/mysqld/mysqld.sock Setup = /usr/lib/odbc/libodbcmyS.so Server = localhost Database = asterisk Option = 3

/etc/asterisk/res_odbc.conf

[mysqlpool] enabled => yes dsn => asterisk username => *** password => *** pre-connect => yes

/etc/asterisk/cdr_odbc.conf

[global] dsn=mysqlpool loguniqueid=yes dispositionstring=yes table=cdr

What could be wrong here?