Asterisk RealTime Sip

Business SIP Providers
Provider Plan Details Monthly Rate *
Vonage Business SIP Trunking
  • One provider & nationwide coverage
  • Easily integrated into your existing infrastructure
  • More uptime, flexibility and disaster recovery options
$25.00
Details
8x8 8x8 IP Trunking
  • Unlimited calls to US and Canada
  • Softphone and mobile app available
  • 2012 Market Leader Award
$29.99
Details
Business PBX Solutions
Provider Solution Details
Bicom VoIP Become an ITSP Now!
  • Become a serious competitor in VoIP Immediately
  • FULL Consultancy, Installation, Training & Support
  • Sell Hosted IP PBXs, Biz Lines, Call Centre
  • Turnkey Provisioning at your data center
Details
3CX Software PBX for Windows
  • Windows Software Solution
  • Easy to Install and Manage
  • Auto Configures Phones & Trunks
  • Android, iOS, Windows & Mac clients
Details

Asterisk RealTime SIP


sip.conf Setup

You can keep any sip users in the flatfile AND use RealTime. How cool is that?

Extconfig.conf Setup with Asterisk 1.6.1.1

Add the following line, swapping your own personal values if you wish:


sipusers => mysql,general,sip_buddies
sippeers => mysql,general,sip_buddies
extensions => mysql,general,extensions_table


Database Config

put the following in res_mysql.conf

[general]
dbhost = 127.0.0.1
dbname = asterisk
dbuser = myuser
dbpass = mypass
dbport = 3306


Values in sip.conf or iax.conf like in older versions of * are no longer used.


Database Table

Lets create the table we need:

NOTE: You can use any table name you wish, just make sure the table name matches what you have the family name bound to.

NOTE: General principles: the column names in your database table correspond to the option names in sip.conf. You do not have to have all option names defined in your table; you only have to define those columns you actually use in sip.conf. Exceptions to this are 'regserver' and 'regseconds', which the channel driver's realtime routines use for internal book-keeping. The 'name' field must also be present to hold equivalent of the [category name] in the sip.conf file. It is easily possible that different versions of Asterisk will require different tables. For instance, a 1.6 version of Asterisk would not use the "cancallforward", or 'restrictcid', or 'mask', or 'qualify', or "musiconhold" columns, but might require 'mohinterpret', 'mohsuggest', etc. etc. options instead. Options (column names) that not offered in sip.conf are ignored. Some options in sip.conf are OK to have multiple entries, but in a real-time database, Only one column is available. In these cases, you can add multiple values separated by semicolons. This occurs with setvar, allow/disallow, and permit/deny.

NOTE: Column order is important!! If you place "ipaddr" before "host" (in the case of dynamic), you will never load the public IP address of your sip device, as it will be overwritten when "host" is encountered. allow/disallow and permit/deny, the order of these statements is crucial in the config file, as they are applied in order. In the realtime db, the order is determined by the order of the columns in the table. You will note that the deny/disallow entries come before the allow/permit entries, to support the common usage of 'deny all', then permit '192.168.....'.



#
# Table structure for table `bit_sip_buddies`
#

CREATE TABLE `bit_sip_buddies` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(80) NOT NULL default '',
`host` varchar(31) NOT NULL default '',
`nat` varchar(5) NOT NULL default 'no',
`type` enum('user','peer','friend') NOT NULL default 'friend',
`accountcode` varchar(20) default NULL,
`amaflags` varchar(13) default NULL,
`call-limit` smallint(5) unsigned default NULL,
`callgroup` varchar(10) default NULL,
`callerid` varchar(80) default NULL,
`cancallforward` char(3) default 'yes',
`canreinvite` char(3) default 'yes',
`context` varchar(80) default NULL,
`defaultip` varchar(15) default NULL,
`dtmfmode` varchar(7) default NULL,
`fromuser` varchar(80) default NULL,
`fromdomain` varchar(80) default NULL,
`insecure` varchar(4) default NULL,
`language` char(2) default NULL,
`mailbox` varchar(50) default NULL,
`md5secret` varchar(80) default NULL,
`deny` varchar(95) default NULL,
`permit` varchar(95) default NULL,
`mask` varchar(95) default NULL,
`musiconhold` varchar(100) default NULL,
`pickupgroup` varchar(10) default NULL,
`qualify` char(3) default NULL,
`regexten` varchar(80) default NULL,
`restrictcid` char(3) default NULL,
`rtptimeout` char(3) default NULL,
`rtpholdtimeout` char(3) default NULL,
`secret` varchar(80) default NULL,
`setvar` varchar(100) default NULL,
`disallow` varchar(100) default 'all',
`allow` varchar(100) default 'g729;ilbc;gsm;ulaw;alaw',
`fullcontact` varchar(80) NOT NULL default '',
`ipaddr` varchar(15) NOT NULL default '',
`port` smallint(5) unsigned NOT NULL default '0',
`regserver` varchar(100) default NULL,
`regseconds` int(11) NOT NULL default '0',
`lastms` int(11) NOT NULL default '0',
`username` varchar(80) NOT NULL default '',
`defaultuser` varchar(80) NOT NULL default '',
`subscribecontext` varchar(80) default NULL,
`useragent` varchar(20) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `name_2` (`name`)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;


