[asterisk-users] CDR in an MySQL-Database

[John Novack]

Luca Bertoncello wrote:
> Hi list!
>
> I'd like to save all information about calls (CDR) in a MySQL-Database.
> I created the DB and a user for Asterisk on a separate server, then I
> configured my cdr_mysql.conf so:
>
> [global]
> hostname=192.168.10.3
> dbname=asterisk
> table=cdr
> password=MYSECRET
> user=asterisk
> port=3306
>
> and my cdr.conf so:
>
> [general]
> enable=yes
> unanswered = yes
> safeshutdown=yes
>
> [mysql]
> usegmtime=no
> loguniqueid=yes
> loguserfield=yes
> accountlogs=yes
>
> I created the table in the DB so:
>
> CREATE TABLE IF NOT EXISTS `cdr` (
>    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
>    `calldate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
>    `clid` varchar(80) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `src` varchar(80) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `dst` varchar(80) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `dcontext` varchar(80) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `lastapp` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `lastdata` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `duration` float unsigned DEFAULT NULL,
>    `billsec` float unsigned DEFAULT NULL,
>    `disposition` enum('ANSWERED','BUSY','FAILED','NO ANSWER','CONGESTION')
> COLLATE utf8_bin DEFAULT NULL, `channel` varchar(50) COLLATE utf8_bin DEFAULT
> NULL, `dstchannel` varchar(50) COLLATE utf8_bin DEFAULT NULL,
>    `amaflags` varchar(50) COLLATE utf8_bin DEFAULT NULL,
>    `accountcode` varchar(20) COLLATE utf8_bin DEFAULT NULL,
>    `uniqueid` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `userfield` float unsigned DEFAULT NULL,
>    `answer` datetime NOT NULL,
>    `end` datetime NOT NULL,
>    PRIMARY KEY (`id`),
>    KEY `calldate` (`calldate`),
>    KEY `dst` (`dst`),
>    KEY `src` (`src`),
>    KEY `dcontext` (`dcontext`),
>    KEY `clid` (`clid`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
>
> Then I restarted Asterisk (core restart now).
> Unfortunately it does not work, since I get on boot:
>
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1468 load_mysql_config: MySQL RealTime: No database user found, using 'asterisk' as default.
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1474 load_mysql_config: MySQL RealTime: No database password found, using 'asterisk' as default.
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1480 load_mysql_config: MySQL RealTime: No database host found, using localhost via socket.
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1486 load_mysql_config: MySQL RealTime: No database name found, using 'asterisk' as default.
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1492 load_mysql_config: MySQL RealTime: No database port found, using 3306 as default.
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1509 load_mysql_config: MySQL RealTime: No database socket found (and unable to detect a suitable path).
>
> And of course:
>
> OpenWrt*CLI> cdr show status
>
> Call Detail Record (CDR) settings
> ----------------------------------
>    Logging:                    Enabled
>    Mode:                       Simple
>    Log unanswered calls:       Yes
>
> * Registered Backends
>    -------------------
>      cdr-custom
>
> Asterisk 1.8 runs on an OpenWRT-Switch.
> Any idea?
>
> Thanks
> Luca Bertoncello
> (lucabert at lucabert.de)
>
Been doing this with MySql for the last 10 years, though not on an openWrt machine
MySql is on the Asterisk machine.
Also have additional database tables to block by callerId and name
Did have some issues with the dialplan syntax when moving from 1.4 to 11, but it just works
I assume OpenWRT is a pre compiled Asterisk package?
You may not have the proper configuration to use MySql
Your error message(s) seem to say it expects to find the MySql server on "localhost" but you say it is on a different machine!!
perhaps you need to fix that first?


John Novack




-- 

Dog is my Co-pilot