Login
Wiki HomeHow to download cdr_mysql
Due to Mysql client libraries licensing, the Mysql billing application is no longer an integrated part of the Asterisk standard distribution. It is now located in the asterisk-addons CVS directory.Follow the instructions on http://www.asterisk.org/index.php?menu=download for Subversion download
# cd /usr/src
# svn checkout http://svn.digium.com/svn/asterisk-addons/branches/1.2 asterisk-addons-1.2
You must have mysql and mysql-devel packages installed.
- In an RPM-based Linux, you can check presence of MySQL like this:
- rpm -qa | grep mysql
- For debian or other dpkg-based systems, check like this:
- dpkg -l mysql-server libmysqlclient*dev
- In FreeBSD, you'll find MySQL in the ports library, /usr/ports/databases/mysql4-server
Compile
# cd asterisk-addons-1.2# make clean
# make
# make install
Check that in make stage that there are no mysql.h errors, which mean you are missing mysql-devel package
root@localhost asterisk-addons-1.2# make
./mkdep -fPIC -I../asterisk -D_GNU_SOURCE `ls *.c`
app_addon_sql_mysql.c:23:19: error: mysql.h: No such file or directory
cdr_addon_mysql.c:38:19: error: mysql.h: No such file or directory
cdr_addon_mysql.c:39:20: error: errmsg.h: No such file or directory
res_config_mysql.c:53:19: error: mysql.h: No such file or directory
res_config_mysql.c:54:27: error: mysql_version.h: No such file or directory
res_config_mysql.c:55:20: error: errmsg.h: No such file or directory
If make fails due to complaining about a missing "asterisk.h" file you can either copy this file from your asterisk directory, or create a soft link ("ln -s ...") for /usr/src/asterisk that points to your asterisk source directory.
A sample configuration file, can be found on the cdr_mysql.conf page.
Copy the sample configuration file to /etc/asterisk/cdr_mysql.conf and edit it according to your requirements. Then edit your modules.conf to load cdr_addon_mysql.so and finally restart asterisk; before the restart you should, however, check that your cdr table has been created correctly and is accessible to the username and password you specified.
Table definitions for Asterisk cdr_mysql
This is the database definition you use to install in Mysql to support billing.Create the database
mysql --user=root --password=password [-h dbhost]
CREATE DATABASE asterisk;
GRANT INSERT
ON asterisk.*
TO asterisk@localhost
IDENTIFIED BY 'yourpassword';
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 '',
`userfield` varchar(255) NOT NULL default ''
);
ALTER TABLE `cdr` ADD `uniqueid` VARCHAR(32) NOT NULL default '';
ALTER TABLE `cdr` ADD INDEX ( `calldate` );
ALTER TABLE `cdr` ADD INDEX ( `dst` );
ALTER TABLE `cdr` ADD INDEX ( `accountcode` );
Please note that the rights granted in GRANT above is the _least_ the asterisk user will need. To allow the user to do more than just add new data to the table, see the MySQL manual on the topic
for trunk version since 29 Dec 2007 must be:
GRANT INSERT, SELECT ...
because cdr_addon_mysql now do DESC 'cdr'; for check tables fields.
Hint: Copy and paste this SQL command into a text file, save it under an appropriate name, then execute the following command:
mysql --user=username --password=password databasename < nameoftextfile
Voila! The table is created for you. (:biggrin:)
An interesting note: on versions prior to v1.2, the name of the table, 'cdr', is hard-coded into the mysql interface, so if the table is created under a different name, the mysql CDR backend will not work. In v1.2 the table name is configurable via the "table=" option in cdr_mysql.conf
Yet another note: It probably isn't a good idea to use the InnoDB engine. Asterisk keeps autocommit turned on by default; therefore, each INSERT query is its own transaction. This will cause the query to take significantly longer to execute. On my personal computer, the "clock time" penalty is about 4-7 times that of MyISAM on a default Ubuntu install. Please also note that the CDR table does not require the features that the InnoDB engine provides.
Storing the Unique ID
Q: It would appear that the "uniqueid" field is not being populated in the MySQL CDR DB. Is this an obsolete field or is a bug?A: You need to define MYSQL_LOGUNIQUEID at compile time for it to use that field.
You have two options in /usr/src/asterisk-addons:
1. Either add CFLAGS+=-DMYSQL_LOGUNIQUEID to the Makefile.
Note that recently (around Asterisk 1.4.18 or before) this has changed to ASTCFLAGS+=-DMYSQL_LOGUNIQUEID
2. Or instead add a #define MYSQL_LOGUNIQUEID to the top of cdr_addon_mysql.c.
Finally perform the usual make clean, make, make install. Be sure to check the Makefile for the presence of this flag after having done a CVS update! You will most probably also want to index the uniqueid field in your cdr table to improve performance.
You will also have to add a `uniqueid` column in your mysql database after the `accountcode` column:
ALTER TABLE `cdr` ADD `uniqueid` VARCHAR(32) NOT NULL default ''
after `accountcode`;
What would I need all this for? For example you are running an AGI script and would like to be able to related AGI data with the CDR table. The problem is that the AGI script will lose connection to the call as soon as the caller hangs up, so you'll need a way to find the correct cdr entry (that'll also be created only after the call has been completed).
If you get the following error while compiling cdr_mysql:
/usr/bin/ld: cannot find -lz
Make sure you have the zlib-devel package installed.
See also
- Asterisk cdr odbc
- Asterisk billing
- Asterisk config cdr_mysql.conf
- Asterisk voicemail database
- Asterisk CDR csv mysql import
- Asterisk CDR csv mysql Perl import
- Asterisk queue_log on MySQL
- Asterisk CDR csv conversion mysql
- Asterisk CDR Master.csv postgresql Perl import
- What is CDR
Page Changes
configuration of database cdr for phpmyadmin
Specifically, I need to know the meaning of the column of the database phpmyadmin.
That is when I seek the calls that were made from four per day example, I get calls and one of the fields in the database 'cdr' is called 'diposition' and 'amaflags'. Well this is the question, when I place a call to my mobile for example from the extension 801 I cuelgo the call, that is, I have not responded yet in the table of my database this field as I get 'ANSWER '(and I have not off-hook).
The other issue is the field amaflags not what it means. And it happens it is always 3, and if they are not seconds or they are.
Thanks and a greeting Ismael Perez.
configuración base datos cdr phpmyadmin
Concretamente necesito saber el significado de la columna de la base de datos phpmyadmin.
Es decir cuando busco las llamadas que se han hecho a partir del dia cuatro por ejemplo, me aparecen las llamadas y uno de los campos de la base de datos 'cdr' se llama 'diposition' y 'amaflags'. Bueno pues esta es la cuestión, cuando realizo una llamada por ejemplo a mi movil desde la extensión 801 yo cuelgo la llamada, es decir, no la he respondido, sin embargo en la tabla de mi base de datos este campo me aparece como 'ANSWER'(y no he descolgado).
La otra cuestión es la del campo amaflags que no se lo que significa. y da la casualidad que siempre está en 3, y no se si son segundos o que son.
Gracias y un saludo de Ismael Pérez.
RE : Error loading cdr_addon_mysql.so
The error was when loading cdr_addon_mysql : "Module 'cdr_addon_mysql.so' does not provide a description."
So near the end of cdr_addon_mysql.c in the function my_load_module() (line 457) there is :
res = ast_cdr_register(name, desc, mysql_log)
should be replaced by :
res = ast_cdr_register(name, ast_module_info->description, mysql_log)
Then the module could be loaded without the error.
Error loading cdr_addon_mysql.so
I've got a problem while loading the module cdr_addon_mysql. Every things was working fine, but i wanted to upgrade to the latest stable release of asterisk (1.4.13), so I updated the asterisk addons package too (version 1.4.4). Then asterisk try to load this module, in the console i can see this message :
Oct 22 11:57:45 WARNING28226: loader.c:650 load_resource: Module 'cdr_addon_mysql.so' could not be loaded.
I tried also with the svn version of asterisk addons, same problem.
Can anybody help me ?
Re: Transferring CDR data into mysql.. HELP!!!!!
Transferring CDR data into mysql.. HELP!!!!!
i am new in asterisk and linux, i need to transfer the CDR into database... can anybody help me out by providing proper guidance....
Transferring CDR data into mysql.. HELP!!!!!
i am new in asterisk and linux, i need to transfer the CDR into database... can anybody help me out by providing proper guidance....
Re: Failed to connect to mysql database, why?
Storing to multiple databases