Summary:ASTERISK-02570: res_config_mysql - realtime driver
Reporter:drmac (drmac)Labels:
Date Opened:2004-10-08 17:56:31Date Closed:2004-12-03 16:45:29.000-0600
Versions:Frequency of
Environment:Attachments:( 0) res_config_mysql_makefile.patch.txt
( 1) res_config_mysql.c
( 2) res_config_mysql.c
( 3) res_mysql.conf.sample
( 4) sip_schema_mysql.txt
Description:Haven't seen much on the -dev list for realtime config, let alone realtime config with mysql. If no one else has started working on this, I would like to take the lead. res_config_odbc seems simple enough and I figure it would take a few days to a week to crunch out a working res_config_mysql based off the odbc one.



I can be reached off-list at mboehm@cytelcom.com
Comments:By: Mark Spencer (markster) 2004-10-09 10:06:33

Don't forget to submit a disclaimer!  It should be pretty easy to do, but I recommend you wait just until Monday or so for the API to settle down.

By: drmac (drmac) 2004-10-09 10:18:23

Is a disclaimer needed for each patch submitted? I faxed one a few weeks ago for another patch I submitted.

By: Brian West (bkw918) 2004-10-09 11:05:17

Modify the disclaimer to say "Everything I put on bugs.digium.com under name drmac is disclaimed"  or osmething like that.... Thats how I did it.

By: Mark Spencer (markster) 2004-10-09 11:11:55

A new disclaimer or modified one should not be required.  It just helps if you mention that you have one when you place the bug report so we know not to ask you about it :)

By: drmac (drmac) 2004-10-09 14:22:08

OK. There is one on file that should cover myself as well as our employees. Listed under company name 'Cypress Telecommunications' signed by our president.

By: drmac (drmac) 2004-10-11 15:19:34

