Asterisk realtime mysql ( tried every things, please help )

man2asterisk · November 7, 2008, 10:53pm

I have a problem with Asterisk Realtime setup for sip and extensions, I have tried every possible thing nothing works :-

My Asterisk Version :-
Connected to Asterisk 1.2.12.1 currently running on asterisk1 (pid = 2061)

Status of mysql realtime :-

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

My extconf.conf :-

[settings]
sipusers =>mysql,asterisk,sip_buddies
sippeers => mysql,asterisk,sip_buddies
extensions => mysql,asterisk,extensions_r
voicemail => mysql,asterisk,voicemessages
queues => mysql,asterisk,queues
queue_members => mysql,asterisk,queue_members

My sip.conf

[general]
context=default ; Default context for incoming calls
bindport=5060

type=friend
rtcachefriends = yes

Debug Details by using asterisk -vvvvvvvvgc shows that mysql realtime is loaded :-

MySQL RealTime driver loaded.
[app_realtime.so] => (Realtime Data Lookup/Rewrite)
[pbx_realtime.so] => (Realtime Switch)
[res_features.so] => (Call Features Resource)

I have merged the standard Asteriskrealtime database tables to asterisk database thats why you will see asterisk as DB in sipusers =>mysql,asterisk,sip_buddies .

When registering with x-lite to a sip user in sip.conf as static it works fine :-
– Registered SIP ‘203’ at 122.169.89.91 port 12092 expires 3600
– Saved useragent “X-Lite release 1100l stamp 47546” for peer 203

Where as if i try to register an entry in database with same x-lite it shows nothing at all on cli debug but on xlite it gives Not found 404 error .

Even checked the debug logs and I dont see any query fail error only references to mysql or realtime are :-