July/15,2010 Sherwood McGowan - Sherwood McGowan Consulting
Here's a table definition that should not only cover all 1.6.x config items, but it should also be a little "cleaner" since it uses enums for options that have only couple or so possible values.
Nov/4,2010 Nick Barnes - Vitell
Updated to work with latest 1.6.x and moved 'deny' above 'permit' otherwise nothing will work! Sherwood: Thanks Nick for catching that!

# Asterisk 1.6.x structure for sip 'device' table
CREATE TABLE `bit_sip_devices` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(80) NOT NULL DEFAULT '',
`context` varchar(80) DEFAULT NULL,
`callingpres` enum('allowed_not_screened','allowed_passed_screen','allowed_failed_screen','allowed','prohib_not_screened','prohib_passed_screen','prohib_failed_screen','prohib','unavailable') DEFAULT 'allowed_not_screened',
`deny` varchar(95) DEFAULT NULL,
`permit` varchar(95) DEFAULT NULL,
`secret` varchar(80) DEFAULT NULL,
`md5secret` varchar(80) DEFAULT NULL,
`remotesecret` varchar(250) DEFAULT NULL,
`transport` enum('tcp','udp','tcp,udp') DEFAULT NULL,
`host` varchar(31) NOT NULL DEFAULT '',
`nat` varchar(5) NOT NULL DEFAULT 'no',
`type` enum('user','peer','friend') NOT NULL DEFAULT 'friend',
`accountcode` varchar(20) DEFAULT NULL,
`amaflags` varchar(13) DEFAULT NULL,
`callgroup` varchar(10) DEFAULT NULL,
`callerid` varchar(80) DEFAULT NULL,
`defaultip` varchar(15) DEFAULT NULL,
`dtmfmode` varchar(7) DEFAULT NULL,
`fromuser` varchar(80) DEFAULT NULL,
`fromdomain` varchar(80) DEFAULT NULL,
`insecure` varchar(4) DEFAULT NULL,
`language` char(2) DEFAULT NULL,
`mailbox` varchar(50) DEFAULT NULL,
`pickupgroup` varchar(10) DEFAULT NULL,
`qualify` char(3) DEFAULT NULL,
`regexten` varchar(80) DEFAULT NULL,
`rtptimeout` char(3) DEFAULT NULL,
`rtpholdtimeout` char(3) DEFAULT NULL,
`setvar` varchar(100) DEFAULT NULL,
`disallow` varchar(100) DEFAULT 'all',
`allow` varchar(100) DEFAULT 'g729;ilbc;gsm;ulaw;alaw',
`fullcontact` varchar(80) NOT NULL DEFAULT '',
`ipaddr` varchar(15) NOT NULL DEFAULT '',
`port` mediumint(5) unsigned NOT NULL DEFAULT '0',
`username` varchar(80) NOT NULL DEFAULT '',
`defaultuser` varchar(80) NOT NULL DEFAULT '',
`subscribecontext` varchar(80) DEFAULT NULL,
`directmedia` enum('yes','no') DEFAULT NULL,
`trustrpid` enum('yes','no') DEFAULT NULL,
`sendrpid` enum('yes','no') DEFAULT NULL,
`progressinband` enum('never','yes','no') DEFAULT NULL,
`promiscredir` enum('yes','no') DEFAULT NULL,
`useclientcode` enum('yes','no') DEFAULT NULL,
`callcounter` enum('yes','no') DEFAULT NULL,
`busylevel` int(10) unsigned DEFAULT NULL,
`allowoverlap` enum('yes','no') DEFAULT 'yes',
`allowsubscribe` enum('yes','no') DEFAULT 'yes',
`allowtransfer` enum('yes','no') DEFAULT 'yes',
`ignoresdpversion` enum('yes','no') DEFAULT 'no',
`videosupport` enum('yes','no','always') DEFAULT 'no',
`maxcallbitrate` int(10) unsigned DEFAULT NULL,
`rfc2833compensate` enum('yes','no') DEFAULT 'yes',
`session-timers` enum('originate','accept','refuse') DEFAULT 'accept',
`session-expires` int(5) unsigned DEFAULT '1800',
`session-minse` int(5) unsigned DEFAULT '90',
`session-refresher` enum('uac','uas') DEFAULT 'uas',
`t38pt_usertpsource` enum('yes','no') DEFAULT NULL,
`outboundproxy` varchar(250) DEFAULT NULL,
`callbackextension` varchar(250) DEFAULT NULL,
`registertrying` enum('yes','no') DEFAULT 'yes',
`timert1` int(5) unsigned DEFAULT '500',
`timerb` int(8) unsigned DEFAULT NULL,
`qualifyfreq` int(5) unsigned DEFAULT '120',
`contactpermit` varchar(250) DEFAULT NULL,
`contactdeny` varchar(250) DEFAULT NULL,
`lastms` int(11) NOT NULL,
`regserver` varchar(100) NOT NULL DEFAULT '',
`regseconds` int(11) NOT NULL DEFAULT '0',
`useragent` varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `name_2` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

