Ed's Blog

Hi all,
Today I’ve been setting up Asterisk to work with ODBC and MySQL.

I started out by creating the MySQL table, the code is:

[code lang="sql"]
CREATE TABLE cdr(calldate DATETIME NOT NULL,clid varchar(80) NOT NULL DEFAULT 'aDefaultValue',src varchar(80) NOT NULL DEFAULT 'aDefaultValue',dst VARCHAR(80) NOT NULL DEFAULT 'aDefaultValue',dcontext VARCHAR(80) NOT NULL DEFAULT 'aDefaultValue',channel VARCHAR(80) NOT NULL DEFAULT 'aDefaultValue',dstchannel VARCHAR(80) NOT NULL DEFAULT 'aDefaultValue',lastapp VARCHAR(80) NOT NULL DEFAULT 'aDefaultValue',lastdata VARCHAR(80) NOT NULL DEFAULT 'aDefaultValue',duration int(11) NOT NULL DEFAULT '0',billsec int(11) NOT NULL DEFAULT '0',disposition VARCHAR(45) NOT NULL DEFAULT 'aDefaultValue',amaflags int(11) NOT NULL DEFAULT '0',accountcode VARCHAR(20) NOT NULL DEFAULT 'aDefaultValue',uniqueid VARCHAR(32) NOT NULL DEFAULT 'aDefaultValue',userfield VARCHAR(255) NOT NULL DEFAULT 'aDefaultValue');
[/code]

WARNING: I’ve found that any spaces in the above create table code will cause syntax errors in mySQL.

I did the install in Ubuntu so you may need to do other steps to install the drivers needed for ODBC on your OS.
You need to get MySQL and iODBC and unixodbc.

apt-get install unixodbc libmyodbc unixodbc iodbc

This should install all the packages needed for MySQL and ODBC.

Go and edit your /etc/odbcinst.ini and paste in:

[MySQL]
Description = MySQL ODBC MyODBC Driver
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
CPTimeout = 0
CPReuse = 0

Now go and edit /etc/odbc.ini

[MySQL-asterisk]
Description = MYSQL ODBC Driver Testing
Driver = MySQL
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = YourUsername
Password = YourPassword
Database = YourDatabase
Option = 3

No go and edit /etc/asterisk/cdr.conf and set enabled = yes and at the bottom of the cdr.conf add the following

[odbc]
usegmtime=yes
loguniqueid=yes
loguserfiled=yes

Then go and edit /etc/asterisk/res_odbc.conf, add this:

[mysql]
enabled => yes
dsn => MySQL-asterisk
username => YourUsername
password => YourPassword
pre-connect => yes

Finally edit /etc/asterisk/cdr.conf and replace the existing global with:

[global]
dsn=MySQL-asterisk
;username=asterisk
;password=asterisk
loguniqueid=yes
dispositionstring=yes
table=cdr ;”cdr” is default table name
usegmtime=no

Go to your asterisk console ( type asterisk -vvvvvvvvr) and type restart now
Connect once more and type module reload res_odbc. Make sure it all looks good
Type module reload cdr_odbc and see if it is connecting ok.

If it doesn’t make sure you can access mysql from the command line and make sure you have given asterisk permissions on the mysql database. i did this but it may be a bit insecure, I’m only developing at the moment

[code lang="sql"]
GRANT ALL PRIVILEGES ON asterisk.* TO YourUsername@localhost IDENTIFIED BY 'YourPassword'
[/code]

edlong Archive Post, Asterisk