• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3245
  • Last Modified:

asterisk odbc show command is empty and cdr is not working

I'm trying to get odbc working and connected to MySQL for cdr using asterisk and I get no errors and no odbc cdr logging, but it does log to the csv. Can anyone help with this?

I get this output from odbc show:
*CLI> odbc show

ODBC DSN Settings


Open in new window

and this output from cdr show status:
*CLI> cdr show status

Call Detail Record (CDR) settings
  Logging:                    Enabled
  Mode:                       Simple
  Log unanswered calls:       No

* Registered Backends
    Adaptive ODBC


Open in new window

and this output from echo "select 1" | isql -v asterisk-connector:
[root]# echo "select 1" | isql -v asterisk-connector
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
SQL> +---------------------+
| 1                   |
| 1                   |
SQLRowCount returns 1
1 rows fetched
SQL> [root]#

Open in new window

I have edited these files and restarted asterisk:
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc3.so
Setup           = /usr/lib/libodbcmyS.so
FileUsage       = 1

Open in new window

Description             = MySQL connection to 'asterisk' database
Driver                  = MySQL
Database                = asterisk
Server                  = localhost
User                    = asterisk
Password                = ****
Port                    = 3306
Socket                  = /var/lib/mysql/mysql.sock

Open in new window

preload => res_odbc.so
preload => res_config_odbc.so

Open in new window

enabled => yes
dsn => asterisk-connector
username => asterisk
password => ****
pre-connect => yes
limit => 0
pooling => no

Open in new window


Open in new window

and I have created a mysql database, user, and table as follows:
CREATE USER 'asterisk'@'localhost' IDENTIFIED BY '****';
GRANT ALL PRIVILEGES ON asterisk.* TO 'asterisk'@'localhost';

        calldate datetime NOT NULL default '0000-00-00 00:00:00',
        clid varchar(80) NOT NULL default '',
        src varchar(80) NOT NULL default '',
        dst varchar(80) NOT NULL default '',
        dcontext varchar(80) NOT NULL default '',
        channel varchar(80) NOT NULL default '',
        dstchannel varchar(80) NOT NULL default '',
        lastapp varchar(80) NOT NULL default '',
        lastdata varchar(80) NOT NULL default '',
        duration int(11) NOT NULL default '0',
        billsec int(11) NOT NULL default '0',
        disposition varchar(45) NOT NULL default '',
        amaflags int(11) NOT NULL default '0',
        accountcode varchar(20) NOT NULL default '',
        uniqueid varchar(32) NOT NULL default '',
        userfield varchar(255) NOT NULL default ''

Open in new window

1 Solution
I may be able to help you to fix the ODBC connection - that needs to be working before you have any chance of writing CDR's. It is something I have set up many times for Asterisk, running on CentOS 5.

I find it helpful to use different names for the MySQL database, the MySQL user and the ODBC DSN. For the samples here, I'll use the following:
 MySQL database       asterisk
 MySQL user               astuser
 DSN                           myast

Here is the step by step process I follow to get Asterisk to connect on startup:

1a. Create the database and table in MySQL (as you already have done) and create the user, astuser, with all privileges and with a password - I'll show a password of pw123 as an example:
 grant ALL PRIVILEGES on asterisk.* to astuser@localhost identified by 'pw123';

1b. I create another user with all privileges that I use for testing, as above, but with access from my Windows PC where I run SQLyog to check and view databases. For example:
 grant ALL PRIVILEGES on asterisk.* to winuser@'192.168.1.%' identified by 'pw123';

1c. Now use SQLyog from a PC on 192.168.1.x to check that the database and table are accessible and look like you expected.

2. Make sure the driver files exist in the location identified in /etc/odbc.ini and /etc/odbcinst.ini
For your case, those files are:
You see, on a 64 bit server, they might be in /usr/lib64. Or if you haven't installed the mysql-connector-odbc package then the files might not be anywhere. They might be different versions.

3. Add the DSN definition to the file /etc/odbc.ini as shown in sample attached.

4. Check that the basic ODBC connection works from Linux command line like this:
isql -v myast astuser pw123

You should see this:
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |

At the SQL prompt, type "show tables" and you should see your cdr table in the asterisk database. To exit the isql prompts, type "quit".

5. edit the file /etc/asterisk/res_odbc.conf and edit or add the following:
enabled => yes
dsn => myast
username => astuser
password => pw123
pre-connect => yes

6. Restart Asterisk, go to the CLI and type "odbc show". You should see this:
ODBC DSN Settings

  Name:   myast
  DSN:    myast
  Pooled: No
  Connected: Yes

Driver = /usr/lib/libmyodbc3.so
Description = MyODBC Driver connecting asterisk data
SERVER = localhost
USER = astuser
Password = pw123
Database = asterisk

Open in new window

AFLLCAuthor Commented:
Thank you, after redoing the setup and following your instructions I found that what was stopping mine from working was the limit => 0 setting that the oreilly book had me put in.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now