Asterisk realtime odbc setup

sjs205 · April 12, 2013, 3:19pm

Hello all,

Forgive me if I’ve missed something silly, but I’m new to Asterisk…

I am trying to integrate Asterisk and openSIPS using the tutorial found here: http://www.opensips.org/Resources/DocsTutAsterisk18. After many days debugging, I finally managed to get the ODBC engine working and now, have a sipusers table(View exported from opensips table) in my asterisk MySQL database.

However, it would seem that although the odbc driver is initiatised and does connect at startup, sippeers are still populated from the sip.conf file rather than the database. I have included a number of log files below:

[quote]sudo cat /etc/odbc.ini
[MySQL-asterisk]
Description = MySQL Asterisk database
Trace = Off
TraceFile = stderr
Driver = MySQL
SERVER = localhost
USER = asterisk
PASSWORD = passwd
PORT = 3306
DATABASE = asterisk[/quote]

[quote]sudo cat /etc/odbcinst.ini
[MySQL]
Description = MySQL driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
CPTimeout =
CPReuse =
UsageCount = 1
[/quote]

sudo cat /etc/asterisk/modules.conf
;
; Asterisk configuration file
;
; Module Loader configuration file
;

[modules]
autoload=yes
;
; Any modules that need to be loaded before the Asterisk core has been
; initialized (just after the logger has been initialized) can be loaded
; using ‘preload’. This will frequently be needed if you wish to map all
; module configuration files into Realtime storage, since the Realtime
; driver will need to be loaded before the modules using those configuration
; files are initialized.
;
; An example of loading ODBC support would be:
preload => res_odbc.so
preload => res_config_odbc.so
;
; If you want, load the GTK console right away.
; Don’t load the KDE console since
; it’s not as sophisticated right now.
;
noload => pbx_gtkconsole.so
;load => pbx_gtkconsole.so
noload => pbx_kdeconsole.so
;
; Intercom application is obsoleted by
; chan_oss. Don’t load it.
;
noload => app_intercom.so
;
; The ‘modem’ channel driver and its subdrivers are
; obsolete, don’t load them.
;
noload => chan_modem.so
noload => chan_modem_aopen.so
noload => chan_modem_bestdata.so
noload => chan_modem_i4l.so
;
; Comment this out (after installing CAPI middleware and hardware
; drivers) if you have CAPI-able hardware and wish to use it in
; Asterisk.
;
noload => chan_capi.so
;
load => res_musiconhold.so
;
; Load either OSS or ALSA, not both
; By default, load OSS only (automatically) and do not load ALSA
;
noload => chan_alsa.so
;noload => chan_oss.so
;
; Disable CDR logging to SQLite by default since it writes unconditionally to
; cdr.db without a way to rotate it.
;
noload => cdr_sqlite.so
;
; These conflict with app_directory.so and each other.
noload => app_directory_odbc.so
;
; Enable these if you want to configure Asterisk in a database
;
;noload => res_config_odbc.so
noload => res_config_pgsql.so
;
; Module names listed in “global” section will have symbols globally
; exported to modules loaded after them.
;
[global]

sudo service asterisk restart
Stopping Asterisk PBX: asterisk.
Starting Asterisk PBX: asterisk.

