Click to expand Table of Contents
FreeSWITCH can be configured to use ODBC to connect to a remote database instead of using the default SQLite databases.
PostgreSQL is natively supported since FreeSWITCH 1.2.5, see PostgreSQL in the core to learn more
In other words: if you use PostgreSQL and FreeSWITCH >= 1.2.5, you no longer need the guide below.
MySQL is not supported natively, you still need ODBC and this how-to (No native Support of MySQL is planned for the close future)
To use unixODBC you will need to install:
- unixODBC >= 2.3
- An ODBC driver for your chosen database.
Several popular databases are supported including MySQL, PostgreSQL and others.
Before you begin you will need to install and configure your chosen database ensuring you've set the correct permissions for access.
For additional help configuring your database, see:
Note - the following configuration has been tested on Ubuntu 10.04 LTS Server (64-bit) with PostgreSQL 8.4/9.1 and MySQL 5.1.
Install the unixODBC driver
You will need to obtain the ODBC driver for your chosen database. Instructions for obtaining the drivers for MySQL or PostgreSQL are provided below.
Configure odbc.ini and odbcinst.ini
Add the following with the correct information into your odbc.ini file located at /etc/odbc.ini
/etc/odbc.ini for MySQL
If you are running on a 32-bit linux installation, use the following configuration, but change all occurrences of
/usr/lib/ in the examples provided.
/etc/odbcinst.ini for MySQL
If it is available on your system, you must use
Driver = /usr/lib64/odbc/libmyodbc_r.so in your odbcinst.ini.
Also note, (see bug below on bottom of page); If you are using the FreeSWITCH pooled resources, then you definitely need Threading = 0 for high call volume. What this does is it "disables a global mutex in the ODBC core that only allows 1 concurrent operation per process regardless of the concurrent connections." (i.e. It slows down when you have too many connections trying at one time)
As far as has been tested, when making changes to the odbcinst.ini, you do not need to restart FreeSWITCH to effect the changes.
/etc/odbc.ini for PostgreSQL
Important If you are running on a 32-bit Linux installation, use the following configuration, but change all references to /usr/lib64/ in to /usr/lib/ in the examples provided.
/etc/odbcinst.ini for PostgreSQL
Testing your connection
Once you've set up the .ini files and have your ODBC drivers installed, you can test whether your ODBC connection is working correctly by entering:
If a connection is successfully established you should see something similar to:
Compile Freeswitch and Set Modules to Use ODBC
You will need to compile/recompile FreeSWITCH so that ODBC is detected and included within the installation. You will then need to edit the freeswitch configuration to utilize the ODBC connection you just defined.
See Compile FreeSWITCH with ODBC Support below for more information.
PostgreSQL 8.4 on Debian Squeeze
Note: We've experienced some major stability issues with the unixODBC-debian-package 2.2.14p2-1 from the repository. FreeSWITCH would die anywhere between a few days to weeks. Upgrading to a manually compiled unixODBC 2.3.0-pre fixed this problem and has been tested stable for the last 2 months. --[[User:Peletiah|Peletiah]] 13:04, 4 February 2012 (UTC)''
Install PostgreSQL and ODBC
You also have to install '''unixodbc-dev''', which by default depends on libodbcinstq1c2 which would install x11-common and other packages for GUI-Support. To avoid this, you have to create a fake libodbcinstq1c2-package, see instructions below. If you don't care, do ''apt-get install unixodbc-dev'' and continue with odbc-configuration.
Fake libodbcinstq1c2-package with equivs
If you don't want to install X11 on a headless server, create a fake-libodbcinstq1c2-package with equivs. Please read the documentation at http://www.debian.org/doc/manuals/apt-howto/ch-helpers.en.html to make sure you don't damage your system.
create a control-file for the fake-package:
edit the control-file so it looks like this:
build the fake-package:
Create /etc/odbcinst.ini by command
Create /etc/odbc.ini Like this
In order to not have postgres log everything it does change CommLog to 0
Test the connection with
Install unixODBC and the MySQL ODBC Connector
odbcinst -j. This will show you list of config files.
- Uncomment the MySQL sample driver confirguration in
- Add the following with the correct information into your odbc.ini file located at
Note: If you are connecting your freeswitch server to a remote MySQL database, you can take out the last line from the above setting. Take off the "Socket = /var/lib/mysql/mysql.sock" line.
Note: OPTION allows you to set client specific FLAGS, in the example 67108864 (FLAG_MULTI_STATEMENTS) is set - See [http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html] for all flags. The number represents the addition of all flag numbers that you want enabled.
Note: On CentOS it is OPTION (without S), instead of OPTIONS.
For some tips on setting up your dsn up in unixODBC see [[Mod_spidermonkey_odbc#unixodbcc]]
- Test your ODBC setup by running the utility
isql maxpowersoft_odbc myUser myPass
Install the following ports and follow instructions as detailed within the CentOS section
Go to [[Mod_spidermonkey_odbc#General_Configuration]] for instructions to configure unixODBC
Create the FreeSWITCH Database
- Create a database in mysql or pgsql (Note: Do NOT use utf8_bin collation! Especially important with IP failover configurations)
- Create a user
- Do not worry about creating the actual tables. FreeSWITCH will take care of this part itself if they do not exist.
MySQL Client 4.x and MySQL Server 5.x
If you're getting an error about authentication method not being supported, use old password format in db.
Compile FreeSWITCH with ODBC support
UnixODBC support will be autodetected by ./configure, and if found, will be compiled into FreeSWITCH.
ODBC DSN Format
See ODBC DSN
ODBC not available
"mod_sofia: ODBC IS NOT AVAILABLE!" means
"FreeSWITCH is compiled without ODBC support."
"[unixODBC][Driver Manager] Data source name not found, and no default driver specified"
a) Double check that the name of the [dsn] is exactly the same in your config files
- OR -
b) Try linking your ODBC config files to your FreeSWITCH install directory.
Idle in transaction
There's a known issue with PostgreSQL and ODBC where processes hang due to locking
When this happens (calls appear "hanging", FS-console doesn't respond), "ps ax" will show PostgreSQL processes in limbo:
root@localhost:~# ps ax|grep postgres|grep free
15753 ? Ss 0:00 postgres: freeswitch_db freeswitch_db 127.0.0.1(43846) idle in transaction
15759 ? Ss 0:00 postgres: freeswitch_db freeswitch_db 127.0.0.1(43847) idle
15760 ? Ss 0:00 postgres: freeswitch_db freeswitch_db 127.0.0.1(43848) UPDATE waiting
A known workaround is to add the following parameters to ''odbcinst.ini'' in the respective driver-sections(ANSI, Unicode - see odbc-configuration above for a full example):
See Bug #3693 for additional information