asterisk users - Jan 2007 - Realtime Voicemail Password Change Not Working

Hi All,

I'm using asterisk 1.2.9.1 and mysql 3.23, asterisk add-ons 1.2.3.
All seems to work normally with realtime voicemail, reads vmbox
parameters from the db fine.  When I try to change the password,
asterisk operates normally, "enter new password" ok, "re-enter
new
password" ok, "password has been changed"

There are no entries in the mysql.log setting the new password in the
database.  How can I isolate between asterisk, realtime driver, and
mysql?

Thanks.

JR

-- 
JR Richardson
Engineering for the Masses

> On 1/17/07, JR Richardson <jmr.richardson@gmail.com> wrote:
> > > I'm using asterisk 1.2.9.1 and mysql 3.23, asterisk add-ons
1.2.3.
> > > All seems to work normally with realtime voicemail, reads vmbox
> > > parameters from the db fine.  When I try to change the password,
> > > asterisk operates normally, "enter new password" ok,
"re-enter new
> > > password" ok, "password has been changed"
> > >
> > > There are no entries in the mysql.log setting the new password in
the
> > > database.  How can I isolate between asterisk, realtime driver,
and
> > > mysql?
> >
> > I updated to asterisk 1.2.14 and add-ons 1.2.5 with no luck.  I still
don't
> > see any update statement in the mysql.log when I change a password.  I
built
> > a vmbox in the voicemail.conf file and can change that password just
fine.
> > Any suggestions?
>
> JR,
>
> I'm just pulling things out of the air here, but if realtime voicemail
> works like realtime users/peers, loading everything into memory from
> MySQL, then there would need to be some type or prune command to force
> the re-read of the voicemail table, this is asuming you change the
> password via MySQL and not on the handset. Maybe something like DBput
> would work to update astdb as well. Again just throwing out ideas...
>
> It sounds like you are using the handset to update the password. Is
> this correct?
Yes, I'm using the handset to change the vm password, throught the vm
advanced features.  I can directly change the db as expected, just not
through the vm application.
> Not sure about how you log in MySQL but using ODBC, in your
> odbcinst.ini or a similar file for Mysql, which keeps the settings for
> your db driver etc, you should be able to turn on logging. I can in
> odbcinst and it creates logs.
>
> The problem you have seems more like a permission problem however, the
> user you're using to log into the DB doesn't seem to have the
> permission to write to the table which keeps the user information OR
> the voicemail database itself. This problem becomes a bit trickier
> when your vm user table is actually a view of tables that hold
> subscriber/user information and is compounded by the fact if
> voicemails are being stored in a different db than where the sip/iax
> user information is being stored to derive sipusers and sippeers
> family values as then the user that asterisk is using to connect to
> the voicemail db will also need write permission in the db that stores
> user information.
I use the same database for the sip, iax, exten and vm, different
tables.  When a sip device registers, asterisk writes to the database
with updates to the sip table ipaddress, port and regseconds, so I
don't think there is a write permissions issue from asterisk res_mysql
to the mysql database.  I thought of that also and changed the user to
full access, but that didn't help.  Mysql logs all database
transactions in the /var/log/mysql.log file.  I see all the query
selects from the voicemail table and i see all the query updates to
the sip table, but never see any query updates for the vmpasswd to the
voicemail table.  I would assume there would at least be errors if
there was a permissions problem.  I don't see where asterisk is trying
to update the vmpassword through the realtime driver.

How is your voicemail.conf file setup?

Thanks.

JR
-- 
JR Richardson
Engineering for the Masses

> Interesting, well if you're seeing the other selects in the mysql.log
> then this update not showing up is bizarre. It would also mean that
> permissions are irrelevant if doesn't even attempt to change the
> password, as you'd rightly pointed out as well. I just tested it again
> and this is what I see in the odbc sql.log
> 
> SQL = [UPDATE vmusers SET password=? WHERE uniqueid=?][length = 46
> (SQL_NTS)]
> 
> So it definately spits out something but my setup is considerably
> different to yours. I am using ODBC -> FreeTDS -> MS SQL Server for
> starters. There's nothing out of the oridinary in my voicemail.conf.
> What I do remember is some conversation sometime about the file
> locking fix that was put in or was being talked about regards to
> people using static files and multiple people trying to change their
> passwords. Just checking if you've compiled (*) clean without any mods
> to the code etc. I mean I have made mods to app_voicemail.c but
> nothing that affects passwords. Just for giggles, have you tried doing
> "realtime update voicemail mailbox 1234 password 2345" ? I know
you
> said that your db updates for regsecs, ip address etc is working but
> try specifically writing to your voicemail table and see if you are
> able to manually update the password. At least that way you can just
> focus on seeing why the password code is not being triggered in the
> (*) code when using MySQL. Sorry I cna't think of anything else to
> suggest at the moment.
I was able to update the password through the dialplan with this:
exten => 5555,1,MYSQL(Connect connid 127.0.0.1 pbx pbx pbxdb)
exten => 5555,2,MYSQL(Query resultid ${connid} UPDATE\ voicemail\ SET\
password=1111\ where\ mailbox=52007)
exten => 5555,3,MYSQL(Clear ${resultid})
exten => 5555,4,MYSQL(Disconnect ${connid})
exten => 5555,5,Hangup

Finaly I got an update statement in the mysql log:
12 Query       UPDATE voicemail SET password=1111 where mailbox=52007

So these results suggest that mysql, voicemail table, and the res_mysql
adddon are working fine.  It suggests that app_voicemail is not passing the
update statement to the res_mysql driver.

This was a clean install, nothing out of the ordinary.

Thanks.

JR

>
> I was able to update the password through the dialplan with this:
> exten => 5555,1,MYSQL(Connect connid 127.0.0.1 pbx pbx pbxdb)
> exten => 5555,2,MYSQL(Query resultid ${connid} UPDATE\ voicemail\ SET\
> password=1111\ where\ mailbox=52007)
> exten => 5555,3,MYSQL(Clear ${resultid})
> exten => 5555,4,MYSQL(Disconnect ${connid})
> exten => 5555,5,Hangup
>
> Finaly I got an update statement in the mysql log:
> 12 Query       UPDATE voicemail SET password=1111 where mailbox=52007
>
> So these results suggest that mysql, voicemail table, and the res_mysql
> adddon are working fine.  It suggests that app_voicemail is not passing 
> the
> update statement to the res_mysql driver.
>
> This was a clean install, nothing out of the ordinary.
I would second the other posters suggestion: use Realtime update (show 
application realtime update) since it uses the actual realtime setup. The 
MySQL command shown above uses a new connection that you specify so is not 
such a good test.

Cameron