[quote]sudo tail /var/log/asterisk/messages -n 50
…
[Apr 12 16:14:40] NOTICE[27473] loader.c: 2 modules will be loaded.
[Apr 12 16:14:40] NOTICE[27473] res_odbc.c: Connecting asterisk
[Apr 12 16:14:40] NOTICE[27473] res_odbc.c: res_odbc: Connected to asterisk [MySQL-asterisk]
[Apr 12 16:14:40] NOTICE[27473] res_odbc.c: Registered ODBC class ‘asterisk’ dsn->[MySQL-asterisk]
[Apr 12 16:14:40] NOTICE[27473] res_odbc.c: res_odbc loaded.
[Apr 12 16:14:40] NOTICE[27473] config.c: Registered Config Engine odbc
[Apr 12 16:14:40] NOTICE[27473] cdr.c: CDR simple logging enabled.
[Apr 12 16:14:40] NOTICE[27473] loader.c: 228 modules will be loaded.
[Apr 12 16:14:40] NOTICE[27473] res_smdi.c: No SMDI interfaces are available to listen on, not starting SMDI listener.
[Apr 12 16:14:40] NOTICE[27473] config.c: Registered Config Engine mysql
[Apr 12 16:14:40] NOTICE[27473] res_config_ldap.c: No directory user found, anonymous binding as default.
[Apr 12 16:14:40] ERROR[27473] res_config_ldap.c: No directory URL or host found.
[Apr 12 16:14:40] ERROR[27473] res_config_ldap.c: Cannot load LDAP RealTime driver.
[Apr 12 16:14:40] NOTICE[27473] config.c: Registered Config Engine sqlite
[Apr 12 16:14:40] NOTICE[27473] config.c: Registered Config Engine curl
[Apr 12 16:14:40] NOTICE[27473] chan_skinny.c: Configuring skinny from skinny.conf
[Apr 12 16:14:40] WARNING[27473] chan_dahdi.c: Ignoring any changes to ‘userbase’ (on reload) at line 23.
[Apr 12 16:14:40] WARNING[27473] chan_dahdi.c: Ignoring any changes to ‘vmsecret’ (on reload) at line 31.
[Apr 12 16:14:40] WARNING[27473] chan_dahdi.c: Ignoring any changes to ‘hassip’ (on reload) at line 35.
[Apr 12 16:14:40] WARNING[27473] chan_dahdi.c: Ignoring any changes to ‘hasiax’ (on reload) at line 39.
[Apr 12 16:14:40] WARNING[27473] chan_dahdi.c: Ignoring any changes to ‘hasmanager’ (on reload) at line 47.
[Apr 12 16:14:40] WARNING[27473] chan_sip.c: No valid transports available, falling back to ‘udp’.
[Apr 12 16:14:40] NOTICE[27473] cel_tds.c: cel_tds has no global category, nothing to configure.
[Apr 12 16:14:40] WARNING[27473] cel_tds.c: cel_tds module had config problems; declining load
[Apr 12 16:14:40] WARNING[27473] cel_pgsql.c: CEL pgsql config file missing global section.
[Apr 12 16:14:40] NOTICE[27473] pbx_ael.c: Starting AEL load process.
[Apr 12 16:14:40] NOTICE[27473] pbx_ael.c: AEL load process: parsed config file name ‘/etc/asterisk/extensions.ael’.
[Apr 12 16:14:40] NOTICE[27473] pbx_ael.c: AEL load process: checked config file name ‘/etc/asterisk/extensions.ael’.
[Apr 12 16:14:40] NOTICE[27473] pbx_ael.c: AEL load process: compiled config file name ‘/etc/asterisk/extensions.ael’.
[Apr 12 16:14:40] NOTICE[27473] pbx_ael.c: AEL load process: merged config file name ‘/etc/asterisk/extensions.ael’.
[Apr 12 16:14:40] NOTICE[27473] pbx_ael.c: AEL load process: verified config file name ‘/etc/asterisk/extensions.ael’.
[Apr 12 16:14:40] ERROR[27473] ais/clm.c: Could not initialize cluster membership service: Try Again
[Apr 12 16:14:40] ERROR[27473] codec_dahdi.c: Failed to open /dev/dahdi/transcode: No such file or directory
[Apr 12 16:14:41] ERROR[27473] chan_vpb.cc: No Voicetronix cards detected
[/quote]

mysql query list showing odbc connecting…

              148 Field List        vmusers %
