Sip.conf realtime update using mysql

[yan]

Recently I setup a mysql database to support the sip.conf. I check some information in the voip-info.org. it should be res_mysql.conf existed, which is used to confiure to connect the remote mysql server.

I found that there is no file named this in my etc/asterisk/ directory but only res_odbc.conf. So is it necessary? if yes, then how can i get it?

I also read some others http://www.voip-info.org/wiki/view/Asterisk+sip+mysql+peers
it said that i should do

[quote]You also need to define the following variables in the general section of sip.conf and iax.conf:

* dbname: Name of database in your Mysql server
* dbhost: Hostname of server
* dbuser: Username in MySQL
* dbpass: Password for user in MySQL 

The following data is stored in the database:

* name: Name of peer (used in SIP registration)
* secret: Secret (password) stored in plaintext!
* context: Default context for incoming calls from user
* ipaddr: Default IP address of user
* port: Default port 

[/quote]

As I understand. I don’t need to configure the res_mysql.conf also will work. is it ?

it there any hits for me that how to run this? for example, how many conf file I need to configure?

[yan]

my asterisk server doesn’t have the res_mysql.conf under /etc/asterisk/

and also doesn’t have the res_config_mysql.so module under /usr/lib/asterisk/module

how can I get this?

[rusty]

asterisk-addons - An addons package, which includes MySQL support for call detail records

voip-info.org/wiki/index.php … isk-addons

[yan]

I have install this package. there is res_mysql.conf.sample file but I don’t have any res_config_mysql.so file still.

The problem is that there is no detail documentation for the res_odbc.conf

I am not so sure whether in the version 1.2.6, asterisk only has one conf file .

[baconbuttie]

did you get any error messages when compiling ? how are you installing it ?

you don’t need res_odbc.conf if you’re using MySQL only.

and cross-posting is considered rude !

[baconbuttie]

From a cross-posted thread[quote=“yan”]
make clear
make install[/quote]
there is no “make clear” … “make && make install”

@yan

Is it some wrong when i complie, because I don’t get the res_config_mysql.so file at all.

i don’t know, what is displayed on the console when you compile ?

@yan

If I can get the res_config_mysql.so, should I put it in the /usr/lib/asterisk/module/?

if it’s not compiling, then something is wrong. fix that first.

edit: is it BSD you’re using ?

[yan]

sorry for the cross-posting. I don’t know where should I post.

using make clean just for sure that clear up the last time install file. It seems that i need to install the mysql server and develop package before i install asterisk-addons package.

Anyway thanks.

[baconbuttie]

@yan

It seems that i need to install the mysql server and develop package before i install asterisk-addons package.

you can be forgiven for not knowing that. the README doesn’t state it. the wiki does though - voip-info.org/wiki-Asterisk+cdr+mysql

[yan]

Now I do some configuration in two conf files: extconfig.conf,res_mysql.conf

But the asterisk complained that:

.Jul 7 23:08:12 WARNING[20188]: res_config_mysql.c:388 config_mysql: MySQL RealTime: Failed to query database. Check debug for more info.
Jul 7 23:08:12 NOTICE[20188]: chan_sip.c:12307 reload_config: Unable to load config sip.conf

Here is my configure files:

extconfig.conf

[setting]

sip.conf=>mysql,asterisk,sip_test

res_mysql.conf

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

I had already setup the database name asterisk in the MYSQL server. and also insert one user in it. But it seems that it can’t connect the MYSQL server. What is wrong?[/quote]

[baconbuttie]

if you turn debug on in logger.conf you’ll see the actual error. it’s probably that the user isn’t permitted to connect to the server from that host.

[yan]

acturlly I had open the debug in logger.conf. There is no complain.

And in the /var/log/asterisk/debug file there is a blank file …first time I want some complain.

I am sure that I can login the database. I gurant all the right to the “root” user. and I also try to command in other PC to connect the mysql server
（mysql -u root -h XXX.XXX.XXX -p) it can see all the database I created including the ‘asterisk’ I had used in the extconfig.conf file.

[yan]

I suddenly remenber that I don’t use the plaintext password for the database.

so i change the res_mysql.conf dbpass=XXXXXXX

where XXXXX is the encrpt of my original password I got from the MYSQL SERVER mysql database and user table.

using the select password from user where user=‘root’;

but I got a serious error from asterisk

.Jul 8 00:12:15 ERROR[20709]: res_config_mysql.c:615 mysql_reconnect: MySQL RealTime: Failed to connect database server asterisk on 132.230.4.95. Check debug for more info.

what is wrong still no complain from the debug file.

[bira_more]

You must use plaintext password.
You must create DB for real time.
You must create all neccessary tables.
You must use user who have rights to use DB for real time.

[yan]

You must create all neccessary tables.

what does it mean? as I only use the sip.conf=>mysql,asterisk,sip_test in the extconfig.conf file, so I believe the table I need in only sip_test, right?

[yan]

I check it again, the database should be right, but still the warning from the asterisk:

.Jul 8 22:37:39 WARNING[31410]: res_config_mysql.c:388 config_mysql: MySQL RealTime: Failed to query database. Check debug for more info.
Jul 8 22:37:39 NOTICE[31410]: chan_sip.c:12307 reload_config: Unable to load config sip.conf

there is no debug information in the debug file, what can i do?

my asterisk version is 1.2.6. it should be supported the mysql realtime function.

[bira_more]

You need to look at:
voip-info.org/wiki/view/Aste … alTime+Sip
I mean you need to create databases and trables, ofcource if you haven’t already done it.
At link above you will see how to create tables for SIP pears/users/friends - and Asterisk will look there also, not onnly in sip.conf.
Same way you can proceed with iax.conf
extensions.conf - I have no experience using extensions from DB.

[yan]

I have no idea. Actually I have follow the hits from the website you recommended.

If I use below lines in extconfig.conf. there is no complain like:WARNING[11508]: res_config_mysql.c:388 config_mysql: MySQL RealTime: Failed to query database. Check debug for more info.

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

And then I use sip show peers in asterisk console, there is no entries from the MYSQL database. only the entries in sip.conf.

but if i use

sip.conf=>mysql,asterisk,sip_buddies

I got the same warning from asterisk. So what is the difference between the using sipuers and sip.conf in extconfig.conf file?

[yan]

When I using the same database to finish the CDR task. It works well.

I can check the the calling information from the database. so it means that my database works and also can connect to the asterisk, but why it can’t work for the SIP

[Ooohlin]

I had a somewhat similar problem - CDR was working perfect but not RealTime even though they hade the same database settings. I discovered that RealTime needed the correct path to the MySQL socket while CDR didn’t. The default socket path in RealTime was set to /tmp/mysql.sock but the default socket path in MySQL wasn’t the same. After making changes in res_mysql.conf everything worked just fine.

[CharlesKing]

I also had a similar problem where I had created all but the realtime extension table.
Anyone it turned out to be a permissions problem in mysql.

You need to make sure that the SIP table has SELECT and UPDATE rights.

Hope this helps someone with such a silly little problem.