Apr 27/10 Serge Berney - iXo SA
Since 1.6.1.18 : Added 'useragent'
To prevent the warning message : "Table sip_buddies requires a column 'useragent' of size '20', but no such column exists."

If you have error message like :

   WARNING[27599] acl.c: Invalid IP address in 
   ERROR[27599] chan_sip.c: Bad ACL entry in configuration line 0 : 


Be sure that fields 'deny', 'permit', 'mask' contain valid IP or are set to "(NULL)" value (if no ACL control is needed)

If you have error message like :

   WARNING[27221] acl.c: Unable to lookup ''
   NOTICE[27221] chan_sip.c: Registration from '<sip:xxx@a.b.c.d:zzz>' failed for 'a.b.c.d' - No matching peer found


be sure that field 'defaultip' is not null (set value to '0.0.0.0' if you don't need it).


Apr 07/09 Akan Nkweini
Added 'lastms'.
Without this I had the following error
res_config_mysql.c:376 update_mysql: MySQL RealTime: Failed to query database. Check debug for more info.

Dec 03/08 Artem
Asterisk 1.4.21.2 (on Debian) does not work if qualify is set and rtcachefriends=yes (which is default).
qualify must be NULL or should not be in the table at all. Otherwise this error message will flood your log file/console:

handle_response_peerpoke: Peer 'xyz' is now Reachable.

Sept 18/08 caspar
It seems like Asterisk 1.6.0 RealTime SIP does not work without the following fields:
  • defaultip
  • defaultuser
  • regserver
  • regseconds
  • mask
  • fromuser
  • fromdomain

May 14/08 arf_
Added 'subscribecontext' to the above as it's may be needed in some case (and it works a least with asterisk 1.4.18)

Apr 15/08 bcnit
Added 'defaultuser' to the above as it's needed in Asterisk 1.6.

Jul 9/07 bcnit
Note that 'regserver' has been added to the above this doesn't appear to be used by anything other than the initial call RealTime makes to clear the record, but including it gets rid of a WARNING on the console!

Jun 8/07 bcnit
If you are going to access the table above using MS Access and MyODBC, define port as:

`port` mediumint(8) unsigned NOT NULL default '0'

Or you'll have all sorts of problems trying to update rows!

Jan 4/06 mhaynes
I ran into problems using the Message Waiting Indicator with my Cisco 7960g phone using the realtime system under Asterisk 1.2.0. Turns out you need to set the rtcachefriends=yes in the general context of your flat file sip.conf. Once this is done, MWI starts working as usual.

Nov 9/05 hfwang
Note: It seems that since asterisk 1.0.2RC1 there is 1 additional field for SIP accounts called 'fullcontact'. Added this field in the table above.

Updated by: DHuang

(3/16/05) Updated by: utdrmac - incominglimit and outgoinglimit are deprecated. Use Asterisk cmd SetGroup instead.

Jan 2/06 misak
Added setvar column


NOTE: The index created on the column 'name' is because RealTime does its SELECT query using that column everytime. That column must also be unique.

You do not need every column listed above. If you wish, you can remove those columns you know you will never use. The columns in your tables should line up with the fields you would specify in the given entity declaration. If an entry would appear more than once, in the column it should be separated by a semicolon. For example, an entity that looks like:

[foo]
host=dynamic
secret=bar
context=default
allow=gsm
allow=ulaw



could be stored in a table like this:


namehostsecretcontextipaddrportallow
foodynamicbardefault127.0.0.14569gsm;ulaw




You do not need to insert the ipaddr, port or regseconds information. These columns will be updated periodicaly by RealTime.



Testing

Throw some data into the above table and try to register an extension. The /var/log/asterisk/debug should give info on any problems.



Realtime Caching...

As of CVS-HEAD 3/16/05, if you enable RealTime caching in your sip.conf, Voicemail MWI works and so does 'sip show peers'. To do so, add "rtcachefriends=yes" to the general section of your sip.conf file.

As the name implies, this caches the "RealTime" information from the database. As a result, there is a delay in updating some (if not all) fields in the SIP entry when you update the database. For instance, if you create an entry with a context = "context1" and Asterisk loads it from the database (perhaps the phone registered or tried to make a call), Asterisk holds on to that information as far as I can tell, indefinitely until a sip reload occurs.

This also means that you will have to do a sip reload to clear out any entries. Removing them from the database does not seem to work. You can still add new entries though without reloading Asterisk.

RealTime caching...isn't that an oxymoron anyways? :-) — (Someone check to see if this affects IAX too, I don't have any IAX phones at the moment. - Flobi) — (RealTime caching does affect IAX as well, works the same way. - Josh)

