Wednesday, July 11, 2012

FreeSWITCH with SIP Users in MySQL [Mod XML_CURL]

FreeSWITCH is one of the biggest attraction for me since my introduction to VoIP. Since I like it too much and work with it most of the time but couldn't post anything good related to Freeswitch so far. Luckily I got a link from one of my friend's blog Touchkanalogy about Freeswitch module mod_xml_curl sort-of saying "FreeSWITCH SIP Realtime". Like Asterisk's SIP real-time where SIP users are inserted into database and asterisk authenticates incoming SIP user REGISTRATIONs from database settings.

FreeSWITCH don't exactly work like asterisk. FreeSWITCH needs XML configurations for users' settings. Following my friend's blog and the source document for the freeswitch XML_CURL  module. I was able to successfully insert users in Database and incoming SIP user's authentication requests were processed according to the data in DB.

The Logic is simple. Incoming SIP requests are received by Freeswitch which asks a web-service about the details of the user. The web-server just queries data and if any relevant details are found returns them in XML format. Freeswitch accepts or reject the SIP request depending upon the XML response.

Ref URL: 
Another way to represent the above flowchart.
With SIP Proxy REGISTER Load-Balancer

Following are the commands and steps I performed to make this happen. Remember I had a CentOS server hosting Freeswitch so Ubuntu or other OS users need to find corresponding packages and directories accordingly.

root@FS_HA1#yum install mysql mysql-server php-pdo curl php-xml

Next Edit the php.ini file to include the extension for mysql and pdo.

root@FS_HA1#vim /etc/php.ini

Just below  this Huge tag

; Dynamic Extensions ;
Add these two lines.

Restart the web server to make changes effective.

root@FS_HA1#/etc/init.d/httpd restart
Then goto the freeswitch source directory. I already had FS installed so I installed the module explicitly.

root@FS_HA1#cd /usr/src/freeswitch
root@FS_HA1#make mod_xml_curl && make mod_xml_curl-install
Then enable the module in modules.conf

root@FS_HA1#cd /usr/local/freeswitch/conf/
root@FS_HA1#vim autoload_configs/modules.conf.xml

Uncomment the following tag by removing the  "<!--"and "-->"
<!-- <load module="mod_xml_curl"/> -->

Edit the settingfor the XML_CURL module.

root@FS_HA1#vim autoload_configs/xml_curl.conf.xml

The most importthing is to set the web-service URL where FS will send all the queries.

 <param name="gateway-url" value="http://localhost/fs_curl/" bindings="directory"/>

In my case the web-server is on localhost and I plan on using the sub directory fs_curl for the php pages.

Now its time to download the real thing.
root@FS_HA1#cd /usr/src/
root@FS_HA1#git clone git://

root@FS_HA1#cd freeswitch-contrib/intralanman/PHP/fs_curl/
root@FS_HA1#mkdir /var/www/html/fs_curl
root@FS_HA1#cp -rf * /var/www/html/fs_curl
root@FS_HA1#cd /var/www/html/fs_curl/

Edit the DB connector values and debugging stuff from the global configurations file for FS_CURL
root@FS_HA1#vim global_defines.php

Edit the following lines.

define('DEFAULT_DSN', 'mysql:dbname=FS_DB;host=');define('DEFAULT_DSN_LOGIN', 'freeswitch');
define('DEFAULT_DSN_PASSWORD', 'passw0rd');

If you want to debug uncomment the following (Not recommended for production servers)

define('FS_CURL_DEBUG', 9);define('FS_DEBUG_TYPE', 2); 
define('FS_DEBUG_FILE', '/var/log/fs_curl.debug');

root@FS_HA1#touch /var/log/fs_curl.debug
root@FS_HA1#chown apache:apache /var/log/fs_curl.debug

We need to modify the fs_directory.php file a bit to use the correct table names.
root@FS_HA1#vim fs_directory.php

[ON LINE:88]

