Fixing CDR/CEL on Ubuntu/Debian Installation

morpix · September 1, 2015, 10:01pm

Hi there,

This is just a fix guide for those struggling to get CDR’s working with FreePBX 13 + Asterisk 13 on an Ubuntu or Debian install. It applies to any of these 2 guides:

http://wiki.freepbx.org/display/HTGS/Installing+FreePBX+13+on+Debian+8.1
http://wiki.freepbx.org/display/HTGS/Installing+FreePBX+13+on+Ubuntu+Server+14.04.2+LTS

After the install, when reloading, you’ll see the following in the Asterisk console:

[2015-09-02 08:23:10] NOTICE[1316] res_odbc.c: Connecting asteriskcdrdb
[2015-09-02 08:23:10] WARNING[1316] res_odbc.c: res_odbc: Error SQLConnect=-1 errno=0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified
[2015-09-02 08:23:10] WARNING[1316] res_odbc.c: Failed to connect to asteriskcdrdb
[2015-09-02 08:23:10] WARNING[1316] cdr_adaptive_odbc.c: No such connection 'asteriskcdrdb' in the 'asteriskcdrdb' section of cdr_adaptive_odbc.conf.  Check res_odbc.conf.

So, we have to fix odbc before any records will be written:

1: Install libmyodbc with "apt-get install libmyodbc"
2: Edit /etc/odbcinst.ini and add the following:

[MySQL]
Description = ODBC for MySQL
Driver      = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Setup       = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
FileUsage   = 1

You can use find / -name ‘libmyodbc.so’ to verify these paths, but that should be where they land.

Edit /etc/odbc.ini and change the following:

[MySQL-asteriskcdrdb]
Description=MySQL connection to ‘asteriskcdrdb’ database
driver=MySQL
server=localhost
database=asteriskcdrdb
Port=3306
Socket=/var/run/mysqld/mysqld.sock
option=3

The socket path is incorrect and should be fixed.

Test the odbc connection with:

[email protected]:~# isql -v MySQL-asteriskcdrdb
±--------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
±--------------------------------------+
SQL>

The res_odbc error should be gone and you’ll see CDR reports in FPBX.

Hope this helps anyone else struggling to get CDR’s going on a custom install.

Regards,
Juan

xrobau · September 1, 2015, 10:03pm

Thanks, I’ll update the wiki!

Edit: What’s the problems with the socket connector? That should work perfectly?

morpix · September 1, 2015, 10:04pm

when using localhost, I get this:

[email protected]:~# isql -v MySQL-asteriskcdrdb
[08S01][unixODBC][MySQL][ODBC 5.1 Driver]Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[ISQL]ERROR: Could not SQLConnect

Might be something in particular with the socket connector rather than tcp/ip. So yeah, it works with 127.0.0.1

xrobau · September 1, 2015, 10:09pm

Is that the correct location for the socket file? I think it’s in a different location.

Edit: I’ve updated the Debian 8.1 docs, but I still think the socket location is wrong. I don’t have a Debian 8.1 machine up at the moment to check.

morpix · September 1, 2015, 10:11pm

That’s what’s specified by default in /etc/odbc.ini. So if that’s the wrong path, it needs to be fixed anyway.

edit I’ll gladly try different paths if you want me to. I have a fresh Debian setup infront of me.

xrobau · September 1, 2015, 10:12pm

Just have a look at /etc/my.cnf, that should tell you what the socket file is.

morpix · September 1, 2015, 10:14pm

Ahaa… Sorted… The path in /etc/odbc.ini is wrong. Should be /var/run/mysqld/mysqld.sock

Using that, localhost works fine.

Edit: I’ll edit my original post too.

dicko · September 1, 2015, 10:15pm

That would need to be agreed by /etc/mysql/debian.cnf in debian based systems

morpix · September 1, 2015, 10:17pm

Found it in /etc/mysql/my.cnf. Fresh and updated 8.1.0

xrobau · September 1, 2015, 10:19pm

Done and updated. Any other problems with Deb 8.1? That’s my favourite distro at the moment, but I didn’t actually check that. Whoops 8-\

I’ll have to build a 14.04 machine and see where that socket is (unless someone else has one handy and can check?)

dicko · September 1, 2015, 10:20pm

hehe yes, systemd (but that’s a personal opinion)

http://without-systemd.org/wiki/index.php/How_to_remove_systemd_from_a_Debian_jessie/sid_installation

morpix · September 1, 2015, 10:36pm

On a side-note Rob…

I couldn’t get the automatic startup to work with a systemd script. It would always fail.

Sep  2 07:48:57 debian systemd[1]: freepbx.service: main process exited, code=exited, status=1/FAILURE
Sep  2 07:48:57 debian systemd[1]: Failed to start FreePBX VoIP Server.
Sep  2 07:48:57 debian systemd[1]: Unit freepbx.service entered failed state.

So I just made asterisk start with the system by running “update-rc.d asterisk defaults” and disabling the freepbx.service stuff altogether.

