How can I enable CDR logging via the cdr_adaptive_odbc.so module in Asterisk 1.8.5?

mysql odbc asterisk cdr
Konrad Kleine picture Konrad Kleine · Sep 2, 2011 · Viewed 11.4k times · Source

I am running

  • an Asterisk 1.8.5 with CEL and CDR via
  • ODBC (connected to MySQL).

The CEL works fine (see below) but the CDR is not working via adaptive ODBC. (Standard ODBC and direct MySQL writing is working for CDR btw.)

The asterisk can find the correct table and columns for CEL. See for yourself:

dev-lt-tk1*CLI> core set verbose 99
Verbosity is at least 99

dev-lt-tk1*CLI> module reload cel_odbc.so 
    -- Reloading module 'cel_odbc.so' (ODBC CEL backend)
  == Parsing '/opt/gemeinschaft/etc/asterisk/cel_odbc.conf':   == Found
    -- Found CEL table cel@odbc-voipstat101.
       > Found id column with type 4 with len 10, octetlen 10, and numlen (0,10)
       > Found eventtype column with type 12 with len 30, octetlen 30, and numlen (0,0)
       > Found eventtime column with type 93 with len 19, octetlen 19, and numlen (0,10)
       > Found userdeftype column with type 12 with len 255, octetlen 255, and numlen (0,0)
       > Found cid_name column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found cid_num column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found cid_ani column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found cid_rdnis column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found cid_dnid column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found exten column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found context column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found channame column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found appname column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found appdata column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found accountcode column with type 12 with len 20, octetlen 20, and numlen (0,0)
       > Found peeraccount column with type 12 with len 20, octetlen 20, and numlen (0,0)
       > Found uniqueid column with type 12 with len 150, octetlen 150, and numlen (0,0)
       > Found linkedid column with type 12 with len 150, octetlen 150, and numlen (0,0)
       > Found amaflags column with type 4 with len 10, octetlen 10, and numlen (0,10)
       > Found userfield column with type 12 with len 255, octetlen 255, and numlen (0,0)
       > Found peer column with type 12 with len 80, octetlen 80, and numlen (0,0)

Here's my table structure for CEL:

mysql> describe cel;
+-------------+--------------+------+-----+-------------------+-----------------------------+
| Field       | Type         | Null | Key | Default           | Extra                       |
+-------------+--------------+------+-----+-------------------+-----------------------------+
| id          | int(30)      | NO   | PRI | NULL              | auto_increment              |
| eventtype   | varchar(30)  | NO   |     | NULL              |                             |
| eventtime   | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| userdeftype | varchar(255) | NO   |     | NULL              |                             |
| cid_name    | varchar(80)  | NO   |     | NULL              |                             |
| cid_num     | varchar(80)  | NO   |     | NULL              |                             |
| cid_ani     | varchar(80)  | NO   |     | NULL              |                             |
| cid_rdnis   | varchar(80)  | NO   |     | NULL              |                             |
| cid_dnid    | varchar(80)  | NO   |     | NULL              |                             |
| exten       | varchar(80)  | NO   |     | NULL              |                             |
| context     | varchar(80)  | NO   |     | NULL              |                             |
| channame    | varchar(80)  | NO   |     | NULL              |                             |
| appname     | varchar(80)  | NO   |     | NULL              |                             |
| appdata     | varchar(80)  | NO   |     | NULL              |                             |
| accountcode | varchar(20)  | NO   |     | NULL              |                             |
| peeraccount | varchar(20)  | NO   |     | NULL              |                             |
| uniqueid    | varchar(150) | NO   |     | NULL              |                             |
| linkedid    | varchar(150) | NO   |     | NULL              |                             |
| amaflags    | int(11)      | NO   |     | NULL              |                             |
| userfield   | varchar(255) | NO   |     | NULL              |                             |
| peer        | varchar(80)  | NO   |     | NULL              |                             |
+-------------+--------------+------+-----+-------------------+-----------------------------+
21 rows in set (0.00 sec)