130412 16:14:40 149 Connect asterisk@localhost on asterisk
149 Query SET NAMES latin1
149 Query SET character_set_results = NULL
149 Query SET SQL_AUTO_IS_NULL = 0
149 Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
149 Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
149 Query SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND ( TABLE_TYPE=‘BASE TABLE’ OR TABLE_TYPE=‘VIEW’ ) AND TABLE_NAME LIKE ‘sipusers’
149 Field List sipusers %
149 Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
149 Query SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND ( TABLE_TYPE=‘BASE TABLE’ OR TABLE_TYPE=‘VIEW’ ) AND TABLE_NAME LIKE ‘meetme’
149 Field List meetme %
149 Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
149 Query set @@sql_select_limit=DEFAULT
149 Query SELECT COUNT() FROM voicemessages WHERE dir = ‘/var/spool/asterisk/voicemail/default/1234/INBOX’
149 Query SELECT COUNT() FROM voicemessages WHERE dir = ‘/var/spool/asterisk/voicemail/default/1234/Old’
149 Query SELECT COUNT() FROM voicemessages WHERE dir = ‘/var/spool/asterisk/voicemail/default/1234/Urgent’
149 Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
149 Query SELECT COUNT() FROM voicemessages WHERE dir = ‘/var/spool/asterisk/voicemail/other/1234/INBOX’
149 Query SELECT COUNT() FROM voicemessages WHERE dir = ‘/var/spool/asterisk/voicemail/other/1234/Old’
149 Query SELECT COUNT() FROM voicemessages WHERE dir = ‘/var/spool/asterisk/voicemail/other/1234/Urgent’
149 Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
149 Query SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND ( TABLE_TYPE=‘BASE TABLE’ OR TABLE_TYPE=‘VIEW’ ) AND TABLE_NAME LIKE ‘vmusers’
149 Field List vmusers %

I think that is everything, however, if I have missed something please let me know.

Any help would be much appreciated.

Best regards,

Steven

sjs205 · April 12, 2013, 3:49pm

Just to expand the exact issue, in my database I have two users, ‘alice’ and ‘bob’, but in my file sip.conf I have two other users called demo-alice, demo-bob.

From the asterisk command line:

[quote]Connected to Asterisk 1.8.10.1~dfsg-1ubuntu1 currently running on swannserver (pid = 27473)
Verbosity is at least 3
swannserverCLI> sip reload
Reloading SIP
swannserverCLI> sip show peers
Name/username Host Dyn Forcerport ACL Port Status Realtime
demo-alice (Unspecified) D N A 0 Unmonitored
demo-bob (Unspecified) D N A 0 Unmonitored
2 sip peers [Monitored: 0 online, 0 offline Unmonitored: 0 online, 2 offline]
swannserver*CLI> [/quote]

So shouldn’t I have the ‘alice’ and ‘bob’ users here instead of the demo-* users?

Also, I notice that when I try to register with user ‘alice’ using twinkle on another server, I get the following error:

[quote] sudo tail /var/log/asterisk/messages -n 4
[Apr 12 16:42:45] ERROR[27503] chan_sip.c: Peer ‘alice’ is trying to register, but not configured as host=dynamic
[Apr 12 16:42:45] NOTICE[27503] chan_sip.c: Registration from ‘“alice” sip:alice@swannsips.com’ failed for ‘192.168.5.10:5060’ - Peer is not supposed to register
[Apr 12 16:42:45] ERROR[27503] chan_sip.c: Peer ‘alice’ is trying to register, but not configured as host=dynamic
[Apr 12 16:42:45] NOTICE[27503] chan_sip.c: Registration from ‘“alice” sip:alice@swannsips.com’ failed for ‘192.168.5.10:5060’ - Peer is not supposed to register[/quote]

And also notice that my MySQL query log is as follows:

So obviously it is referring to the database, but should it not list these users when issuing the ‘sip show peers’ command?

Many thanks,

Steven

navaismo · April 12, 2013, 5:40pm

To see the peers with the command sip show peer try before: sip show peer bob load & sip show peer alice load

And those peers set the host as dynamic in order to allow the registration.

sjs205 · April 12, 2013, 6:58pm

Hello navaismo,

Thanks for getting back to me. I entered the commands as follows but I still don’t see the peers with the command ‘sip show peers’:

Connected to Asterisk 1.8.10.1~dfsg-1ubuntu1 currently running on swannserver (pid = 27473)
Verbosity is at least 3
swannserver*CLI> sip show peer bob load