It works fine. I guess without FPBX setting permissions, etc.

xrobau · September 1, 2015, 10:37pm

What was the log before that? The one where it displays all the output…

Don’t use the Asterisk startup script, as it runs as root. The last thing you want is Asterisk running as root. Not only is it a security issue, it’ll also break voicemails.

morpix · September 1, 2015, 10:41pm

EDIT: Spoke to soon…

Sep  2 10:44:25 debian fwconsole[460]: [FATAL] SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Sep  2 10:44:25 debian fwconsole[460]: Trace Back:
Sep  2 10:44:25 debian fwconsole[460]: /var/www/html/admin/libraries/BMO/Database.class.php:80 PDO->__construct()
Sep  2 10:44:25 debian fwconsole[460]: [0]: mysql:host=localhost;dbname=asterisk;charset=utf8
Sep  2 10:44:25 debian fwconsole[460]: [1]: freepbxuser
Sep  2 10:44:25 debian fwconsole[460]: [2]: XXXXXXXXxxxXXXXXXX
Sep  2 10:44:25 debian fwconsole[460]: /var/www/html/admin/libraries/BMO/FreePBX.class.php:69 FreePBX\Database->__construct()
Sep  2 10:44:25 debian fwconsole[460]: /var/www/html/admin/bootstrap.php:138 FreePBX->__construct()
Sep  2 10:44:25 debian fwconsole[460]: /etc/freepbx.conf:9 require_once()
Sep  2 10:44:25 debian fwconsole[460]: [0]: /var/www/html/admin/bootstrap.php
Sep  2 10:44:25 debian fwconsole[460]: /var/lib/asterisk/bin/fwconsole:12 include_once()
Sep  2 10:44:25 debian fwconsole[460]: [0]: /etc/freepbx.conf
Sep  2 10:44:25 debian systemd[1]: freepbx.service: main process exited, code=exited, status=1/FAILURE

dicko · September 1, 2015, 10:43pm

Ah the wonders of systemd, everything works and you need nothing in /etc/rc* (your rc.local is empty, right? )

nano /etc/default/asterisk

to run asterisk as whoever you want.

xrobau · September 1, 2015, 10:43pm

You 100% need to have it after Maria. Otherwise it’ll randomly error when trying to startup. Add the ‘After=mariadb.service’ back, so it knows that it can’t start FreePBX until the database is up.

If this is wrong, I’m happy to fix it, but this should be right

http://wiki.freepbx.org/display/HTGS/Example+systemd+startup+script+for+FreePBX

Edit: Hehehehehe. I just saw you edit your post with the ‘Oh no, it failed because Maria wasn’t started!’.

morpix · September 1, 2015, 10:49pm

Rebooting again. I couldn’t find that mariadb service on Debian, so I figured it’s CentOS specific.

Same issue

Sep  2 10:48:35 debian fwconsole[459]: [FATAL] SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Sep  2 10:48:35 debian fwconsole[459]: Trace Back:
Sep  2 10:48:35 debian fwconsole[459]: /var/www/html/admin/libraries/BMO/Database.class.php:80 PDO->__construct()
Sep  2 10:48:35 debian fwconsole[459]: [0]: mysql:host=localhost;dbname=asterisk;charset=utf8
Sep  2 10:48:35 debian fwconsole[459]: [1]: freepbxuser
Sep  2 10:48:35 debian fwconsole[459]: [2]: XXXXXXXXxxxXXXXXXX
Sep  2 10:48:35 debian fwconsole[459]: /var/www/html/admin/libraries/BMO/FreePBX.class.php:69 FreePBX\Database->__construct()
Sep  2 10:48:35 debian fwconsole[459]: /var/www/html/admin/bootstrap.php:138 FreePBX->__construct()
Sep  2 10:48:35 debian fwconsole[459]: /etc/freepbx.conf:9 require_once()
Sep  2 10:48:35 debian fwconsole[459]: [0]: /var/www/html/admin/bootstrap.php
Sep  2 10:48:35 debian fwconsole[459]: /var/lib/asterisk/bin/fwconsole:12 include_once()
Sep  2 10:48:35 debian fwconsole[459]: [0]: /etc/freepbx.conf
Sep  2 10:48:35 debian systemd[1]: freepbx.service: main process exited, code=exited, status=1/FAILURE

xrobau · September 1, 2015, 10:52pm

Again, I don’t have a Debian 8.1 machine up, so you’ll need to figure out what the service name is yourself. Possibly mysql.service?

systemctl list-unit-files --type=service

morpix · September 1, 2015, 10:57pm

Looks like mysql isn’t started by systemd… I’ll create a startup script for it and remove it from rc.X
Do you have an example of what a mysql systemd startup script looks like? Just incase it needs to start after something else too.

xrobau · September 1, 2015, 11:04pm

Wow. This looks like a bit of a rats nets here.

https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=742900

I don’t know what I can do when the package maintainers can’t figure out a way.

Apparently ‘After=mysql.service’ should work though, even if it’s not a systemd service.