Update : use "sip prune realtime PEERNAME" then "sip show peer PEERNAME load" to flush the peer and reload from db - (Voicemeup)



Realtime SIP and templates

Question: In the sip.conf we have the ability to leverage templates to simplify the configuration file. Is this template mechanism available via SIP realtime?

Answer: No. "The templates are part of the configuration file (text files) parser and not supported in databases."

Reference: http://comments.gmane.org/gmane.comp.telephony.pbx.asterisk.user/276114
(Added 14 Sept 2013 bluecrow76)



See Also

Asterisk RealTime SIP


sip.conf Setup

You can keep any sip users in the flatfile AND use RealTime. How cool is that?

Extconfig.conf Setup with Asterisk 1.6.1.1

Add the following line, swapping your own personal values if you wish:


sipusers => mysql,general,sip_buddies
sippeers => mysql,general,sip_buddies
extensions => mysql,general,extensions_table


Database Config

put the following in res_mysql.conf

[general]
dbhost = 127.0.0.1
dbname = asterisk
dbuser = myuser
dbpass = mypass
dbport = 3306


Values in sip.conf or iax.conf like in older versions of * are no longer used.


Database Table

Lets create the table we need:

NOTE: You can use any table name you wish, just make sure the table name matches what you have the family name bound to.

NOTE: General principles: the column names in your database table correspond to the option names in sip.conf. You do not have to have all option names defined in your table; you only have to define those columns you actually use in sip.conf. Exceptions to this are 'regserver' and 'regseconds', which the channel driver's realtime routines use for internal book-keeping. The 'name' field must also be present to hold equivalent of the [category name] in the sip.conf file. It is easily possible that different versions of Asterisk will require different tables. For instance, a 1.6 version of Asterisk would not use the "cancallforward", or 'restrictcid', or 'mask', or 'qualify', or "musiconhold" columns, but might require 'mohinterpret', 'mohsuggest', etc. etc. options instead. Options (column names) that not offered in sip.conf are ignored. Some options in sip.conf are OK to have multiple entries, but in a real-time database, Only one column is available. In these cases, you can add multiple values separated by semicolons. This occurs with setvar, allow/disallow, and permit/deny.