FROM:$where_array [] = sprintf ( "domain_id='%s'", $domain ['id'] );
TO:$where_array [] = sprintf ( "domain='%s'", $domain ['id'] );

FROM:$query = sprintf ( "SELECT * FROM directory d %s %s ORDER BY username", $join_clause, $where_clause );
TO:$query = sprintf ( "SELECT * FROM directory %s %s ORDER BY username", $join_clause, $where_clause );

Save and Exit.

root@FS_HA1#cd /var/www/html/fs_curl/sql/
root@FS_HA1#vim +93 mysql-5.0-with-samples.sql
Delete this line all-together else you may get this error:

ERROR 1231 (42000) at line 93: Variable 'character_set_client' can't be set to the value of 'NULL
root@FS_HA1#mysql -uroot -p
mysql> create database FS_DB;
mysql> quit;
root@FS_HA1#mysql FS_DB < mysql-5.0-with-samples.sql
Create New users and passwords for them, domain needs to be added as well.
root@FS_HA1#mysql -uroot -p

mysql>use FS_DB;
mysql> insert into directory_domains values (3,'');
Query OK, 1 row affected (0.00 sec)

mysql>INSERT into directory (username,domain) VALUES ("40277",3);
mysql>INSERT into directory (username,domain) VALUES ("50354",3);

GET THE "id" from the directory table for these newly created users.
mysql> select * from directory;
| id | username | domain          |
|  1 | 1000     |     |
|  2 | 1001     |     |
|  3 | 1002     |     |
|  5 | 1003     |    |
|  6 | 1004     |     |
|  7 | 1005     |     |
|  8 | 1006     |     |
|  9 | 1007     |    |
| 10 | 2000     | default         |
| 11 | 1009     | $${local_ip_v4} |
| 12 | 40277    | 3               |
| 13 | 50354    | 3               |
12 rows in set (0.00 sec)


