CFLAGS+=-DMYSQL_LOGUNIQUEID
and then add this line to the dr_addon_mysql.c :
#define MYSQL_LOGUNIQUEID
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;
mysql-5.0.37-linux-i686.tar.gz
mysql-5.0.37.tar.gz
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 nodule 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 sonsole 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:
% asterisk start
% asterisk -rvvvvvvv
Asterisk 1.4.4, Copyright (C) 1999 - 2006 Digium, Inc. and others.
Created by Mark Spencer <markster@digium.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
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:
% mysql <build.sql
[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!
christo