NOTE: Column order is important!! If you place "ipaddr" before "host" (in the case of dynamic), you will never load the public IP address of your sip device, as it will be overwritten when "host" is encountered. allow/disallow and permit/deny, the order of these statements is crucial in the config file, as they are applied in order. In the realtime db, the order is determined by the order of the columns in the table. You will note that the deny/disallow entries come before the allow/permit entries, to support the common usage of 'deny all', then permit '192.168.....'.



#
# Table structure for table `bit_sip_buddies`
#

CREATE TABLE `bit_sip_buddies` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(80) NOT NULL default '',
`host` varchar(31) NOT NULL default '',
`nat` varchar(5) NOT NULL default 'no',
`type` enum('user','peer','friend') NOT NULL default 'friend',
`accountcode` varchar(20) default NULL,
`amaflags` varchar(13) default NULL,
`call-limit` smallint(5) unsigned default NULL,
`callgroup` varchar(10) default NULL,
`callerid` varchar(80) default NULL,
`cancallforward` char(3) default 'yes',
`canreinvite` char(3) default 'yes',
`context` varchar(80) default NULL,
`defaultip` varchar(15) default NULL,
`dtmfmode` varchar(7) default NULL,
`fromuser` varchar(80) default NULL,
`fromdomain` varchar(80) default NULL,
`insecure` varchar(4) default NULL,
`language` char(2) default NULL,
`mailbox` varchar(50) default NULL,
`md5secret` varchar(80) default NULL,
`deny` varchar(95) default NULL,
`permit` varchar(95) default NULL,
`mask` varchar(95) default NULL,
`musiconhold` varchar(100) default NULL,
`pickupgroup` varchar(10) default NULL,
`qualify` char(3) default NULL,
`regexten` varchar(80) default NULL,
`restrictcid` char(3) default NULL,
`rtptimeout` char(3) default NULL,
`rtpholdtimeout` char(3) default NULL,
`secret` varchar(80) default NULL,
`setvar` varchar(100) default NULL,
`disallow` varchar(100) default 'all',
`allow` varchar(100) default 'g729;ilbc;gsm;ulaw;alaw',
`fullcontact` varchar(80) NOT NULL default '',
`ipaddr` varchar(15) NOT NULL default '',
`port` smallint(5) unsigned NOT NULL default '0',
`regserver` varchar(100) default NULL,
`regseconds` int(11) NOT NULL default '0',
`lastms` int(11) NOT NULL default '0',
`username` varchar(80) NOT NULL default '',
`defaultuser` varchar(80) NOT NULL default '',
`subscribecontext` varchar(80) default NULL,
`useragent` varchar(20) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `name_2` (`name`)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;


July/15,2010 Sherwood McGowan - Sherwood McGowan Consulting
Here's a table definition that should not only cover all 1.6.x config items, but it should also be a little "cleaner" since it uses enums for options that have only couple or so possible values.
Nov/4,2010 Nick Barnes - Vitell
Updated to work with latest 1.6.x and moved 'deny' above 'permit' otherwise nothing will work! Sherwood: Thanks Nick for catching that!

