Asterisk realtime mysql problem

Asterisk Asterisk Support
1

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.6.1-rc1 with addons 1.6.1-rc2
My Freepbx version:-
2.5.1

Status of mysql realtime :-

fabrikam3*CLI> realtime mysql status
general connected to asterisk@127.0.0.1, port 3306 with username asterisk for 0 seconds.

My res_mysql.conf

[general]
dbhost = 127.0.0.1
dbname = asterisk
dbuser = asterisk
dbpass = ********
dbport = 3306
dbsock = /var/run/mysqld/mysqld.sock
requirements=warn ; or createclose or createchar

Wenn i change my res_mysql.conf to
dbhost = 127.0.0.1 --> dbhost = 192.168.1.14

i get the following error

fabrikam3*CLI> realtime mysql status
general connected to asterisk@127.0.0.1, port 3306 with username asterisk.

so without telling my the seconds but wenn i look to the log i see this.

[Feb 19 13:11:13] ERROR[2607] res_config_mysql.c: MySQL RealTime: Failed to connect database server asterisk on 192.168.1.14 (err 2003). Check debug for more info.

My extconf.conf :-

[settings]

; => ,[,table_name]
sippeers => mysql,asterisk,sip
sipusers => mysql,asterisk,sip

Debug Details by using asterisk -vvvvvvvvgc shows =========================================================================
== Parsing ‘/etc/asterisk/asterisk.conf’: == Found
== Parsing ‘/etc/asterisk/extconfig.conf’: == Found
== Binding sippeers to mysql/asterisk/sip
== Binding sipusers to mysql/asterisk/sip
Asterisk already running on /var/run/asterisk.ctl. Use ‘asterisk -r’ to connect.

Also wenn i try this i will get an error.

fabrikam3*CLI> realtime update sipfriends name nilsvanwoensel port 3306
Failed to update. Check the debug log for possible SQL related entries.
Command ‘realtime update sipfriends name nilsvanwoensel port 3306’ failed.

The logs from asterisk. Don’t get it why he would not connect

[Feb 19 12:33:29] WARNING[2464] config.c: Realtime mapping for ‘sippeers’ found to engine ‘mysql’, but the engine is not available
[Feb 19 12:33:29] WARNING[2464] config.c: Realtime mapping for ‘sippeers’ found to engine ‘mysql’, but the engine is not available
[Feb 19 12:33:29] NOTICE[2414] config.c: Registered Config Engine mysql
[Feb 19 12:33:29] WARNING[2414] translate.c: plc_samples 160 format f
[Feb 19 12:33:33] NOTICE[2464] chan_sip.c: Peer ‘nilsvanwoensel’ is now UNREACHABLE! Last qualify: 0
[Feb 19 12:33:43] WARNING[2464] res_config_mysql.c: MySQL RealTime: Invalid database specified: asterisk
[Feb 19 12:33:43] WARNING[2464] res_config_mysql.c: MySQL RealTime: Invalid database specified: asterisk
[Feb 19 12:33:43] NOTICE[2464] chan_sip.c: Peer ‘nilsvanwoensel’ is now Reachable. (103ms / 2000ms)
[Feb 19 12:34:43] WARNING[2464] res_config_mysql.c: MySQL RealTime: Invalid database specified: asterisk
[Feb 19 12:34:43] WARNING[2464] res_config_mysql.c: MySQL RealTime: Invalid database specified: asterisk
[Feb 19 12:35:43] WARNING[2464] res_config_mysql.c: MySQL RealTime: Invalid database specified: asterisk

I work with freepbx 2.5.1 so the database is a bit different if i look to the tutorails @ internet. I took the database files that are located in the package off freepbx. Wenn I add an user with freepbx he wil work fine. But when I create it only with sql he does nothing till i submit and reload with freepbpx.

Strange thing is wenn i add a user with freebpx and the SIP is working with asterisk and I change a thing in the mysql database and I use the command /var/lib/asterisk/bin/module_admin reload
he takes the info from the database and will use it for asterisk, but wenn i make a new user this won’t work ?? :confused:

Here my table

  id   keyword  data  flags  
  1 account nilsvanwoensel 0 
  1 accountcode   0 
  1 allow   0 
  1 callerid device <1> 0 
  1 callgroup   0 
  1 canreinvite no 0 
  1 context from-internal 0 
  1 deny 0.0.0.0/0.0.0.0 0 
  1 dial SIP/nilsvanwoensel 0 
  1 disallow   0 
  1 dtmfmode rfc2833 0 
  1 host dynamic 0 
  1 mailbox nilsvanwoensel@device 0 
  1 nat yes 0 
  1 permit 0.0.0.0/0.0.0.0 0 
  1 pickupgroup   0 
  1 port 5060 0 
  1 qualify yes 0 
  1 record_in Always 0 
  1 record_out Always 0 
  1 secret 1234 0 
  1 type friend 0

Can somebody help me please ?

2

Could you login to mysql with user “asterisk” and do a “show databases” the error above says that that database simply doesn’t exist…

3

Yes i can. Also i can connect with phpmyadmin and fill the database

4

Uhhh… what I meant was can you please login do a “show databases” and post what you see to this thread. Copy & Paste style.

5

I think your problem may be this:

dbhost = 127.0.0.1
dbname = asterisk
dbuser = asterisk
dbpass = ********
dbport = 3306
dbsock = /var/run/mysqld/mysqld.sock

I would try removing the dbsock property. I am guessing it will clear up the issue.

[Edit] After looking at some docs, the dbsock may be ignored if the dbhost doesn’t appear to be the local host, still worth a shot [/edit]

6

@g2010, i removed that dbsock but it is not fixing the problem

mysql -u asterisk -p

Will show this.

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| asterisk |
| asteriskcdrdb |
| mysql |
±-------------------+
4 rows in set (0.00 sec)

7

Aren’t you having a problem connecting to a remote MySQL server? If so, you should use the -H switch for the mysql command line to connect to a different host.

8

I try to connect on the server hisself with 127.0.0.1 so remote mysql is not important because i don’t use it ?

9

I thought the whole point of this thread was that you received errors when trying to connect to a 192.168.X.X IP address instead of 127.0.0.1?

10

No i just want to connect to mysql on the server itself

11

Ah, I was completely confused by your statement:

[quote]Wenn i change my res_mysql.conf to
dbhost = 127.0.0.1 --> dbhost = 192.168.1.14

i get the following error [/quote]

I thought the whole problem came about when you were trying to connect to a remote MySQL server. Here is what you should try, and if this doesn’t clear things up then I am out of ideas.

install the mysql-client and mysql-devel libraries via yum or apt, or whatever your system uses.

then do

make clean
./configure
make all
make install

12

Make sure your MySQL server listening to 0.0.0.0 or your 192.x.x.x

run the command:

netstat -l -p -n

and if you have a firewall make sure it accept your incoming connect coz firewalls doesn’t filter any connection to 127.0.0.1 loopback
8)

13

Does freePBX do SIP realtime now?

I read that you tried to create your own extensions through mysql alone. Being a freePBX system, you may have to add stuff to astDB.

Do a
CLI> database show

and you’ll see a bunch of extra stuff in there that freePBX uses in the dialplan.

Also - just for testing
mysqldump your current database
create a new database
grant a new user access to this new database.
load the dumped data.
change the dbname,user,pass in asterisk realtime.

Maybe there is something simple that has been overlooked.

Chris