CDR not working with asterisk & odbc

cyberrock · July 2, 2023, 11:42pm

I had ODBC working with asterisk to write to my CDR then recently needed to check my CDR to realise nothing had been written for about 6 months. I had a look at my config, nothing seamed to have changed. but then tried to re-install everything except FREEPBX trying to get it to work. I have attempted several variations of config, and nothing is working… HOWEVER, I have now managed to avoid all other errors with the exceptoion of :

ERROR[2917] cdr_odbc.c: Unable to retrieve database handle. CDR failed.

NO errors before, of after.
Debian 10 w/ MariaDB 10 Asterisk 16 FreeBPX 16
Here is my config:

/etc/odbc.ini

[MySQL-asteriskcdrdb]
Description = MySQL connection to 'asteriskcdrdb' database
Driver = MySQL
Server = localhost
Database = asteriskcdrdb
Username = freepbxuser
Password = **PASSWORD**
Port = 3306
Socket = /var/run/mysqld/mysqld.sock
Option = 3

/etc/odbcinst.ini

[MySQL]
Description = ODBC for MySQL (MariaDB)
Driver = /usr/local/lib/libmaodbc.so
FileUsage = 1

/etc/asterisk/cdr_adaptive_odbc.conf

[adaptive_connection]
connection=MySQL-asteriskcdrdb
table=cdr
usegmtime=yes
alias start => calldate

/etc/asterisk/cdr_odbc.conf

[global]
dsn=MySQL-asteriskcdrdb
username=freepbxuser
password=**PASSWORD**
loguniqueid=yes
dispositionstring=yes
table=cdr
usegmtime=no
hrtime=yes
newcdrcolumns=yes

/etc/asterisk/res_odbc_additional.conf

[asteriskcdrdb]
enabled => yes
dsn => MySQL-asteriskcdrdb
pre-connect => yes
max_connections => 5
username => freepbxuser
password => **PASSWORD**
database => asteriskcdrdb

cdr show status

Call Detail Record (CDR) settings
    ----------------------------------
      Logging:                    Enabled
      Mode:                       Simple
      Log unanswered calls:       Yes
      Log congestion:             No
    
    * Registered Backends
      -------------------
        cdr-custom
        cdr_manager
        ODBC
        Adaptive ODBC

odbc show asteriskcdrdb

ODBC DSN Settings
-----------------

  Name:   asteriskcdrdb
  DSN:    MySQL-asteriskcdrdb
    Last fail connection attempt: 2023-06-27 14:29:46
    Number of active connections: 0 (out of 5)
    Logging: Disabled

Charles_Darwin · July 3, 2023, 9:23am

Is it a freePBX 16 system? If yes…there was a recent update. Now, there is a new setting in advanced-settings, where you have to switch cdr logging on.

BlazeStudios · July 3, 2023, 11:58am

Not that at all. This is an actual connection issue.

annabell · July 3, 2023, 12:53pm

Confirming that it’s a very real problem with FreePBX 16 and the new Framework module.

BlazeStudios · July 3, 2023, 1:03pm

These are not the same problems. The issue you are referring to turns off CDR logging which means it doesnt run and doesnt attempt to connect the backends like ODBC.

This issue is CDR logging is running but the ODBC connections are failing. Both issue result in no CDR logging but they are not the same thing.

Charles_Darwin · July 3, 2023, 1:15pm

You are right…but anyway he could check, if the CDR-logging is enabled in advanced-settings.

my cdr_adaptive_odbc.conf looks like this…by the way


`[asteriskcdrdb]
connection=asteriskcdrdb
loguniqueid=1
table=cdr
alias start => calldate`

no “mySQL”…same with the other config files…but I am no pro…

annabell · July 3, 2023, 2:20pm

cdr show status is a better indicator of whether CDR logging is enabled and working.
odbc show asteriskcdrdb always shows logging disabled in my testing.

cyberrock · July 3, 2023, 2:21pm

Hello all, thanks for your replies… well, you were right, I didn’t notice the new CDR button… however, if hasnt changed anything…still comes up with that 1 error message. also changed /etc/asterisk/cdr_adaptive_odbc.conf to

[asteriskcdrdb]
connection=MySQL-asteriskcdrdb
table=cdr
alias start => calldate

still doesnt work

Charles_Darwin · July 3, 2023, 2:53pm

Be careful, when you edit the configs…always keep a backup of the original file. As I said, I am no pro, but here are my versions of the config files you posted above. I would suggest that you wait until a pro tells you what to do

cdr_adaptive_odbc.conf

[asteriskcdrdb]
connection=asteriskcdrdb
loguniqueid=1
table=cdr
alias start => calldate

res_odbc_additional.conf

[asteriskcdrdb]
enabled=>yes
dsn=>MySQL-asteriskcdrdb
pre-connect=>yes
max_connections=>5
username=>freexxx
password=>xxx
database=>asteriskcdrdb

odbc.ini

[MySQL-asteriskcdrdb]
Description=MySQL connection to 'asteriskcdrdb' database
driver=MySQL
server=localhost
database=asteriskcdrdb
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3
Charset=utf8

odbcinst.ini

# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description	= ODBC for PostgreSQL
Driver		= /usr/lib/psqlodbcw.so
Setup		= /usr/lib/libodbcpsqlS.so
Driver64	= /usr/lib64/psqlodbcw.so
Setup64		= /usr/lib64/libodbcpsqlS.so
FileUsage	= 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description	= ODBC for MySQL
Driver		= /usr/lib/libmyodbc5.so
Setup		= /usr/lib/libodbcmyS.so
Driver64	= /usr/lib64/libmyodbc5.so
Setup64		= /usr/lib64/libodbcmyS.so
FileUsage	= 1

I don’t have a cdr_odbc.conf…
Anyway, when you change the configs restart asterisk…even better do a “fwconsole restart”…

EDIT: Just realized that you don’t have a freePBX distro…so forget my configs…

annabell · July 3, 2023, 4:32pm

Try changing your username entries to root and password entries to passw0rd with a zero. Then reboot.

Charles_Darwin · July 3, 2023, 4:46pm

That sounds very weird…why should he do it?

annabell · July 3, 2023, 5:32pm

Because it sounds like the existing settings are out of sync between the config files and the entry in the MySQL table.

dicko · July 3, 2023, 6:01pm

Because if he/she is using Ward Mundy’s PIAF, that is how the root mysql account is ACL’d

Charles_Darwin · July 3, 2023, 6:03pm

I see…root…passw0rd…that’s definitely the highest security level one can think of

dicko · July 3, 2023, 6:05pm

Well,perhaps marginally better than absolutely none on 127.0.0.1 for if/when your webserver’s buffer overflow allows such access, yes that has happened way more than once.

annabell · July 3, 2023, 7:28pm

dicko · July 3, 2023, 8:26pm

there is no default username or password in the ‘distro’, the root ACL is however restricted to 127.0.0.1 for what it’s worth.

BlazeStudios · July 3, 2023, 10:20pm

This means CDR Logging is enabled.

dicko · July 3, 2023, 10:56pm

The Adaptive ODBC backend also needs to be registered for the gooey to show them,

cyberrock · July 4, 2023, 6:40am

I have tried everything above, a number of times. I even found an old backup from feb. feb still works, i imported all my “working” config and still get that error message… the only thing I think is possible, in the last5-6 months, I have added a conflicting package or damaged a package whilst doing something else