# Asterisk 1.6.x structure for sip 'device' table
CREATE TABLE `bit_sip_devices` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(80) NOT NULL DEFAULT '',
`context` varchar(80) DEFAULT NULL,
`callingpres` enum('allowed_not_screened','allowed_passed_screen','allowed_failed_screen','allowed','prohib_not_screened','prohib_passed_screen','prohib_failed_screen','prohib','unavailable') DEFAULT 'allowed_not_screened',
`deny` varchar(95) DEFAULT NULL,
`permit` varchar(95) DEFAULT NULL,
`secret` varchar(80) DEFAULT NULL,
`md5secret` varchar(80) DEFAULT NULL,
`remotesecret` varchar(250) DEFAULT NULL,
`transport` enum('tcp','udp','tcp,udp') DEFAULT NULL,
`host` varchar(31) NOT NULL DEFAULT '',
`nat` varchar(5) NOT NULL DEFAULT 'no',
`type` enum('user','peer','friend') NOT NULL DEFAULT 'friend',
`accountcode` varchar(20) DEFAULT NULL,
`amaflags` varchar(13) DEFAULT NULL,
`callgroup` varchar(10) DEFAULT NULL,
`callerid` varchar(80) DEFAULT NULL,
`defaultip` varchar(15) DEFAULT NULL,
`dtmfmode` varchar(7) DEFAULT NULL,
`fromuser` varchar(80) DEFAULT NULL,
`fromdomain` varchar(80) DEFAULT NULL,
`insecure` varchar(4) DEFAULT NULL,
`language` char(2) DEFAULT NULL,
`mailbox` varchar(50) DEFAULT NULL,
`pickupgroup` varchar(10) DEFAULT NULL,
`qualify` char(3) DEFAULT NULL,
`regexten` varchar(80) DEFAULT NULL,
`rtptimeout` char(3) DEFAULT NULL,
`rtpholdtimeout` char(3) DEFAULT NULL,
`setvar` varchar(100) DEFAULT NULL,
`disallow` varchar(100) DEFAULT 'all',
`allow` varchar(100) DEFAULT 'g729;ilbc;gsm;ulaw;alaw',
`fullcontact` varchar(80) NOT NULL DEFAULT '',
`ipaddr` varchar(15) NOT NULL DEFAULT '',
`port` mediumint(5) unsigned NOT NULL DEFAULT '0',
`username` varchar(80) NOT NULL DEFAULT '',
`defaultuser` varchar(80) NOT NULL DEFAULT '',
`subscribecontext` varchar(80) DEFAULT NULL,
`directmedia` enum('yes','no') DEFAULT NULL,
`trustrpid` enum('yes','no') DEFAULT NULL,
`sendrpid` enum('yes','no') DEFAULT NULL,
`progressinband` enum('never','yes','no') DEFAULT NULL,
`promiscredir` enum('yes','no') DEFAULT NULL,
`useclientcode` enum('yes','no') DEFAULT NULL,
`callcounter` enum('yes','no') DEFAULT NULL,
`busylevel` int(10) unsigned DEFAULT NULL,
`allowoverlap` enum('yes','no') DEFAULT 'yes',
`allowsubscribe` enum('yes','no') DEFAULT 'yes',
`allowtransfer` enum('yes','no') DEFAULT 'yes',
`ignoresdpversion` enum('yes','no') DEFAULT 'no',
`videosupport` enum('yes','no','always') DEFAULT 'no',
`maxcallbitrate` int(10) unsigned DEFAULT NULL,
`rfc2833compensate` enum('yes','no') DEFAULT 'yes',
`session-timers` enum('originate','accept','refuse') DEFAULT 'accept',
`session-expires` int(5) unsigned DEFAULT '1800',
`session-minse` int(5) unsigned DEFAULT '90',
`session-refresher` enum('uac','uas') DEFAULT 'uas',
`t38pt_usertpsource` enum('yes','no') DEFAULT NULL,
`outboundproxy` varchar(250) DEFAULT NULL,
`callbackextension` varchar(250) DEFAULT NULL,
`registertrying` enum('yes','no') DEFAULT 'yes',
`timert1` int(5) unsigned DEFAULT '500',
`timerb` int(8) unsigned DEFAULT NULL,
`qualifyfreq` int(5) unsigned DEFAULT '120',
`contactpermit` varchar(250) DEFAULT NULL,
`contactdeny` varchar(250) DEFAULT NULL,
`lastms` int(11) NOT NULL,
`regserver` varchar(100) NOT NULL DEFAULT '',
`regseconds` int(11) NOT NULL DEFAULT '0',
`useragent` varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `name_2` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

Apr 27/10 Serge Berney - iXo SA
Since 1.6.1.18 : Added 'useragent'
To prevent the warning message : "Table sip_buddies requires a column 'useragent' of size '20', but no such column exists."

If you have error message like :

   WARNING[27599] acl.c: Invalid IP address in 
   ERROR[27599] chan_sip.c: Bad ACL entry in configuration line 0 : 


Be sure that fields 'deny', 'permit', 'mask' contain valid IP or are set to "(NULL)" value (if no ACL control is needed)

If you have error message like :

   WARNING[27221] acl.c: Unable to lookup ''
   NOTICE[27221] chan_sip.c: Registration from '<sip:xxx@a.b.c.d:zzz>' failed for 'a.b.c.d' - No matching peer found


