Asterisk realtime CDR and SIP

As I configure the asterisk work with two function realtime. CDR runs well but SIP always got a warning.

Here is what I got from the debug

Jul 31 17:42:46 DEBUG[4779] res_config_mysql.c: MySQL RealTime Host: 132.230.4.95
Jul 31 17:42:46 DEBUG[4779] res_config_mysql.c: MySQL RealTime Port: 3306
Jul 31 17:42:46 DEBUG[4779] res_config_mysql.c: MySQL RealTime User: root
Jul 31 17:42:46 DEBUG[4779] res_config_mysql.c: MySQL RealTime Password: XXXXXX
Jul 31 17:42:46 DEBUG[4779] res_config_mysql.c: MySQL RealTime: Successfully connected to database.
Jul 31 17:42:47 DEBUG[4779] res_config_mysql.c: MySQL RealTime: Static SQL: SELECT category, var_name, var_val, cat_metric FROM sip_buddies WHERE filename='sip.conf' and commented=0 ORDER BY filename, cat_metric desc, var_metric asc, category, var_name, var_val, id
Jul 31 17:42:47 DEBUG[4779] res_config_mysql.c: MySQL RealTime: Everything is fine.
Jul 31 17:42:47 DEBUG[4779] res_config_mysql.c: MySQL RealTime: Query: SELECT category, var_name, var_val, cat_metric FROM sip_buddies WHERE filename='sip.conf' and commented=0 ORDER BY filename, cat_metric desc, var_metric asc, category, var_name, var_val, id
Jul 31 17:42:47 DEBUG[4779] res_config_mysql.c: MySQL RealTime: Query Failed because: Unknown column 'category' in 'field list'

The warning message is below

......Aug  1 22:40:03 WARNING[9199]: res_config_mysql.c:388 config_mysql: MySQL RealTime: Failed to query database. Check debug for more info.
Aug  1 22:40:03 NOTICE[9199]: chan_sip.c:12485 reload_config: Unable to load config sip.conf

I have sure the mysql server runs well, because when I input the “realtime mysql status”, console responds like

*CLI> realtime mysql status
Connected to asterisk@127.0.0.1, port 3306 with username root for 1 minutes, 38 seconds.

is there someone can give me some hits? I try to solute this problem for about one month. :cry: :cry:

Yan,

I had the same problem. I think you configured your extconfig.conf for static database access, altough your table definition (sip_buddies) is for realtime access.
you should have something like :

sipusers => mysql,asterisk,sip_buddies
sippeers => mysql;asterisk,sip_buddies

I’ve been looking around myself for the exact database schema of the version 1.2.9.1 with the asterisk-addons 1.2.3, but can’t find it anywhere than in the docs “asterisk realtime” on www.voip-info.org.

Somebody has a more accurate database schema ?

Hi, obe1

you can try this sip table for the realtime configure
http://www.voip-info.org/wiki/view/Asterisk+RealTime+Sip

I had try your way to solve this problem, it seems there is no complain now.
I use the table I found in the link i listed aboved for my asterisk sip users. And I input one record in this sip_buddies table. I suppose I should see this record works when i check in the asterisk console using

sip show peers
sip show users

but there is only the users i defined in the sip.conf file.

Hi Yan,

I’ve used that doc, you mentioned.
I think it’s normal that there’s no result with sip show users and sip show peers,since they are loaded realtime.
Normally changeing in sip.conf “rtcachefriends=yes” should make them available in the sip show users/peers. But I can 't get a sipphone registered when using the realtime configuration. I get always authorisation problems. So I haven’t seen an output when sipusers have registered realtime.

Have you ? What 's shown in the asterisk console when your phone registers ?

I still didn’t get the right responds from asterisk.

Here is my configuration for the extension:

extension.conf

[incoming]
exten => 505,1,Dial(SIP/test)
exten => 505,2,Busy

exten => 506,1,Dial(IAX2/test)
exten => 506,2,Busy

[default]
switch => Realtime/mysqlcontext@realtime_ext

extconfig.conf

mysql server, asterisk database
table: extensions_table

mysql> select * from extensions_table;
+----+--------------+-------+----------+------+----------------+
| id | context      | exten | priority | app  | appdata        |
+----+--------------+-------+----------+------+----------------+
|  1 | mysqlcontext | 701   |        1 | Dial | SIP/sipuser,30 |
+----+--------------+-------+----------+------+----------------+

sip_buddies


mysql> select name,context from sip_buddies;
+---------+----------+
| name    | context  |
+---------+----------+
| test    | incoming |
| sipuser | default  |
+---------+----------+

The result by now I got is that:

  1. sip show peers and sip show users commands can’t find the test and sipuser. just the users I defined in sip.conf.
  2. using the software connect to the asterisk. when dail 505 and 701. no responds. just a warning message from *

Aug 2 15:29:18 NOTICE[20644]: app_dial.c:1012 dial_exec_full: Unable to create channel of type 'SIP' (cause 3 - No route to destination)
3. when the * start up, there is one more warning now, when I use the realtime extension.

.Aug  2 15:28:12 WARNING[20616]: pbx.c:3644 ast_context_create: Tried to register context 'default', already in use

obe1, here is my email qiuhaiyin@hotmail.com. you can contact me.

Yan, In your extensions_table you should type " SIP/sipuser|30 " .
The comma gives problems,

Further I’ve got my server running with real sipusers en extensions.
I’ve moved a side my complete config and installated the samples config ( version 1.2.9.1 )
Now I’ve to look for the problem causing config, by putting back one by one my old configs.

For your problem, first concentrate on the sipphones getting registered.

In your sip_buddies only fill in the fields name, secret,host and context.

Even without a single extension defined, your sipphone should register.
And you should see something like :

-- SIP Seeding peer from astdb: 'o-tomw' at o-tomw@10.65.1.47:5061 for 1800
-- Registered SIP 'o-tomw' at 10.65.1.47 port 5061 expires 1800
-- Saved useragent "X-Lite release 1105d" for peer o-tomw

Asterisk then updates your sip_buddies table :

UPDATE sip_buddies SET ipaddr = ‘10.65.1.47’, port = ‘5061’, regseconds = ‘1154677981’, username = ‘o-tomw’, fullcontact = ‘sip:o-tomw@10.65.1.47:5061’ WHERE name = ‘o-tomw’

After this SIPphone has registered, his “route” is known to the system.

Then you 'll need to add the extensions in the right context (perhaps first try to add the context with the extension in extensions.conf before trying the mysqltable)