THE PROBLEM:

Now when I use cdr_adaptive_odbc I get these strange results when loading the module. The column type cannot be matched to an SQL column type.

dev-lt-tk1*CLI> module reload cdr_adaptive_odbc.so 
    -- Reloading module 'cdr_adaptive_odbc.so' (Adaptive ODBC CDR backend)
  == Parsing '/opt/gemeinschaft/etc/asterisk/cdr_adaptive_odbc.conf':   == Found
    -- Found adaptive CDR table ast_cdr@odbc-voipstat101.
       > Found _id column with type 4 with len 10, octetlen 10, and numlen (0,10)
       > Found calldate column with type 93 with len 19, octetlen 19, and numlen (0,10)
       > Found uniqueid column with type -9 with len 32, octetlen 32, and numlen (0,0)
       > Found clid column with type -9 with len 80, octetlen 240, and numlen (0,0)
       > Found src column with type -9 with len 30, octetlen 30, and numlen (0,0)
       > Found dst column with type -9 with len 30, octetlen 30, and numlen (0,0)
       > Found dcontext column with type -9 with len 50, octetlen 50, and numlen (0,0)
       > Found channel column with type -9 with len 60, octetlen 60, and numlen (0,0)
       > Found dstchannel column with type -9 with len 60, octetlen 60, and numlen (0,0)
       > Found lastapp column with type -9 with len 30, octetlen 30, and numlen (0,0)
       > Found lastdata column with type -9 with len 80, octetlen 80, and numlen (0,0)
       > Found duration column with type 4 with len 8, octetlen 8, and numlen (0,10)
       > Found billsec column with type 4 with len 8, octetlen 8, and numlen (0,10)
       > Found disposition column with type -9 with len 15, octetlen 15, and numlen (0,0)
       > Found amaflags column with type -6 with len 3, octetlen 3, and numlen (0,10)
       > Found accountcode column with type -9 with len 25, octetlen 25, and numlen (0,0)
       > Found userfield column with type -9 with len 255, octetlen 255, and numlen (0,0)
       > Found sequence column with type -8 with len 32, octetlen 32, and numlen (0,0)
       > Found linkedid column with type -8 with len 32, octetlen 32, and numlen (0,0)

Here's our CDR table structure:

mysql> describe ast_cdr;
+-------------+-----------------------+------+-----+-------------------+----------------+
| Field       | Type                  | Null | Key | Default           | Extra          |
+-------------+-----------------------+------+-----+-------------------+----------------+
| _id         | int(10) unsigned      | NO   | PRI | NULL              | auto_increment |
| calldate    | timestamp             | NO   | MUL | CURRENT_TIMESTAMP |                |
| uniqueid    | varchar(32)           | NO   | MUL | NULL              |                |
| clid        | varchar(80)           | NO   |     |                   |                |
| src         | varchar(30)           | NO   | MUL |                   |                |
| dst         | varchar(30)           | NO   | MUL |                   |                |
| dcontext    | varchar(50)           | NO   |     |                   |                |
| channel     | varchar(60)           | NO   |     |                   |                |
| dstchannel  | varchar(60)           | NO   |     |                   |                |
| lastapp     | varchar(30)           | NO   |     |                   |                |
| lastdata    | varchar(80)           | NO   |     |                   |                |
| duration    | mediumint(8) unsigned | NO   |     | 0                 |                |
| billsec     | mediumint(8) unsigned | NO   |     | 0                 |                |
| disposition | varchar(15)           | NO   |     |                   |                |
| amaflags    | tinyint(3) unsigned   | NO   |     | 0                 |                |
| accountcode | varchar(25)           | NO   | MUL |                   |                |
| userfield   | varchar(255)          | NO   |     |                   |                |
| sequence    | char(32)              | YES  |     | NULL              |                |
| linkedid    | char(32)              | YES  |     | NULL              |                |
+-------------+-----------------------+------+-----+-------------------+----------------+
19 rows in set (0.00 sec)