mysql> insert into directory_params (directory_id,param_name,param_value) VALUES (12,'password','S3cr3t40277');
mysql> insert into directory_params (directory_id,param_name,param_value) VALUES (13,'password','S3cr3t50354');
Thats All, now try registering your SOFT Phone using new username/password and see if your requests reach to WEB-Server.
You can troubleshoot the XML_CURL module by issuing the following command on FS Console.
freeswitch@internal> xml_curl debug_on
For each XML response we'll now get an output on FS_CLI
mod_xml_curl.c:318 XML response is in /tmp/d8fc4b98-cb1a-11e1-b16b-2f327a1d3f98.tmp.xml


  1. Thank you so much for the great tutorial !

  2. Not working for me. Extension's registration on freeswitch is ok, but when I'm trying to call from user 1 to user 2 voicemail is answering. How to call? where to fix? thanks.

    1. I believe that you've some dial-plan issues. Please follow the console logs and see what conditions are matching and which conditions are not matching for you to make a successful call to B-party.

    2. Good day to you,i have the same problem(
      here is freeswitch output
      2014-04-05 16:53:47.077659 [NOTICE] switch_core_session.c:1577 Session 3 (loopback/voicemail-a) Ended
      2014-04-05 16:53:47.077659 [NOTICE] switch_core_session.c:1581 Close Channel loopback/voicemail-a [CS_DESTROY]
      2014-04-05 16:53:47.536978 [NOTICE] switch_core_session.c:1577 Session 4 (loopback/voicemail-b) Ended
      2014-04-05 16:53:47.536978 [NOTICE] switch_core_session.c:1581 Close Channel loopback/voicemail-b [CS_DESTROY]
      2014-04-05 16:53:47.634643 [NOTICE] switch_core_session.c:1577 Session 2 (sofia/internal/1002@ Ended
      2014-04-05 16:53:47.634643 [NOTICE] switch_core_session.c:1581 Close Channel sofia/internal/1002@ [CS_DESTROY]
      2014-04-05 16:53:52.534552 [NOTICE] switch_channel.c:1050 New Channel sofia/internal/1002@ [652282c2-bd04-11e3-951c-1bd706ecac6e]
      2014-04-05 16:53:52.755595 [INFO] mod_dialplan_xml.c:558 Processing 1002 <1002>->1000 in context public
      2014-04-05 16:53:52.755595 [NOTICE] switch_ivr.c:1838 Transfer sofia/internal/1002@ to XML[1000@default]
      2014-04-05 16:53:52.755595 [INFO] mod_dialplan_xml.c:558 Processing 1002 <1002>->1000 in context default
      2014-04-05 16:53:52.755595 [INFO] switch_ivr_async.c:3701 Bound B-Leg: *1 execute_extension::dx XML features
      2014-04-05 16:53:52.775525 [INFO] switch_ivr_async.c:3701 Bound B-Leg: *2 record_session::/usr/local/freeswitch/recordings/1002.2014-04-05-16-53-52.wav
      2014-04-05 16:53:52.775525 [INFO] switch_ivr_async.c:3701 Bound B-Leg: *3 execute_extension::cf XML features
      2014-04-05 16:53:52.775525 [INFO] switch_ivr_async.c:3701 Bound B-Leg: *4 execute_extension::att_xfer XML features
      2014-04-05 16:53:53.094687 [ERR] mod_dptools.c:3977 No dial-string available, please check your user directory.
      2014-04-05 16:53:53.094687 [NOTICE] switch_ivr_originate.c:2670 Cannot create outgoing channel of type [user] cause: [MANDATORY_IE_MISSING]
      2014-04-05 16:53:53.094687 [INFO] mod_dptools.c:3201 Originate Failed. Cause: MANDATORY_IE_MISSING
      2014-04-05 16:53:53.094687 [NOTICE] sofia_glue.c:4360 Pre-Answer sofia/internal/1002@!
      2014-04-05 16:53:53.115695 [NOTICE] mod_dptools.c:1225 Channel [sofia/internal/1002@] has been answered
      2014-04-05 16:53:54.135534 [NOTICE] switch_channel.c:1050 New Channel loopback/app=voicemail:default 1000-a [6614cf0a-bd04-11e3-9542-1bd706ecac6e]
      2014-04-05 16:53:54.135534 [NOTICE] switch_channel.c:1048 Rename Channel loopback/app=voicemail:default 1000-a->loopback/voicemail-a [6614cf0a-bd04-11e3-9542-1bd706ecac6e]
      2014-04-05 16:53:54.135534 [NOTICE] switch_channel.c:1050 New Channel loopback/voicemail-b [661526e4-bd04-11e3-9546-1bd706ecac6e]
      2014-04-05 16:53:54.135534 [NOTICE] mod_loopback.c:945 Pre-Answer loopback/voicemail-a!
      2014-04-05 16:53:54.135534 [NOTICE] mod_dptools.c:1260 Pre-Answer loopback/voicemail-b!

    3. You have to add the dial string parameter in you sip peer.
      add that in directory_params table.

      Bilal Abbasi

  3. I am trying to setup multiple freeswitch servers and have clients register to any server they want to.

    I need incoming calls to be sent to any server also by a load balancer but have access to an extension that may be registered on a different server.

    Any idea how to do this?


    1. Hey J,

      I suggest you discuss this on FreeSWITCH or OpenSIPS or Kamailio mailing list to get more ideas.

      Here is my idea:

      1- Put in a Kamailio or OpenSIPS infront of your FreeSWITCH Servers, Let the SIP proxy handle the registrations.

      2- For any incoming call send call to Load-Balanced array of FreeSWITCH servers who will just do some call routing logic, and if the dialed number matches the extension sequence number route call back to the SIP Proxy.

      3- In SIP Proxy detect the call is coming in from the FreeSWITCH servers, try finding the active registered extension in SIP Proxy and connect the call to that user.

      4- If in SIP proxy the number is offline, send call to some outbound gateway/trunk.

      Let me know if its doable, else I might've to find out time and have the setup blogged.

  4. I may be wrong, but it looks like in the file mysql-5.0-with-samples.sql,
    (36, 1, 'odbc-dsn', 'freeswitch-mysql:freeswitch:Fr33Sw1tch');
    should be changed to:
    (36, 1, 'odbc-dsn', 'FS_DB:freeswitch:Fr33Sw1tch');

  5. mysql>INSERT into directory (username,domain) VALUES ("40277",3);
    should be:
    mysql>INSERT into directory (username,domain_id) VALUES ("40277",3);

    mysql>INSERT into directory (username,domain) VALUES ("50354",3);
    should be:
    mysql>INSERT into directory (username,domain_id) VALUES ("50354",3);

    1. Modify as this works for you :) Thanks for reading.

    2. Och - the reading is a doddle! Thank *you* for writing!!

    3. Hey , did you fix this ? Let me know if any help is required.

  6. my extension are registring but call hangs up when i dial from one extension to the other - calls cant be made!

    1. I hope you might've figured something out. Its clearly a xml_dialplan issue. Kindly let me know if any further help is needed in this regard.

  7. Got through the installation without any problem and I can register users, however when dialing any of the test/ivr/echo numbers I get an engaged tone. Further investigation shows a issue with the dial-plan, specifcally a MySQL call.

    When we manually probe http://localhost/fs_curl/index.php?section=dialplan we get the following error:

    We've specified dial-plan in the bindings along with directory in the url-gateway. We've double checked the DB tables and data and its all there as per your tutorial. We'reusing MySQL 5.1.73. We also get an error when we try and run the offending SQL query in the fs_dialplan.php file

    Any idea whats going on here?

  8. i got this error going to and i find in also in the log curl files

    i did non find the root@FS_HA1#vim +93 mysql-5.0-with-samples.sql i think the file is a little bit changed and the line 93 is practicaly empty :can you help me it does'nt work

  9. Hi, thanks for the excellent tutorial. It works in the sense that calls get connected but there is a problem because calls to *any* number work (including non-existent accounts) but it always reaches the hold music and no other services work.

    I assume that this is a dialplan issue since it never happened before I used mod_xml_curl and fs_curl with FreeSwitch. All services connected as expected when I was using static XML config files.

    Could you give me any pointers? Thanks for any suggestions :-)

  10. I've realised the reason for this, having gone through the process of populating a couple of directory entries which were blank. Likewise, the dialplan entries need populating. Until then, FreeSwitch doesn't know what to do and defaults to the hold music, having no other instructions. Thanks, this has proved really useful and my struggles with the XML have ended up being very educational in how FreeSwitch actually works! :-)

  11. Ok, in case you see this, I have solved the issue that I had. Here is how. The main default.xml and public.xml dialplans were there but not the public/00_inbound_did.xml dialplan which allows you to transfer between profiles. A few rules need to be added to the MySQL DB in order for this to happen. Here is the file:

    This file is at [INSTALL_DIR]/freeswitch/conf/dialplan/public/public/00_inbound_did.xml and you can copy the way the IDs and rules work if you work through the tables systematically.

    The only thing to change is "domain_name=$${domain}" if you are using multiple domains, as this had to be changed to make it work for me.

    Once again, an excellent tutorial - thanks :-)

  12. May need to update git clone git:// to git clone

  13. May need to update git clone git:// to git clone

  14. May also need to change domain_id to domain in /var/www/html/fs_curl/sql/mysql-5.0-with-samples.sql

  15. Very good tutorial. I was able to follow it stepwise.

    May need to update git clone git:// to git clone

  16. I didn't test it but nice tutorial +1 for u :)

  17. Thanks for the tutorial. I'm trying to adapt this to work with FusionPBX (which is based on FreeSWITCH) but uses a PostgreSQL database. Can you offer some assistance in this regard?