and then add this line to the dr_addon_mysql.c :
These two small changes will enable your system to include the uniqueid in the cdr records.
Now to set up your mysql environment, start off by downloading these two archives from mysql.com mirrors;
The first mysql download will provide the binary installation for our cdr database, and the other to provide matching sources and headers to compile libraries against. These are required for building the app_addon_sql_mysql.so module in asterisk-addons. This is the module that we will load into asterisk to provide the mysql cdr functionality.
moved mysql-5.0.37-linux-i686-icc-glibc23.tar.gz to /usr/local, extracted it and create mysql sylmlink:
626 mv mysql-5.0.37-linux-i686.tar.gz /usr/local/ 627 cd /usr/local/ 628 tar -zxvf mysql-5.0.37-linux-i686.tar.gz 524 ln -s mysql-5.0.37-linux-i686 mysql 525 cd mysql 526 ls 527 scripts/mysql_install_db --user=mysql 528 chown -R root 529 chown -R root . 530 chwon -R mysql data 531 chown -R mysql data 532 chgrp -R mysql . 533 bin/mysqld_safe --user=mysql &
Now just to check that the mysql server is running:
root@ser-6:/usr/local/mysql# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 6 Server version: 5.0.37 MySQL Community Server (GPL) Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> quit Bye root@ser-6:/usr/local/mysql#
looks good. The next stage is to compile the mysql client libraries - this will create the libmysqlclient.so which is needed to build the asterisk mysql cdr addon.
537 cd /usr/src/ 539 tar -zxvf mysql-5.0.37.tar.gz 540 cd mysql-5.0.37 543 ./configure --without-server 544 make && make install
Now you can recompile asterisk-addons, but note, if you try to load the module into asterisk straight away, you may get the following error:
Error loading module 'app_addon_sql_mysql.so': libmysqlclient.so.15
To overcome this, make sure that you update your /etc/ld.so.conf and then run ldconfig. You will need to include the path to your libmysqlclient directory by adding /usr/local/lib/mysql into the /etc/ld.so.conf. If you have installed your mysql client libs elsewhere, then just check that your linker can find them!
If you're still having problems, this may be because you actually have to recompile asterisk aswell. I'm a bit unclear on this, but as far as I can make out, this is purely an architectural issue - Asterisk is based on a monolithic architecture and doesn't provide the full modular functionality that you often get with server apps such as Apache, or even the linux kernel itself, so sometimes a full recompile is required to build in what should be just modular extensions. Perhaps somebody can clarify this.
Once you're done compiling, there are three ways to check whether or not your module works. These are:
1) Edit your /etc/asterisk/modules.conf and ensure that the cdr_addon_mysql.so is in the load list. You sholud see a line like:
load => cdr_addon_mysql.so
You can then load asterisk in console mode and watch the output to see if any errors are generateed when the module is loaded. Do this by running:
% asterisk -c
2) Another way to achieve all this is to start asterisk, and then to fire up the asterisk cli and query the list of loaded modules. Do it like this:
The Asterisk CLI will then list all the loaded modules. You should see these two modules in the list:% asterisk start % asterisk -rvvvvvvv Asterisk 1.4.4, Copyright (C) 1999 - 2006 Digium, Inc. and others. Created by Mark Spencer <email@example.com> Asterisk comes with ABSOLUTELY NO WARRANTY; type 'core show warranty' for details. This is free software, with components licensed under the GNU General Public License version 2 and other licenses; you are welcome to redistribute it under certain conditions. Type 'core show license' for details. ========================================================================= == Parsing '/etc/asterisk/asterisk.conf': Found == Parsing '/etc/asterisk/extconfig.conf': Found Connected to Asterisk 1.4.4 currently running on ser-6 (pid = 27969) Verbosity was 0 and is now 6 ser-6*CLI> module show
At this stage, however, asterisk will still complain that it can't connect to the cdr database. This is because we haven't created one!app_addon_sql_mysql.so Simple Mysql Interface 0 res_config_mysql.so MySQL RealTime Configuration Driver 0
Next we have to prepare the database for asterisk to write it's CDR data to. This will be a plain old mysql database with a table called cdr. You can call the database whatever you want. In this case I will call the database 'pipes' (because I'm setting it up for Confonia's pipes product) and the table 'cdr'. The cdr table is where asterisk will record it's 'call data records'.
The simplest way to do this is to create a text file containing all the SQL required to create your CDR database. I created one which includes a 'uniqueid' column which I will use to store the uniqueid of every call logged. The file should look like this:
CREATE DATABASE pipes; GRANT INSERT ON pipes.* TO pipes@localhost IDENTIFIED BY 'putpasswordhere'; 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 '', `uniqueid` varchar(32) NOT NULL default '', `userfield` varchar(255) NOT NULL default '' ); ALTER TABLE `cdr` ADD INDEX ( `calldate` ); ALTER TABLE `cdr` ADD INDEX ( `dst` ); ALTER TABLE `cdr` ADD INDEX ( `accountcode` );
Note that because I have only just installed the database and I haven't set up the grants and passwords yet, I can simply run mysql as root and redirect this file into the process. Note, it is bad practice to leave your mysql in it's default post-installed state, so in a second we will run through the post-installation steps required to lock down your mysql server and ensure that nobody can tamper with your data.
So to create your database with the cdr table within it, just save the above file, say as build.sql and run this command:
We're still not quite there. We need to tell Asterisk where to find the database and the authentication details it will need to connect. This is done in the /etc/asterisk/cdr_mysql.conf. This file should look like this:% mysql <build.sql
You will now be able to restart your asterisk server and make a test call. Your call record should look something like this:[global] hostname=localhost dbname=pipes password=putpasswordhere user=pipes
mysql> select * from cdrG *************************** 1. row *************************** calldate: 2007-04-30 09:29:00 clid: 7918160320 src: 7918160320 dst: 810204 dcontext: pipes channel: IAX2/pipes-1 dstchannel: lastapp: AGI lastdata: pipes.pl duration: 8 billsec: 8 disposition: ANSWERED amaflags: 3 accountcode: uniqueid: 1177921740.0 userfield: 1 row in set (0.00 sec)
And we're done. I hope that's useful to somebody. Since I have gone through quite a lot of mucking about to actually get this all to play nicely!