be sure that field 'defaultip' is not null (set value to '0.0.0.0' if you don't need it).


Apr 07/09 Akan Nkweini
Added 'lastms'.
Without this I had the following error
res_config_mysql.c:376 update_mysql: MySQL RealTime: Failed to query database. Check debug for more info.

Dec 03/08 Artem
Asterisk 1.4.21.2 (on Debian) does not work if qualify is set and rtcachefriends=yes (which is default).
qualify must be NULL or should not be in the table at all. Otherwise this error message will flood your log file/console:

handle_response_peerpoke: Peer 'xyz' is now Reachable.

Sept 18/08 caspar
It seems like Asterisk 1.6.0 RealTime SIP does not work without the following fields:
  • defaultip
  • defaultuser
  • regserver
  • regseconds
  • mask
  • fromuser
  • fromdomain

May 14/08 arf_
Added 'subscribecontext' to the above as it's may be needed in some case (and it works a least with asterisk 1.4.18)

Apr 15/08 bcnit
Added 'defaultuser' to the above as it's needed in Asterisk 1.6.

Jul 9/07 bcnit
Note that 'regserver' has been added to the above this doesn't appear to be used by anything other than the initial call RealTime makes to clear the record, but including it gets rid of a WARNING on the console!

Jun 8/07 bcnit
If you are going to access the table above using MS Access and MyODBC, define port as:

`port` mediumint(8) unsigned NOT NULL default '0'

Or you'll have all sorts of problems trying to update rows!

Jan 4/06 mhaynes
I ran into problems using the Message Waiting Indicator with my Cisco 7960g phone using the realtime system under Asterisk 1.2.0. Turns out you need to set the rtcachefriends=yes in the general context of your flat file sip.conf. Once this is done, MWI starts working as usual.

Nov 9/05 hfwang
Note: It seems that since asterisk 1.0.2RC1 there is 1 additional field for SIP accounts called 'fullcontact'. Added this field in the table above.

Updated by: DHuang

(3/16/05) Updated by: utdrmac - incominglimit and outgoinglimit are deprecated. Use Asterisk cmd SetGroup instead.

Jan 2/06 misak
Added setvar column


NOTE: The index created on the column 'name' is because RealTime does its SELECT query using that column everytime. That column must also be unique.

You do not need every column listed above. If you wish, you can remove those columns you know you will never use. The columns in your tables should line up with the fields you would specify in the given entity declaration. If an entry would appear more than once, in the column it should be separated by a semicolon. For example, an entity that looks like:

[foo]
host=dynamic
secret=bar
context=default
allow=gsm
allow=ulaw



could be stored in a table like this:


namehostsecretcontextipaddrportallow
foodynamicbardefault127.0.0.14569gsm;ulaw




You do not need to insert the ipaddr, port or regseconds information. These columns will be updated periodicaly by RealTime.



Testing

Throw some data into the above table and try to register an extension. The /var/log/asterisk/debug should give info on any problems.



Realtime Caching...

As of CVS-HEAD 3/16/05, if you enable RealTime caching in your sip.conf, Voicemail MWI works and so does 'sip show peers'. To do so, add "rtcachefriends=yes" to the general section of your sip.conf file.

As the name implies, this caches the "RealTime" information from the database. As a result, there is a delay in updating some (if not all) fields in the SIP entry when you update the database. For instance, if you create an entry with a context = "context1" and Asterisk loads it from the database (perhaps the phone registered or tried to make a call), Asterisk holds on to that information as far as I can tell, indefinitely until a sip reload occurs.

This also means that you will have to do a sip reload to clear out any entries. Removing them from the database does not seem to work. You can still add new entries though without reloading Asterisk.

RealTime caching...isn't that an oxymoron anyways? :-) — (Someone check to see if this affects IAX too, I don't have any IAX phones at the moment. - Flobi) — (RealTime caching does affect IAX as well, works the same way. - Josh)

Update : use "sip prune realtime PEERNAME" then "sip show peer PEERNAME load" to flush the peer and reload from db - (Voicemeup)



Realtime SIP and templates

Question: In the sip.conf we have the ability to leverage templates to simplify the configuration file. Is this template mechanism available via SIP realtime?

Answer: No. "The templates are part of the configuration file (text files) parser and not supported in databases."

Reference: http://comments.gmane.org/gmane.comp.telephony.pbx.asterisk.user/276114
(Added 14 Sept 2013 bluecrow76)



See Also

Created by: utdrmac, Last modification: Sat 14 of Sep, 2013 (15:06 UTC) by bluecrow76
Please update this page with new information, just login and click on the "Edit" or "Discussion" tab. Get a free login here: Register Thanks! - Find us on Google+