Does anybody know why this happens? I've looked into the cel_odbc.c and cdr_adaptive_odbc.c sources and they seem heavily copy pasted.

Answer

Konrad Kleine picture Konrad Kleine · Nov 2, 2011

Now CDR adaptive ODBC is working! I had to remove all the charsets in my SQL table:

This one is the old table:

CREATE TABLE `ast_cdr` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `calldate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uniqueid` varchar(32) NOT NULL,
  `clid` varchar(80) NOT NULL DEFAULT '',
  `src` varchar(30) NOT NULL DEFAULT '',
  `dst` varchar(30) NOT NULL DEFAULT '',
  `dcontext` varchar(50) NOT NULL DEFAULT '',
  `channel` varchar(60) NOT NULL DEFAULT '',
  `dstchannel` varchar(60) NOT NULL DEFAULT '',
  `lastapp` varchar(30) NOT NULL DEFAULT '',
  `lastdata` varchar(80) NOT NULL DEFAULT '',
  `duration` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `billsec` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `disposition` varchar(15) NOT NULL DEFAULT '',
  `amaflags` int(3) unsigned NOT NULL DEFAULT '0',
  `accountcode` varchar(25) NOT NULL DEFAULT '',
  `userfield` varchar(255) NOT NULL DEFAULT '',
  `sequence` char(32) DEFAULT NULL,
  `linkedid` char(32) DEFAULT NULL,
  PRIMARY KEY (`_id`),
  KEY `calldate` (`calldate`),
  KEY `accountcode` (`accountcode`),
  KEY `src_disposition` (`src`(25),`disposition`(4)),
  KEY `dst_disposition` (`dst`(25),`disposition`(4)),
  KEY `uniqueid` (`uniqueid`(25))
) ENGINE=MyISAM AUTO_INCREMENT=2973 DEFAULT CHARSET=latin1

This is the new table:

CREATE TABLE `ast_cdr` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `calldate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uniqueid` varchar(32) NOT NULL,
  `clid` varchar(80)NOT NULL DEFAULT '',
  `src` varchar(30) NOT NULL DEFAULT '',
  `dst` varchar(30) NOT NULL DEFAULT '',
  `dcontext` varchar(50) NOT NULL DEFAULT '',
  `channel` varchar(60) NOT NULL DEFAULT '',
  `dstchannel` varchar(60) NOT NULL DEFAULT '',
  `lastapp` varchar(30) NOT NULL DEFAULT '',
  `lastdata` varchar(80) NOT NULL DEFAULT '',
  `duration` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `billsec` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `disposition` varchar(15) NOT NULL DEFAULT '',
  `amaflags` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `accountcode` varchar(25) NOT NULL DEFAULT '',
  `userfield` varchar(255) NOT NULL DEFAULT '',
  `sequence` char(32) DEFAULT NULL,
  `linkedid` char(32) DEFAULT NULL,
  PRIMARY KEY (`_id`),
  KEY `calldate` (`calldate`),
  KEY `accountcode` (`accountcode`),
  KEY `src_disposition` (`src`(25),`disposition`(4)),
  KEY `dst_disposition` (`dst`(25),`disposition`(4)),
  KEY `uniqueid` (`uniqueid`(25))
) ENGINE=MyISAM AUTO_INCREMENT=2971;