Name : bob
Realtime peer: Yes, cached
Secret :
MD5Secret :
Remote Secret:
Context : default
Subscr.Cont. :
Language :
AMA flags : Unknown
Transfer mode: open
CallingPres : Presentation Allowed, Not Screened
FromUser : bob
FromDomain : swannsips.com Port 5060
Callgroup :
Pickupgroup :
MOH Suggest :
Mailbox : bob
VM Extension : asterisk
LastMsgsSent : 32767/65535
Call limit : 0
Max forwards : 0
Dynamic : No
Callerid : “” <>
MaxCallBR : 384 kbps
Expire : -1
Insecure : no
Force rport : Yes
ACL : No
DirectMedACL : No
T.38 support : No
T.38 EC mode : Unknown
T.38 MaxDtgrm: -1
DirectMedia : No
PromiscRedir : No
User=Phone : No
Video Support: No
Text Support : No
Ign SDP ver : No
Trust RPID : No
Send RPID : No
Subscriptions: Yes
Overlap dial : No
DTMFmode : rfc2833
Timer T1 : 500
Timer B : 32000
ToHost : swannsips.com
Addr->IP : 127.0.0.1:5060
Defaddr->IP : 127.0.0.1:5060
Prim.Transp. : UDP
Allowed.Trsp : UDP
Def. Username: bob
SIP Options : (none)
Codecs : 0x80000008000e (gsm|ulaw|alaw|h263|testlaw)
Codec Order : (none)
Auto-Framing : No
Status : Unmonitored
Useragent :
Reg. Contact :
Qualify Freq : 60000 ms
Sess-Timers : Accept
Sess-Refresh : uas
Sess-Expires : 1800 secs
Min-Sess : 90 secs
RTP Engine : asterisk
Parkinglot :
Use Reason : No
Encryption : No

swannserver*CLI> sip show peers
Name/username Host Dyn Forcerport ACL Port Status Realtime
demo-alice (Unspecified) D N A 0 Unmonitored
demo-bob (Unspecified) D N A 0 Unmonitored
2 sip peers [Monitored: 0 online, 0 offline Unmonitored: 0 online, 2 offline]

Could this be because the peers I’ve set up in the database are not dynamic? That is, because I have created the ‘sipsusers’ table as a view of the ‘subscriber’ table in the openSIPS database, a number of the fields are linked - as instructed to by the tutorial linked above - such as the domain, host, defaultip…

That being the case I should probably modify the table syntax further. My version of the tutorial’s table is as follows:

CREATE TABLE IF NOT EXISTS sipusers_floating_fields (
port int(5) DEFAULT NULL,
regseconds int(11) DEFAULT NULL,
fullcontact varchar(35) DEFAULT NULL,
useragent varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO sipusers_floating_fields (port, regseconds, fullcontact, useragent) VALUES
(5060, NULL, NULL, NULL);

CREATE VIEW sipusers AS select
opensips.subscriber.username AS name
,_latin1’friend’ AS type,
NULL AS secret,
opensips.subscriber.domain AS host,
concat(opensips.subscriber.rpid,_latin1’ ‘,_latin1’<‘,opensips.subscriber.username,_latin1’>‘) AS callerid,
_latin1’default’ AS context,
opensips.subscriber.username AS mailbox,
_latin1’yes’ AS nat,
_latin1’no’ AS qualify,
opensips.subscriber.username AS fromuser,
NULL AS authuser,
opensips.subscriber.domain AS fromdomain,
NULL AS insecure,
_latin1’no’ AS canreinvite,
NULL AS disallow,
NULL AS allow,
NULL AS restrictcid,
opensips.subscriber.domain AS defaultip,
opensips.subscriber.domain AS ipaddr,
asterisk.sipusers_floating_fields.port AS port,
asterisk.sipusers_floating_fields.regseconds AS regseconds,
opensips.subscriber.username AS defaultuser,
asterisk.sipusers_floating_fields.fullcontact AS fullcontact,
opensips.subscriber.domain AS regserver,
asterisk.sipusers_floating_fields.useragent AS useragent,
0 AS lastms
from opensips.subscriber,asterisk.sipusers_floating_fields;