This is EXPERIMENTAL, use in a test environment, you get no support whatsoever

How to use MySQL database for astdb

Thank you oej for the primary work on appleraisin

Export appleraisin branch from svn:

svn export http://svn.digium.com/svn/asterisk/team/group/edv-appleraisin-trunk/ appleraisin-trunk

Or, if you want to test this on Asterisk 1.8, just export db.c:

svn export http://svn.digium.com/svn/asterisk/team/group/edv-appleraisin-trunk/main/db.c

Overwrite existing db.c and do make, make install

Make sure that you have odbc and odbc-devel installed:

rpm -qa|grep unixODBC

result should be something like:
unixODBC-2.2.11-7.1
unixODBC-devel-2.2.11-7.1

Otherwise install with yum install unixODBC and yum install unixODBC-devel

You also need the devel-package for libtool-ltdl, check with

rpm -q libtool-ltdl-devel

result should be something like
libtool-ltdl-devel-1.5.22-7.el5_4

Compile asterisk as usual, you MUST select res_odbc otherwise it will fail.

Dump your current astdb with this command (NOTE! Only tested on FreePBX 2.9, YMMV)

/var/lib/asterisk/bin/dumpastdb.php myastdb

Edit asterisk.conf, add a systemname like this:

systemname=freepbxrulez

Create a table in asterisk database:

        CREATE TABLE `astdb` (
                `systemname` varchar(40) NOT NULL,
                `family` varchar(256) NOT NULL,
                `keyname` varchar(256) NOT NULL,
                `value` varchar(1050) NOT NULL,
                PRIMARY KEY  (`family`,`keyname`)
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

edit /etc/asterisk/modules.conf add the following at the top before the preload => :

load => res_realtime.so
load => res_odbc.so

Edit /etc/odbcinst.ini and add/edit so it look something like this

# Driver from the MyODBC package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc3.so
Setup           = /usr/lib/libodbcmyS.so
FileUsage       = 1

NOTE!!! Make sure that the lib files exists in /usr/lib and that is has the correct names

Edit /etc/odbc.ini and add/edit so it look something like this

[MySQL-asterisk]
Description     = MySQL ODBC Driver Testing
Driver          = MySQL
Socket          = /var/lib/mysql/mysql.sock
Server          = localhost
User            = asteriskuser
Password        = amp109
Database        = asterisk
Option          = 3

NOTE!!! Change User and Password to what you use, make sure that mysql.sock exists and has the correct name

Edit /etc/asterisk/res_odbc.conf and add/edit so it has a section like this

[test-odbc]
enabled => yes
dsn => MySQL-asterisk
username => asteriskuser
password => amp109

NOTE!!! change username and password to what you use for your MySQL.

edit /etc/asterisk/extconfig.conf, add this at the end:

astdb => odbc,test-odbc,astdb

Note the format for this line:

• astdb = the name of the family to use in Asterisk, astdb to be used
• odbc = the database engine
• test-odbc = the name of the section in res_odbc.conf
• astdb = the table name

Start asterisk with amportal start, if you get errors, check your log.

Now, restore astdb from previous backup:

/var/lib/asterisk/bin/restoreastdb.php myastdb

Now, go to asterisk cli and type

database show

You will now see astdb entries, all read from odbc/mysql

Outstanding Issues

New entries created in Asterisk CLI will report failure but the entry will be created. When I tested ODBC in a live environment Asterisk cored as soon as a SIP REGISTER was sent and Asterisk stored it in astdb
The above core issue is now identified and reported to asterisk developers in issue 17959

Did I mentioned that this is EXPERIMENTAL, use in a test environment, you get no support whatsoever