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 cdr_mysql.so
Unable to load module cdr_mysql.so
Command 'module load cdr_mysql.so' failed.
ERROR[3331444]: loader.c:283 module_load_error: Error loading module 'cdr_mysql.so': /usr/lib/asterisk/modules/cdr_mysql.so: 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/libmaodbc.so and the file /etc/odbcinst.ini appeared in which it was already indicated:
[MariaDB Unicode]
Driver=libmaodbc.so
Description=MariaDB Connector/ODBC(Unicode)
Threading=0
UsageCount=1
Then I configured the /etc/odbc.ini file:
[MySQL-asterisk]
Description = MySQL ODBC Driver
Driver = MariaDB Unicode
Server = localhost
Database = asteriskcdrdb
User = asteriskcdr
Password = ixnfo.com
Option = 3
Charset = utf8
Let’s check the connection to the MySQL database server:
isql -v MySQL-asterisk asteriskcdr ixnfo.com
show databases;
show tables;
select * from cdr;
quit
Let’s try to load the cdr_odbc.so module:
asterisk -rvvv
module load cdr_odbc.so
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 cdr_odbc.so module will be loaded automatically, or we will indicate that all modules will be loaded and disable loading unnecessary ones:
autoload = yes
;load => cdr_odbc.so
noload => cdr_custom.so
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
[global]
dsn=MySQL-asterisk
loguniqueid=yes ;Required to use the userfield
dispositionstring=yes
;newcdrcolumns=yes
;username=asteriskcdr
;password=ixnfo.com
;dbname=asteriskcdrdb
;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:
[MySQL-asterisk]
enabled => yes
dsn => MySQL-asterisk
pooling => no
limit => 1
pre-connect => yes
;username => asteriskcdr
;password => ixnfo.com
;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:
[macro-recording]
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});
[from-internal]
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:
- Configuring Asterisk CDR and Asterisk CDR Viewer
- Installing Asterisk from source
- More about Asterisk