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 1.8.3.2 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
-----------------

phreaknasty*CLI>

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
  -------------------
    cdr-custom
    csv
    cdr_sqlite3_custom
    Adaptive ODBC

phreaknasty*CLI>

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:
/etc/odbcinst.ini
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc3.so
Setup           = /usr/lib/libodbcmyS.so
FileUsage       = 1

Open in new window


/etc/odbc.ini
[asterisk-connector]
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


/etc/asterisk/modules.conf
preload => res_odbc.so
preload => res_config_odbc.so

Open in new window


/etc/asterisk/res_odbc.conf
[asterisk]
enabled => yes
dsn => asterisk-connector
username => asterisk
password => ****
pre-connect => yes
limit => 0
pooling => no

Open in new window


/etc/asterisk/cdr_adaptive_odbc.conf
[first]
connection=asterisk
table=cdr

Open in new window


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

CREATE TABLE cdr (
        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

AFLLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

feptiasChief DudeCommented:
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:
  /usr/lib/libmyodbc3.so
  /usr/lib/libodbcmyS.so
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 |
| |
+------------------------------+
SQL>

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:
[myast]
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

[myast]
Driver = /usr/lib/libmyodbc3.so
Description = MyODBC Driver connecting asterisk data
SERVER = localhost
PORT =
USER = astuser
Password = pw123
Database = asterisk
OPTION = 3
SOCKET =

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IP Telephony

From novice to tech pro — start learning today.