Unable to use cdr_adaptive_odbc

Hi, I’m trying to setup cdr logged to a mysql server. cdr_odbc works fine, but cdr_adaptive_odbc does not work; both use the same odbc connection and cdr table.

OS: Ubuntu 22.04
Asterisk: 20.8.1
Mysql: 8.0.36

This is my odbc connection status

CLI> odbc show all

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

  Name:   mysql
  DSN:    asterisk-mysql
    Number of active connections: 1 (out of 1)
    Logging: Disabled

My res_odbc.conf

[mysql]
enabled => yes
dsn => asterisk-mysql
username => mysql_asterisk_user
password => mysql_asterisk_password
pre-connect => yes
sanitysql => select 1
max_connections => 1
forcecommit => no
isolation => read_committed
backslash_is_escape => yes
connect_timeout => 5
negative_connection_cache => 300

This is how I create the cdr table in msyql 8.0

CREATE TABLE `cdr` (
  `calldate` timestamp NOT NULL,
  `clid` varchar(80) NOT NULL,
  `src` varchar(80) NOT NULL,
  `dst` varchar(80) NOT NULL,
  `dcontext` varchar(80) NOT NULL,
  `channel` varchar(80) NOT NULL,
  `dstchannel` varchar(80) NOT NULL,
  `lastapp` varchar(80) NOT NULL,
  `lastdata` varchar(80) NOT NULL,
  `duration` int NOT NULL,
  `billsec` int NOT NULL,
  `disposition` varchar(45) NOT NULL,
  `amaflags` int NOT NULL,
  `accountcode` varchar(20) NOT NULL,
  `uniqueid` varchar(150) NOT NULL,
  `userfield` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

This is the working cdr_odbc

[global]
dsn=mysql
loguniqueid=yes
dispositionstring=yes
table=cdr  
usegmtime=no 
hrtime=no
newcdrcolumns=no

This is my cdr_adaptive_odbc.conf

[my_cdr_odbc]
connection=mysql
table=cdr
schema=call_center
alias calldate => start

When I reload or restart asterisk, CLI show this:

[2024-06-07 17:44:53] ERROR[2282087]: cdr_adaptive_odbc.c:172 load_config: Unable to query database columns on connection 'mysql'.  Skipping.

Please help!

I’ve checked the mysql general log. Nothing showed up when CLI printed this message. No actual query was made.

Finally, I found a solution.
I’m using MySQL 8.0.36 on Ubuntu 22.04
The odbc connector is 8.4.0 (I installed 8.0.28, which works fine on U20, but I cannot register the driver. It says “myodbc-installer: error while loading shared libraries: libcrypto.so.1.1: cannot open shared object file: No such file or directory”)

I looked into cdr_adaptive_odbc.c, where it print the error message. Print out the odbc error message:


                if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
                        ast_log(LOG_ERROR, "Unable to query database columns on connection '%s'.  Skipping.\n", connection);

                        // start debugging
                        ast_log(LOG_NOTICE, "The value of res is: %d\n", res);
                        SQLCHAR sqlState[6], message[SQL_MAX_MESSAGE_LENGTH];
                        SQLINTEGER nativeError;
                        SQLSMALLINT textLength;
                        SQLGetDiagRec(SQL_HANDLE_STMT, stmt, 1, sqlState, &nativeError, message, sizeof(message), &textLength);
                        ast_log(LOG_ERROR, "SQLState: %s, NativeError: %d, Message: %s, Schema: %s, Table: %s\n", sqlState, nativeError, message, schema, table);
                        // found the bug

                        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
                        ast_odbc_release_obj(obj);
                        continue;
                }

Reload module cdr_adaptive_odbc.so, CLI says:

[2024-06-10 14:56:40] ERROR[2651398]: cdr_adaptive_odbc.c:181 load_config: SQLState: HY000, NativeError: 0, Message: [MySQL][ODBC 8.4(a) Driver][mysqld-8.0.36-0ubuntu0.22.04.1]Support for schemas is disabled by NO_SCHEMA option, but non-empty schema is specified., Schema: call_center, Table: cdr

The MySQL ODBC doc state that there is an option called “NO_SCHEMA” that seems to have been turned on by default in this version of odbc connector. https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-functionality-catalog-schema.html

By telling unixODBC to disable this option, everything works. Edit odbc.ini:

[asterisk-mysql]
Description = MySQL connection to 'asterisk' database
Driver = ODBC-MySql
Database = call_center
Server = 10.3.79.100
Port = 3306
NO_SCHEMA = 0
1 Like