4 16:22:59 VERBOSE[2866] logger.c: [res_config_mysql.so]Nov 4 16:22:59 VERBOSE[2866] logger.c: [res_config_mysql.so] => (MySQL RealTime Configuration Driver

4 16:22:59 DEBUG[2866] res_config_mysql.c: MySQL RealTime Host: 127.0.0.1
Nov 4 16:22:59 DEBUG[2866] res_config_mysql.c: MySQL RealTime Port: 3306
Nov 4 16:22:59 DEBUG[2866] res_config_mysql.c: MySQL RealTime User: root
Nov 4 16:22:59 DEBUG[2866] res_config_mysql.c: MySQL RealTime Password: pass55689
Nov 4 16:22:59 DEBUG[2866] res_config_mysql.c: MySQL RealTime: Successfully connected to database.
Nov 4 16:22:59 NOTICE[2866] config.c: Registered Config Engine mysql
Nov 4 16:22:59 VERBOSE[2866] logger.c: MySQL RealTime driver loaded.
Nov 4 16:22:59 VERBOSE[2866] logger.c: [app_realtime.so]Nov 4 16:22:59 VERBOSE[2866] logger.c: [app_realtime.so] => (Realtime Data Lookup/Rewrite)
Nov 4 16:22:59 VERBOSE[2866] logger.c: [pbx_realtime.so]Nov 4 16:22:59 VERBOSE[2866] logger.c: [pbx_realtime.so] => (Realtime Switch)
Nov 4 16:22:59 VERBOSE[2866] logger.c: [res_features.so]Nov 4 16:22:59 VERBOSE[2866] logger.c: [res_features.so] => (Call Features Resource)

I tried the following and got the error but when i tried to look for error entry on log files both full and debug at /var/log/asterisk where debug and full log files are located i could not find the error as it says in the error below am I looking at wrong place ? I believe if I can find the reason of this error then may be I would be able to trace it down

*CLI> realtime update sipusers name 200 username 121
Failed to update. Check the debug log for possible SQL related entries.
*CLI>

Mark_Logan · November 7, 2008, 11:31pm

Could you paste what you see in the database when you do the following:

Yep, it’s going to be a big mess. (Post it as [code ] for best results maybe?)

and

man2asterisk · November 7, 2008, 11:55pm

Hi Mark , Below is the result as asked :-

[code]

Mark_Logan · November 8, 2008, 12:08am

eek, I forgot to ask you to post the portion from your sip.conf that you are using for the ext.203 registration.

Also please post the X-lite settings that you are using for the successful sip registration (NON-realtime) and the x-lite settings for the un-successful sip registration (realtime)

Sorry I didn’t ask earlier!

man2asterisk · November 8, 2008, 12:20am

[203]
username=203
type=friend
context=test
host=dynamic
dtmfmode=rfc2833

x-lite is same just the asterisk server ip . But issue is while using the "realtime load or update " it does not show any thing ? also with “sip show users” it shows nothing of realtime .

man2asterisk · November 8, 2008, 2:21pm

Can some one help please

Mark_Logan · November 9, 2008, 2:29am

Okay, first things first, when you do “sip show users” and nothing happens, this is normal. It might be odd, but it doesn’t list realtime sip users. That’s just how it is.

Ummm okay, so when you try to register with your asterisk server from X-lite, does anything pop up on the asterisk CLI? Like an error saying it tried to register but failed?

man2asterisk · November 9, 2008, 3:27pm

I ran the asterisk with asterisk -vvvvvv -g -dddddd -c also , nothing show up at all on CLI.
If I enable the rtcachefriends=yes in sip.conf then x-lite first says registering for afound 30-45 seconds and then say Registration error :408 Request Timeout and if i disable the rtcachefriends=yes in sip.info then x-lite gives 404 not found error immediately .

Mark_Logan · November 10, 2008, 12:07am

Well, lets just try working with user 121 in your database.
If you’re trying to register 121 then we have to fix some issues.

You have the following settings:

username = 121 secret = NULL

So first of all, update that row like this:

UPDATE sip_buddies set secret="whatever" where username=121;
Then use the Xlite settings of:

displayname=121 username=1212 password=whatever authorized user name = <leave this blank> Domain=<server IP or name>

If that doesn’t work write in the error seen on the CLI and the Xlite error. Thanks.

man2asterisk · November 10, 2008, 5:34pm

Hi Mark ,

I tried t updated the database tables with the information as you suggested which was done successfully as I checked the database after the update . But no luck the issue is the same. Nothing on CLI , nothing in asterisk log files or mysql log file .

Mark_Logan · November 10, 2008, 8:23pm

When you are getting these database tables, are you logging into mysql with the root user or the asterisk user?

And if you merged the databases together to one called “asterisk”… why are you still registering with asteriskrealtime?

[quote]asterisk1*CLI> realtime mysql status
Connected to asteriskrealtime@127.0.0.1, port 3306 with username root for 1 minutes, 38 seconds.[/quote]

I think you forgot to edit your res_mysql.conf…

If so, change that line to

If you still have issues, paste that document in here too. (scratch out the “sercret=” part)

man2asterisk · November 15, 2008, 12:56am

I have exactly same database, but since you pointed out I even tried that no luck . no luck at all

Rogier21 · November 23, 2008, 6:33pm

Just to give this topic a kick, I am trying to get it working aswell and it is showing the exact same results.

I copied all data from sip.conf to my ast_sip_buddies table.
res_mysql.conf

[general]
dbhost = 192.168.193.2
dbname = asterisk
dbuser = root
dbpass = pass
dbport = 3306
dbsock = /var/lib/mysql/mysql.sock

sip.conf

[general]
context=default
bindport=5060
language=en

extensions.conf

[general]
static = yes
writeprotect = yes
autofallthrough = yes
clearglobalvars = no
priorityjumping = yes

In my ast_sip_buddies table I have 1 row:

+----+-----------------+---------+-----+--------+-------------+----------+------------+-----------+-------------------------+----------------+-------------+-----------+-----------+----------+----------+------------+----------+----------+---------------+----------------------------------+------+--------+------+-------------+-------------+---------+----------+-------------+------------+----------------+--------+--------+----------+-------------------------+-------------+--------+------+-----------+------------+----------+-------------+------------------+
| id | name            | host    | nat | type   | accountcode | amaflags | call-limit | callgroup | callerid                | cancallforward | canreinvite | context   | defaultip | dtmfmode | fromuser | fromdomain | insecure | language | mailbox       | md5secret                        | deny | permit | mask | musiconhold | pickupgroup | qualify | regexten | restrictcid | rtptimeout | rtpholdtimeout | secret | setvar | disallow | allow                   | fullcontact | ipaddr | port | regserver | regseconds | username | defaultuser | subscribecontext |
+----+-----------------+---------+-----+--------+-------------+----------+------------+-----------+-------------------------+----------------+-------------+-----------+-----------+----------+----------+------------+----------+----------+---------------+----------------------------------+------+--------+------+-------------+-------------+---------+----------+-------------+------------+----------------+--------+--------+----------+-------------------------+-------------+--------+------+-----------+------------+----------+-------------+------------------+
|  1 | My Name | dynamic | yes | friend | 1           | NULL     |       NULL | NULL      | "My Name" <100> | yes            | yes         | account_1 | NULL      | NULL     | a1u1e100 | NULL       | invi     | NULL     | 100@account_1 | 35131eb77d09231ab920eb82a83ecc86 | NULL | NULL   | NULL | NULL        | NULL        | NULL    | NULL     | NULL        | NULL       | NULL           | NULL   | NULL   | all      | g729;ilbc;gsm;ulaw;alaw |             |        |    0 | NULL      |          0 | a1u1e100 |             | NULL             |
+----+-----------------+---------+-----+--------+-------------+----------+------------+-----------+-------------------------+----------------+-------------+-----------+-----------+----------+----------+------------+----------+----------+---------------+----------------------------------+------+--------+------+-------------+-------------+---------+----------+-------------+------------+----------------+--------+--------+----------+-------------------------+-------------+--------+------+-----------+------------+----------+-------------+------------------+

When I try to connect to Asterisk with XLite this shows up in the cli:

[Nov 23 03:13:29] NOTICE[11485]: chan_sip.c:15593 handle_request_register: Registration from '"My Name"<sip:a1u1e100@192.168.193.3>' failed for '192.168.193.1' - No matching peer found

I was able to login when I had this user still in sip.conf, but it looks like that the MySQL table is not being read.

MySQL is still there:

Connected to asterisk@192.168.193.2, port 3306 with username root for 3 seconds.

Strange that it resets all the time, it is never more than 3-5 seconds.

When I start Asterisk with “asterisk -vvvvvvvvvvgc” I don’t get any MySQL errors.

Only these MySQL messages come by:

 Binding iaxusers to mysql/asterisk/ast_iax_buddies
  == Binding iaxpeers to mysql/asterisk/ast_iax_buddies
  == Binding sipusers to mysql/asterisk/ast_sip_buddies
  == Binding sippeers to mysql/asterisk/ast_sip_buddies
  == Binding voicemail to mysql/asterisk/ast_voicemail_users
  == Binding extensions to mysql/asterisk/ast_extensions_table
  == Binding queues to mysql/asterisk/ast_queue_table
  == Binding queue_members to mysql/asterisk/ast_queue_member_table
  == Binding extensions to mysql/asterisk/ast_extensions_table
  == Parsing '/etc/asterisk/res_mysql.conf': Found
[Nov 23 03:15:05] NOTICE[11589]: config.c:1276 ast_config_engine_register: Registered Config Engine mysql
res_config_mysql.so => (MySQL RealTime Configuration Driver)
app_addon_sql_mysql.so => (Simple Mysql Interface)
  == Parsing '/etc/asterisk/cdr_mysql.conf': Found
cdr_addon_mysql.so => (MySQL CDR Backend)

Strange thing is that CDR records do work.

man2asterisk · November 24, 2008, 4:57am

I still have no luck, even installed different version of asterisk on other machines to try out but no luck

I dont even get what u getting :-

Binding iaxusers to mysql/asterisk/ast_iax_buddies
== Binding iaxpeers to mysql/asterisk/ast_iax_buddies
== Binding sipusers to mysql/asterisk/ast_sip_buddies
== Binding sippeers to mysql/asterisk/ast_sip_buddies
== Binding voicemail to mysql/asterisk/ast_voicemail_users
== Binding extensions to mysql/asterisk/ast_extensions_table
== Binding queues to mysql/asterisk/ast_queue_table

even my cdr records with mysql works just fine .

Rogier21 · November 24, 2008, 8:29am

When I run this command in the cli:
realtime load sipusers name “My Name”

It will give me this result:

                   Column Name  Column Value
          --------------------  --------------------
                            id  1
                          name  My Name
                          host  dynamic
                           nat  yes
                          type  friend
                      callerid  "My Name" <100>
                       context  account_1
                       mailbox  100@account_1
                        secret  a1u1e100
                      username  a1u1e100

Which is exactly as I stored it in the database.

But when I do this:
sip show peers

It gives me nothing, even with rtcachefriends=yes in sip.conf

Mark_Logan · November 24, 2008, 8:06pm

Okay, as I have said before If “sip show peers” does not seem to bring up anything from the realtime database, this is normal. I have the SIP peers in my database working fine and “sip show peers” doesn’t do anything.

So, Rogier21 it looks like you have the user registered and everything in your realtime db. Now before you try and connect using your softphone AND your realtime db you need to be sure you can connect via a softphone and a regular entry in the sip.conf. Because if you can’t do that, you are looking at some firewall issues. If you have done that and it’s all working, enter in all the values and post back what happens when you try and register. There should be some output to the CLI.

man2asterisk
what do you get when you do the "realtime load sipusers name " thing?

Rogier21 · November 24, 2008, 8:13pm

[quote=“Mark_Logan”]Okay, as I have said before If “sip show peers” does not seem to bring up anything from the realtime database, this is normal. I have the SIP peers in my database working fine and “sip show peers” doesn’t do anything.

So, Rogier21 it looks like you have the user registered and everything in your realtime db. Now before you try and connect using your softphone AND your realtime db you need to be sure you can connect via a softphone and a regular entry in the sip.conf. Because if you can’t do that, you are looking at some firewall issues. If you have done that and it’s all working, enter in all the values and post back what happens when you try and register. There should be some output to the CLI.

man2asterisk
what do you get when you do the "realtime load sipusers name " thing?[/quote]
Hello Mark,

According the manual “sip show peers” should give results, but I also found out it is very sporadic, sometimes yes, sometimes no.

My problem was a simple and stupid one, I wrote in my configuration (in the database) Name: My Name, but username was 100, so I tried to login with 100. I found out later that Name is actually the same (?) as username, but I assumed it was the real name of the person!

Can you tell what the difference is between Name and Username and Fromuser?

Thanks for your answer!

Topic		Replies	Views
Realtime Setting UP Problems Asterisk Support	2	568	March 19, 2010
Realtime Errors Asterisk Support	2	221	November 21, 2005
Asterisk realtime mysql problem Asterisk Support	12	5927	April 15, 2009
Asterisk Realtime Asterisk Support	11	398	April 24, 2009
Realtime (MySQL) Problems Asterisk Support	0	137	May 20, 2006

Asterisk realtime mysql ( tried every things, please help )

Related topics