Here are the differences that matter (thanks to wdoekes2 on #asterisk):

kleine@mrburns:~/Desktop$ diff -u original.sql new.sql 
--- original.sql    2011-09-02 13:52:55.931004844 +0200
+++ new.sql 2011-09-02 13:53:03.501015948 +0200
@@ -2,7 +2,7 @@
       `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
       `calldate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
       `uniqueid` varchar(32) NOT NULL,
-      `clid` varchar(80)NOT NULL DEFAULT '',
+      `clid` varchar(80) NOT NULL DEFAULT '',
       `src` varchar(30) NOT NULL DEFAULT '',
       `dst` varchar(30) NOT NULL DEFAULT '',
       `dcontext` varchar(50) NOT NULL DEFAULT '',
@@ -13,7 +13,7 @@
       `duration` mediumint(8) unsigned NOT NULL DEFAULT '0',
       `billsec` mediumint(8) unsigned NOT NULL DEFAULT '0',
       `disposition` varchar(15) NOT NULL DEFAULT '',
-      `amaflags` tinyint(3) unsigned NOT NULL DEFAULT '0',
+      `amaflags` int(3) unsigned NOT NULL DEFAULT '0',
       `accountcode` varchar(25) NOT NULL DEFAULT '',
       `userfield` varchar(255) NOT NULL DEFAULT '',
       `sequence` char(32) DEFAULT NULL,
@@ -24,4 +24,4 @@
       KEY `src_disposition` (`src`(25),`disposition`(4)),
       KEY `dst_disposition` (`dst`(25),`disposition`(4)),
       KEY `uniqueid` (`uniqueid`(25))
-    ) ENGINE=MyISAM AUTO_INCREMENT=2971;
+    ) ENGINE=MyISAM AUTO_INCREMENT=2973 DEFAULT CHARSET=latin1

Now, reloading the CDR adaptive ODBC module works:

dev-lt-tk1*CLI> module reload cdr_adaptive_odbc.so
    -- Reloading module 'cdr_adaptive_odbc.so' (Adaptive ODBC CDR backend)
  == Parsing '/opt/gemeinschaft/etc/asterisk/cdr_adaptive_odbc.conf':   == Found
    -- Found adaptive CDR table ast_cdr@odbc-voipstat101.
       > Found _id column with type 4 with len 10, octetlen 10, and numlen (0,10)
       > Found calldate column with type 93 with len 19, octetlen 19, and numlen (0,10)
       > Found uniqueid column with type 12 with len 32, octetlen 32, and numlen (0,0)
       > Found clid column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found src column with type 12 with len 30, octetlen 30, and numlen (0,0)
       > Found dst column with type 12 with len 30, octetlen 30, and numlen (0,0)
       > Found dcontext column with type 12 with len 50, octetlen 50, and numlen (0,0)
       > Found channel column with type 12 with len 60, octetlen 60, and numlen (0,0)
       > Found dstchannel column with type 12 with len 60, octetlen 60, and numlen (0,0)
       > Found lastapp column with type 12 with len 30, octetlen 30, and numlen (0,0)
       > Found lastdata column with type 12 with len 80, octetlen 80, and numlen (0,0)
       > Found duration column with type 4 with len 8, octetlen 8, and numlen (0,10)
       > Found billsec column with type 4 with len 8, octetlen 8, and numlen (0,10)
       > Found disposition column with type 12 with len 15, octetlen 15, and numlen (0,0)
       > Found amaflags column with type 4 with len 10, octetlen 10, and numlen (0,10)
       > Found accountcode column with type 12 with len 25, octetlen 25, and numlen (0,0)
       > Found userfield column with type 12 with len 255, octetlen 255, and numlen (0,0)
       > Found sequence column with type 1 with len 32, octetlen 32, and numlen (0,0)
       > Found linkedid column with type 1 with len 32, octetlen 32, and numlen (0,0)

Related questions

Asterisk unable to connect to database using odbc connector

I am pretty new to Asterisk and this is my first attempt to connect to database (MySQL). But I keep getting this error [unixODBC][Driver Manager]Data source name not found, and no default driver specified. Here is my setup: /…

Read More
How to search for rows containing a substring?

If I store an HTML TEXTAREA in my ODBC database each time the user submits a form, what's the SELECT statement to retrieve 1) all rows which contain a given sub-string 2) all rows which don't (and is the search case sensitive?) …

Read More
How does OPENQUERY differ for SELECT and INSERT?

I'm aware that the following query will pull down the result set from a linked server: SELECT * FROM openquery(DEVMYSQL, 'SELECT event_id, people_id, role_id, rank, last_updated FROM event_cast') However, is this the same case when …

Read More