I was amazed at how fast I churned this out. Works great on my test server. Need others to test. Works with a fresh copy of app_voicemail and chan_sip (sorry I don't use any IAX stuff).

I've also uploaded the mysql schema for the sip table I use. It looks huge but according to README.extconfig, each possible option needs its own column. I've set certain non-required columns to be able to be NULL. This seems to keep the returned ast_variable list to only contain those columns with info.

The ast_load_realtime and ast_update_realtime both work with my tests of app_voicemail by changing my password.

Quite frankly, I don't understand how to store a *.conf using the realtime_static way so I haven't tested that.

I've compiled it against MySQL 4.1.5-gamma client libraries and I connect to a 3.23.50 server.

- Add support for prepared statements.
  I saw this in the ODBC, but MySQL servers before 4.1 don't support them.
  Will need to add some server version checking. mysql_get_server_version()
  is not defined for servers before 4.1 either. Gotta do it the hard way.

- Add support for SSL connections. I don't think I have to add anything
  to support SSL conns. From what I've read, mysql_real_connect() will
  auto-create the SSL connection if the server supports it. Ours doesn't
  so I haven't tested it.

- Add transaction support. On the off chance that I am updating my voicemail
  password while someone else is trying to access my VM with my old password.
  Could someone provide me of some situations where you would really need
  this kind of protection?

Will try and update app_directory to use RealTime later today.


By: drmac (drmac) 2004-10-11 15:20:20

BTW, I just called digium and the guy on the phone verified reciept of my disclaimer.

By: Brian West (bkw918) 2004-10-11 22:42:12


By: apignard (apignard) 2004-10-11 22:59:06

Seems work fine with IAX and SIP.

I have one segfault after one hour, but i'm not sure it's coming from this patch. When i backtrace i have this :

ASTERISK-3  0x40ca616f in build_peer (name=0x4835 <Address 0x4835 out of bounds>, v=0x0) at chan_sip.c:8162

i have only friend configured and all peer are in sip.conf ...

By: drmac (drmac) 2004-10-12 09:36:58

Address out of bounds errors are ususally associated with reading past the length of a string. I'm trying to see if the error is associated with my driver....<reading code>....

Line 8162 is this:  if (!strcasecmp(peer->name, name))

'peer->name' comes from the list of previously created peers, and 'name' comes from what was passed to build_peer.

realtime_peer calls build_peer, passing it the name of the registering sip object and a var struct retreived from ast_load_realtime.

The ast_variable struct allows for a variable name to be any length. Can't really see anything else I can add in my code between lines 126 - 143 to make the string copies safer given unlimited length. If anyone has any suggestions, please email me off board. My address is in Additional Information.

Glad to hear its working in any case.

By: Brian West (bkw918) 2004-10-20 20:17:10

1. res_mysql.conf (res_config_odbc.conf has been depricated)
2. Need to have the driver loaded and registered even if the connection to the database fails otherwise you get 1 chance.
3. needs to be able to unload.
4. better reload.
5. check the doc/CODING-GUIDELINES


By: drmac (drmac) 2004-10-21 15:13:46

> 1. res_mysql.conf (res_config_odbc.conf has been depricated)

 This is user-configurable by changing the #define. However, I have changed the default to res_mysql.conf

>2. Need to have the driver loaded and registered even
>   if the connection to the database fails otherwise you get 1 chance.

  Fixed. If database fails to connect, subsequent queries will attemt re-connect as well as CLI "realtime mysql status".

> 3. needs to be able to unload.

   I noticed that the res_config_odbc didn't allow this, so I didn't code it either. You can now unload/load the module.

> 4. better reload.

   Didn't have reload. Will add one; seems to be a problem with my reload issuing a re-parsing the MOH .conf file...huh? exactly..but that is what happens right now.

> 5. check the doc/CODING-GUIDELINES

   Hmm. Thought I did. Any paticular code segment that's not following guidelines?

Standby for res_config_mysql.so v1.2


By: drmac (drmac) 2004-10-21 17:31:11

Uploaded v1.2

If someone could remove the file that was uploaded on 10-11-04, that might clear any "what version do I have?" problems. The makefile patch can stay.

By: drmac (drmac) 2004-10-21 17:32:35

Could someone take a look at my usecount(), and reload() functions? I'm not convinced that my ast_mutex stuff will work. :)

By: Russell Bryant (russell) 2004-11-07 20:12:10.000-0600

any updates here?  It has been a couple of weeks ...

-- Housekeeping

By: drmac (drmac) 2004-11-08 09:43:08.000-0600

I haven't recieved any bug reports since v1.2 was uploaded...so there is no update from me. Works great with sip, voicemail and directory.

By: ruok (ruok) 2004-11-17 12:07:49.000-0600

Sorry, closest open bug I could find, so figured it was related. I will open new bug.

By: Mark Spencer (markster) 2004-12-01 16:14:03.000-0600

Do you want to update this one to support the new multi realtime so we can get it in?

By: drmac (drmac) 2004-12-01 20:19:50.000-0600

Are there any examples of how multi-realtime is to be called?

I've got CVS-HEAD-12/01/04-19:08:00 and I did a "grep -ir multi *" and got no samples of doing multi_realtime.

If I can get one, I would be happy to update the mysql driver.

By: drmac (drmac) 2004-12-01 20:26:51.000-0600

oh good god. why do I always post before looking...and now here i am wasting 2 more records in a database somewhere..geez..i'll get back to you after I've read thru pbx_realtime.c

By: drmac (drmac) 2004-12-02 12:07:54.000-0600

Just uploaded version 1.4 which also includes version 1.3 changes:

v1.4 - (12-02-04) - Added realtime_multi_mysql function
                     This function will return an ast_config with categories,
                     unlike standard realtime_mysql which only returns
                     a linked list of ast_variables

v1.3 - (12-01-04) - Added support other operators
                    Ex: =, !=, LIKE, NOT LIKE, RLIKE, etc...

By: Mark Spencer (markster) 2004-12-02 20:44:09.000-0600

Added to asterisk-addons, thanks!

By: Russell Bryant (russell) 2004-12-02 20:50:35.000-0600

not in 1.0

By: drmac (drmac) 2004-12-03 08:43:01.000-0600

I felt that I needed to post a res_mysql.conf.sample so it can be placed into asterisk-addons/configs/

Also, where do I submit updates for this driver? Someone has suggested to me that I add in multiple database/multiple server support and I want this to be a great help to the Asterisk/MySQL user base.

By: Mark Spencer (markster) 2004-12-03 09:36:14.000-0600

I added the sample configuration file.  Feel free to open a new bug when you have an update you want to make.  Thank you very much for your contribution!

By: Russell Bryant (russell) 2004-12-03 16:45:29.000-0600

cool stuff.