Asterisk unixODBC configuration files for MySQL and MariaDB

Home » Initial Configuration of Asterisk » Asterisk unixODBC configuration files for MySQL and MariaDB
Initial Configuration of Asterisk No Comments

1.0 Asterisk + unixODBC

Having almost all of our Asterisk configuration based on our preferred Database Management System is one of the greatest advantages that we have at the moment to deploy a VoIP solution. Now, having the possibility of building and integrated and unified communication solution in a non-intrusive way for client’s company, while at the same time assuring scalability and flexibility, that’s a mayor thing. That’s precisely what we have at the moment of using Asterisk+unixODBC.

unixODBC “allows the user or the system administrator to easily configure an application to use any ODBC compliant data source. This is perhaps the single biggest advantage of coding an application to the ODBC API and to purchase these applications. Dyamic binding allows the end-user to pick a data source, ie an SQL Server, and use it for all data applications without having to worry about recompiling the application.”

“The unixODBC Project goals are to develop and promote unixODBC to be the definitive standard for ODBC on non MS Windows platforms. This is to include GUI support for both KDE and GNOME.

ODBC is an open specification for providing application developers with a predictable API with which to access Data Sources. Data Sources include SQL Servers and any Data Source with an ODBC Driver.” (unixODBC website)

In this article I’m making the following assumptions:

  • You are using CentOS
  • You are using MariaDB or MySQL (All MySQL connectors -PHP, Perl, Python, Java, MyODBC, Ruby, MySQL C connector etc- work unchanged with MariaDB)

 

2.0 Asterisk ODBC Installation

Follow this document in order to install Asterisk.

You’ll also need to install the Asterisk ODBC package and (optionally) the Asterisk Voicemail ODBC package (if you plan to store your voicemail using ODBC):

 

 

3.0 Install the MySQL-connector

 

 

4.0 Configuring the System File for unixODBC: odbcinst.ini

The system file odbcinst.ini contains information about ODBC drivers available to all users. You could have multiple drivers configured, so if you want to use MariaDB, PostgreSQL and ORACLE at the same time, here is where you would be configuring the drivers.

Create the following /etc/odbcinst.ini file

[MySQL]
Description=MySQL ODBC 5.1 Driver
Driver=/usr/lib/libmyodbc5.so
SETUP=/usr/lib/myodbc3S.so
UsageCount=1

 
In the previous example, the ‘[MySQL]‘ part, is an identifier for this driver (you could call it whatever you want, just remember the name later). The ‘driver‘ setting indicates where is located the MySQL Connector driver file. If you are using CentOS 64bit (or other distro), this might have a different location, you might want to try finding it using:

 

 
… or you can use whatever method you feel comfortable with (e.g. find command)

The ‘setup‘ and ‘usercount‘ parts can be ignored, as they are used when you create the file using a GUI (which is not our case right now).

  • For extensive documentation about how to correctly configure unixODBC drivers file and Data Source Name file, visit the exceptional documentation provided by Nick Gorham at unixODBC website.
  • Information about configuring MySQL ODBC Connector and underlying details can be found on MySQL ODBC Connector documentation’s page.

 

5.0 Configuring the DSN (Data Source Name) File for unixODBC: odbc.ini

The DSN file contains information about DSN’s available to all users. These “System DSN’s” are useful for application such as web servers that may not be running as a real user and so will not have a home directory to contain a .odbc.ini file.

Create the following /etc/odbc.ini file:

[asterisk-dsn]
Description=Asterisk Data Source Name
Driver=MySQL
SERVER=localhost
UID=astdbuser
PWD=MYSQL_USER_PASSWD
DATABASE=asteriskdb
PORT=3306
; SOCKET=/var/lib/mysql/mysql.sock

 
In this example ‘[asterisk-dsn]‘ is the DSN identifier, the ‘Driver‘ part indicates what driver to use from your odbcinst.ini file (section 4.0 of this guide). ‘Server‘ indicates where to connect using the driver belonging to this section (in this example, ‘localhost’). ‘UID‘ and ‘PWD‘ are the username and password of a user that has proper privileges on the database defined by ‘DATABASE‘. You might want to indicate a ‘SOCKET’, instead of a ‘PORT’ (only for local connections: localhost). This is the socket where MariaDB/MySQL listen to, in my system:

 

 

6.0 Testing your unixODBC configuration

If everything went OK, then this command should help you test your configuration:

span class=”st0″>’select now()’

 

7.0 Avoiding deadly pitfalls

  • Get sure that the database configured in the DSN file exists! (you could get weird errors if it doesn’t, but none of the meaningful)
  • Get user that the username in the DSN file is valid (it exists) and has privileges over the indicated database
  • Get sure to provide the correct file name in the odbcinst.ini file, when configuring the driver

 
 

I hope this post has been useful to you. I’ll try to keep it updated and keeping improving it over the time. If there’s something you don’t understand, just drop me a line.