Unable to use cdr_adaptive_odbc

Asterisk Asterisk Support
1

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!

2

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

3

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
4

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.