我在跑步
- and Asterisk 1.8.5 with CEL and CDR via ODBC >(连接到MySQL)。
CEL工作正常(见下文)，但CDR不能通过自适应ODBC工作。(标准的ODBC和直接的MySQL写入对CDR有效)
星号可以为CEL找到正确的表和列。自己看吧:
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)
这是我的表格结构的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)
问题:
现在当我使用cdr_adaptive_odbc时，我在加载模块时得到这些奇怪的结果。列类型不能与SQL列类型匹配。
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)
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)
有人知道为什么会这样吗?我已经查看了cel_odbc.c和cdr_adaptive_odbc.c源代码，它们似乎大量复制粘贴。
现在CDR自适应ODBC正在工作!我必须删除SQL表中的所有字符集:
这是旧表:
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
这是新表:
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;
以下是重要的差异(感谢wdoekes2上的#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
现在，重新加载CDR自适应ODBC模块工作了:
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)