How to configure ODBC for Asterisk

One day I installed new Asterisk 20 from source on Ubuntu Server 22.04 and I needed to record CDR call statistics to a MySQL database since MySQL server was already installed and used by other services. And the cdr_mysql module was removed in Asterisk 19, so I had to configure ODBC to write CDRs to MySQL.

I first tried to load the cdr_mysql module, before I found out that it does not exist in the new version of Asterisk:

asterisk -rvvv
module load
Unable to load module
Command 'module load' failed.
ERROR[3331444]: loader.c:283 module_load_error: Error loading module '': /usr/lib/asterisk/modules/ cannot open shared object file: No such file or directory

Since the cdr_mysql module does not exist, you can use the cdr_odbc or cdr_adaptive_odbc modules, which are configured slightly differently.
Let’s install the necessary components:

apt-cache search ODBC
apt-get install libmyodbc unixodbc unixodbc-dev lame

lame is a wav to mp3 decoder, useful since wav is not a compressed audio file, also when menuselect you need to select mp3 and app_macro.
Since I had the MariaDB server installed for MySQL databases, I installed the odbc-mariadb package (if it’s regular MySQL, then the /etc/odbcinst.ini file needs to be configured manually):

apt-get install odbc-mariadb

After this, the file /usr/lib/x86_64-linux-gnu/odbc/ and the file /etc/odbcinst.ini appeared in which it was already indicated:

[MariaDB Unicode]
Description=MariaDB Connector/ODBC(Unicode)

Then I configured the /etc/odbc.ini file:

Description = MySQL ODBC Driver
Driver = MariaDB Unicode
Server = localhost
Database = asteriskcdrdb
User = asteriskcdr
Password =
Option = 3
Charset = utf8

Let’s check the connection to the MySQL database server:

isql -v MySQL-asterisk asteriskcdr
show databases;
show tables;
select * from cdr;

Let’s try to load the module:

asterisk -rvvv 
module load
module show like odbc
cdr show status

If the module does not load, perhaps the dependencies were not installed when installing Asterisk, install them:

ls /usr/lib/asterisk/modules/ | grep odbc

cd /usr/src/asterisk-*/contrib/scripts
./install_prereq install

In the /etc/asterisk/modules.conf file, we will indicate that the module will be loaded automatically, or we will indicate that all modules will be loaded and disable loading unnecessary ones:

autoload = yes
;load =>
noload =>
noload => cdr_sqlite3_custom
noload => cdr_csv
noload => cdr_pgsql
noload => cdr_tds
noload => cel_tds
noload => cel_radius
noload => cdr_radius
noload => cel_sqlite3_custom

Now let’s configure the cdr_odbc.conf file (at the bottom I commented out the unused parameters):

nano /etc/asterisk/cdr_odbc.conf
loguniqueid=yes ;Required to use the userfield
;table=cdr ;"cdr" is default table name

If “newcdrcolumns” is activated, then additional fields peeraccount, linkedid, sequence must be created in the cdr table.
If “dispositionstring” is not activated, then in the disposition field the call status will be entered in numbers, for example ANSWERED=8, NO ANSWER=4, BUSY=2, FAILED=1 or 0 if the call was interrupted before being answered.

Let’s configure the /etc/asterisk/res_odbc.conf file:

enabled => yes
dsn => MySQL-asterisk
pooling => no
limit => 1
pre-connect => yes
;username => asteriskcdr
;password =>
;dbname => asteriskcdrdb
;table => cdr

For some reason, the name of the audio file was not entered into the recordingfile field, so I didn’t bother and used an empty userfield field, example extensions.conf:

exten => s,1,Set(fname=${UNIQUEID}-${STRFTIME(${EPOCH},,%Y-%m-%d-%H_%M)}-${ARG1}-${ARG2});
exten => s,n,Set(monopt=nice -n 19 /usr/bin/lame -b 32  --silent "/srv/monitor/${fname}.wav"  "/srv/monitor/${fname}.mp3" && rm -f "/srv/monitor/${fname}.wav" && chmod o+r "/srv/monitor/${fname}.mp3");
exten => s,n,Set(CDR(userfield)=${fname}.mp3);
exten => s,n,MixMonitor(/srv/monitor/${fname}.wav,b,${monopt});

exten => _2XX,1,Macro(recording,${CALLERID(num)},${EXTEN})
exten => _2XX,n,Dial(PJSIP/${EXTEN},60,tT)
exten => _2XX,n,Hangup

And in the directory with Asterisk CDR Viewer, I made a symbolic link to /srv/monitor/ so that when backing up /var/www/ I would not archive gigabytes of mp3 files.

See my other articles:

Leave a comment

Leave a Reply