Asterisk unable to connect to database using odbc connector

Question

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:

/etc/odbcinst.int

[Default]
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so

[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
FileUsage       = 1
;UsageCount = 2

/etc/odbc.ini

[MYSQL-DNS]
Driver =MySQL
Description = MySQL connection to ‘My Voice’ database
Trace = Yes
TraceFile = /tmp/odbc.log
Server =localhost
Port =3306
Database =my_database
UserName =myuser
Password =mypass
Option = 3
Socket =/var/run/mysqld/mysqld.sock

/var/lib/asterisk/.profile

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc

I have also copied both odbcinst.ini and odbc.ini to ~/.odbcinst.ini and ~/.odbc.ini respectively and to /var/lib/asterisk/ just to clear all doubt.

res_odbc.conf

[ENV]

[my_database]
enabled => yes
dsn =>MYSQL-DNS
username => myuser
password => mypass
pre-connect => yes
sanitysql => select 1
;idlecheck => 3600
share_connections => yes
pooling => no
limit => 1
;isolation=repeatable_read

Command odbcinst -j outputs

$ odbcinst -j 
unixODBC 2.3.2
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

I think I did a symbolic link to /usr/lib sometime back thus locate libmyodbc.so outputs

$ locate libmyodbc.so
/usr/lib/libmyodbc.so
/usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so

I also added the x permission ie (changed from 644 to 755):

ls -alsh /usr/lib/x86_64-linux-gnu/odbc/

shows 755 for *.so

The Log Outputs

res_odbc.c: Connecting my_database
[Aug  1 09:32:40] WARNING[27846] res_odbc.c: res_odbc: Error SQLConnect=-1 errno=0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified
[Aug  1 09:32:40] WARNING[27846] res_odbc.c: Failed to connect to my_database
[Aug  1 09:32:40] ERROR[27846] res_config_odbc.c: No database handle available with the name of 'my_database' (check res_odbc.conf)

Answer 1

You can check connection as

isql -vvv MYSQL-DNS

That will clearly state you what is wrong.

Answer 2

I run across the same problem recently, and solved it by adding to my res_odbc.conf:

[ENV]
ODBCSYSINI => /etc
ODBCINI    => /etc/odbc.ini 

It seems that problem was that Asterisk was not loading .profile properly.

Answer 3

I got this solved using

Install system DSN 
$ odbcinst -i -s -l -f /etc/odbc.ini 

Test your DSN install by lisinting your installed data sources. 
$ odbcinst -s -q