Fixing CDR/CEL on Ubuntu/Debian Installation

General Help
1

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.

  1. 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.

  1. Test the odbc connection with:

    root@debian:~# 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

2 Likes
2

Thanks, I’ll update the wiki!

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

3

when using localhost, I get this:

root@debian:~# 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

4

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.

5

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.

6

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

7

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.

8

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

9

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

1 Like
10

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?)

11

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

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

1 Like
12

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.

13

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.

14

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
15

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.

16

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 :sunglasses:

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!’.

17

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

Same issue :frowning:

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
18

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

19

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.

20

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.