I have an installation of freepbx 13 with asterisk 13.8 on ubuntu 15.04 , unfortunately the cdr is not working.
Database and table are created but no records.
According to freepbx (link included for reference) there is a bug in mysql : http://wiki.freepbx.org/display/FOP/FreePBX+with+MariaDB+10.x+in+Ubuntu+12.04+and+later
I can do isql and it works but some commands will result Floating point exception error.
I have installed postgresql database in parallel to the mysql in order to only use for cdr. now the CDR records are well written to the posgresql database created.
My problem is how to read the cdr from the freepbx gui ?
I appreciate if someone can help.
Note: the myql connector odbc version is 5.1, I did not find a newer version to try since my board is based on arm processor.
Mariadb was not an option for me since I could not compile on my arm based board.
yes all is installed. i can do isql asteriskcdrdb and it connects.
odbc.ini and odbcinst.ini are configured and the driver was installed with the command odbcinst -q -d I get
Mysql as well as Postgresql.
Actually the same is working fine on ubuntu 14.04. Unfortunately the 14.04 version is not good for my arm based board and that is why I had to switch to 15.04.
The CDR is the only issue I have on 15.04 and as I mentioned earlier CDR is able to write entries into postgress however I cannot read them on the freepbx gui.
FreePBX gui has nothing to do with Asterisk ODBC connection. If data is going in CDR table then You needs to check your user rights which FreePBX uses to read data from CDR table. You can always do amportal a dbug to debug freepbx. Going through debug logs might help you and take you in right direction.
Freepbx gui is always looking at cdr table in asteriskcdrdb located at the mysql database where the cdr table is now on postgresql.
no problems with user rights.
Hey Tedd777,
I think you are looking for a solution to read CDR from postgresql, but I am giving you a possible solution for MySQL which worked well in my setup with similar issue. You may give a try if you would like to:
nano /etc/asterisk/cdr_adaptive_odbc.conf
[adaptive_connection]
connection=asteriskcdrdb
table=cdr
alias start => calldate
Modified the /etc/odbc.ini to reflect
[MySQL-asteriskcdrdb]
Description=MySQL connection to ‘asterisk’ database
driver=MySQL
server=localhost
database=asteriskcdrdb
username=freepbx (user name and password from /etc/freepbx.conf)
password=fpbx
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3
module reload res_odbc.so
module reload cdr_adaptive_odbc.so
restart Asterisk
You can run below commands to confirm CDR, MySQL and ODBC connections:
cdr show status
module show cdr
module show mysql
odbc show
output of command isql Mysql-asteriskcdrdb
isql Mysql-asteriskcdrdb then I try to show databases
±--------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
±--------------------------------------+
SQL> show databases
Floating point exception
output of odbc show
ODBC DSN Settings
Name: asteriskcdrdb
DSN: MySQL-asteriskcdrdb
Last connection attempt: 2016-04-19 16:34:13
output of module reload cdr_adaptive_odbc.so
Module ‘cdr_adaptive_odbc.so’ reloaded successfully.
– Reloading module ‘cdr_adaptive_odbc.so’ (Adaptive ODBC CDR backend)
== Parsing ‘/etc/asterisk/cdr_adaptive_odbc.conf’: Found
[2016-04-19 16:43:51] WARNING[9077]: res_odbc.c:821 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=1045 [unixODBC]
[2016-04-19 16:43:51] WARNING[9077]: cdr_adaptive_odbc.c:135 load_config: No such connection ‘asteriskcdrdb’ in the ‘adaptive_connection’ section of cdr_adaptive_odbc.conf. Check res_odbc.conf.
My cdr records are well written into postgresql but not to the mysql for above (module reload cdr_adaptive_odbc.so)obvious reason.
Mysql Server version: 5.6.28-0ubuntu0.15.04.1 (Ubuntu)
ODBC connector version is 5.1 and could not higher that at all.
It could be the problem with the connector as the asterisk is able to write configurations and interact with the mysql database “asterisk”
I am not getting any errors on php level. The thing is that freepbx was installed using the following 2 commands:
./start_asterisk start
./install -n
and to my understanding they were meant to work with mysql and not postgresql
that leads to the conclusion of not being able to pull the info from postgresql. I was trying a workaround solution just for the cdr portion.
Mysql is necessary as I have sugarcrm on the same machine .
I was wondering if a solution exist to point freepbx GUI to pull just the cdr from the postgresql since asterisk is able to write them correctly there.
We use PDO but it connects to one or the other. So you can’t have your cdr on postgres and FreePBX on mysql and talk to both. Is there a reason you want to use postgres?
I do not wish to use postgresql in fact it is a burden due to resources usage.
The postgresql idea was just to verify if cdr was pushing something into the database.
My wish is to have the CDR entries written to the mysql but there is a problem that I cannot overcome hence my above posts and the call for help.
I have not tried FreePBX with postgresql however as jfinstrom mentioned that it should connect to one or the other. So If use either MySQL or Postgre then it should work by default. However I would love to try that i my local setup.
Now I have the impression that I have to move my asterisk entirely over postgresql in order to have all working homogeneously.
Should there be no solution to make the CDR work on mysql I will be obliged to migrate to the other.
The question is how to make the pbx point to postgresql ? do I have to break my configuration and re-install freepbx ?
What are the procedures?
One issue I saw in your configuration is below:
Under etc/asterisk/res_odbc_additional.conf change context name from [adaptive_connection] to [asteriskcdrdb] and I hope user name/password used here are taken from /etc/freepbx.conf file.
Also add database=>asteriskcdrdb in this file at last.
Second, odbc.ini does not need user name / password entry normally. They are added in your configuration, but i am not sure if this would make any difference.
After above changes (at least first one), reload the res_odbc.so, cdr_adaptive_odbc.so and restart Asterisk.
For the benefit of the doubt I decided to install the same on a VM ubuntu 15.10 to try.
I still believe that a problem with the connector version that is causing the problem.
I tried to make it work with cdr_odbc.conf but the error is now different
res_odbc.c:821 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=1045 [unixODBC]
cdr_odbc.c:175 odbc_log: Unable to retrieve database handle. CDR failed.
So my guess that anything related to mysql on the cdr front is failing, at the same time there is no problem pushing the CDR data towards postgresql.
I will publish the results here but at the same time I was expecting some interaction from the freepbx team concerning this problem.
My assumptions were right, unfortunately the mysql-connector-odbc version 5.1 is not good.
With standard installation of asterisk and freepbx , you do not need to change any file except the odbcinst.ini (to point the odbc to the correct location of libxxxxx.so files)
Loading the mysql-connector-odbc version 5.3.6 onto i386 Ubuntu 15.04 or 15.10 would solve the problem.
Unfortunately this driver does not exist for arm based processors , could not compile it either on the board and do not know how to cross compile!!
@jeanne thank you for your reply, as they say better too late than never.
Well @Marbled could have been right should I remained interested into the dev board. I dropped that due to this specific issue that no one could help at that time.
Since you replied , could you please let me know what to change in res_odbc.conf?
the file res_odbc_additional.conf has the following
;--------------------------------------------------------------------------------;
; Do NOT edit this file as it is auto-generated by FreePBX. ;
;--------------------------------------------------------------------------------;
; For information on adding additional paramaters to this file, please visit the ;
; FreePBX.org wiki page, or ask on IRC. This file was created by the new FreePBX ;
; BMO - Big Module Object. Any similarity in naming with BMO from Adventure Time ;
; is totally deliberate. ;
;--------------------------------------------------------------------------------;
[asteriskcdrdb]
enabled=>yes
dsn=>MySQL-asteriskcdrdb
pooling=>no
limit=>1
pre-connect=>yes
username=>freepbxuser
password=>xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
database=>asteriskcdrdb