[Home]

Summary:ASTERISK-04668: Asterisk voicemail odbc storage
Reporter:Eric Romang / DCLUX (eromang)Labels:
Date Opened:2005-07-24 06:29:07Date Closed:2011-06-07 14:03:06
Priority:MajorRegression?No
Status:Closed/CompleteComponents:Applications/app_voicemail
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:
Description:ODBC Configuration : Version 2.2.6
--------------------

emerge dev-db/unixODBC

/etc/unixODBC/odbc.ini

[vmstorage]
Description     = Asterisk voicemail odbc storage
Driver          = MySQL
Server          = xxx.xxx.xxx.xxx
User            = asteriskodbc
Password        = passwd
Database        = database
Option          = 3

MySQL ODBC Installation : Version 3.51.11
-------------------------

emerge dev-db/myodbc

res_odbc.conf configuration :
-----------------------------

/etc/asterisk/res_odbc.conf

[voipodbc]
dsn=vmstorage
pre-connect=yes

voicemail.conf configuration :
------------------------------

/etc/asterisk/voicemail.conf

odbcstorage=voipodbc

USE asterisk HEAD asterisk-2005_06_28_0958 :
--------------------------------------------

Get the lastest HEAD version

Modification off HEAD apps/Makefile :
-------------------------------------

Uncomment : CFLAGS+=-DUSE_ODBC_STORAGE

Compile asterisk and follow the standard configuration
-------------------------------------------------------

MySQL 4.1 voicemessages table structure :
-----------------------------------------

CREATE TABLE `voicemessages` (
 `id` int(11) NOT NULL auto_increment,
 `msgnum` int(11) NOT NULL default '0',
 `dir` varchar(80) default '',
 `context` varchar(80) default '',
 `macrocontext` varchar(80) default '',
 `callerid` varchar(40) default '',
 `origtime` varchar(40) default '',
 `duration` varchar(20) default '',
 `mailboxuser` varchar(80) default '',
 `mailboxcontext` varchar(80) default '',
 `recording` longblob,
 PRIMARY KEY  (`id`),
 KEY `dir` (`dir`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

Authorize the ODBC user to insert,select,update,delete into the voicemessages
tables

Debugging :
-----------

start asterisk as asterisk:asterisk

login into asteriks : asterisk -rvvvvvvvvvvvvvvvvvv

check if the ODBC is running : odbc show

Name: voipodbc
DSN: vmstorage
Connected: yes

Register your phone on the register and create a voicemessage

Recording the message
x=0, open writing:  /var/spool/asterisk/voicemail/mailbox/110/INBOX/msg0000
format: wav49, 0x81b7f48
- User ended message by pressing #
-- Playing 'auth-thankyou' (language 'en')
-- Playing 'vm-review' (language 'en')
-- Saving message as is
-- Playing 'vm-msgsaved' (language 'en')

mysql # tail -f /var/log/mysql/mysql.log | grep voicemessages

050722 10:35:50     167 Query       SELECT COUNT(*) FROM voicemessages WHERE
dir='/var/spool/asterisk/voicemail/mailbox/110/INBOX' AND msgnum='0'
167 Query       SELECT COUNT(*) FROM voicemessages WHERE
dir='/var/spool/asterisk/voicemail/mailbox/110/INBOX' AND msgnum='1'
050722 10:36:05     167 Query       DELETE FROM voicemessages WHERE
dir='/var/spool/asterisk/voicemail/mailbox/110/INBOX/msg0001' AND msgnum='-1'
167 Query       INSERT INTO voicemessages
(dir,msgnum,recording,context,macrocontext,callerid,origtime,duration) VALUES
('/var/spool/asterisk/voicemail/mailbox/110/INBOX/msg0001','-1','data of the
voicemessage','','','','','')
167 Query       DELETE FROM voicemessages WHERE
dir='/var/spool/asterisk/voicemail/mailbox/110/INBOX' AND msgnum='1'

So first thing we see :

The insert query is not complete ->
context,macrocontext,callerid,origtime,duration are empty

Yet we test with asterisk running as root user

Juggie of asterisk-dev freenodes tell me that the troubles could come from
here

308 Query       INSERT INTO voicemessages
(dir,msgnum,recording,context,macrocontext,callerid,origtime,duration) VALUES
('/var/spool/asterisk/voicemail/mailbox/110/INBOX/msg0001','-1','data of the
voicemessage','','','','','')

We still don't have the context,macrocontext,callerid,origtime,duration
Comments:By: Donny Kavanagh (donnyk) 2005-07-24 15:53:07

You have mailboxuser & mailboxcontext in your table stucture, but you dont seem to be running the patch from 4403?  This problally woudnt cause your issue but just as a note.

Since this works for me in head, i will post my working configuration.

/etc/odbc.ini
[MySQL-asterisk]
Description = MySQL Asterisk database
Trace       = Off
TraceFile   = stderr
Driver      = MySQL
SERVER      = 192.168.45.106
USER        = root
PASSWORD    = toor
PORT        = 3306
DATABASE    = asterisk

in voicemail.conf
odbcstorage=asterisk

in res_odbc.conf
[asterisk]
dsn => MySQL-asterisk
username => root
password => toor
pre-connect => yes

my installed versions are as follows.
MyODBC-2.50.39-19.1
unixODBC-2.2.9-1
unixODBC-devel-2.2.9-1

My initial reaction to this is that it is unixODBC related as this seems to be working fine for me.  If you have any c knowledge, you may try adding in some extra output into app_voicemail to see if you can debug this any further.  I can confirm that head+odbc storage does work for myself, and at least two other people that i know of, so it is not broken.

Try upgrading your unixODBC to version 2.2.9 or later (perhaps 2.2.6 is buggy?)  North/qwell if you see this bug, could you ping in with the version you are running.


By: Donny Kavanagh (donnyk) 2005-07-24 15:58:14

I also just noticed, what is that id field, that isnt in the database structure.  Please see doc/README.odbcstorage.  Try without that field, as it could also be responsible for the issues.  Since its flagged as primary key and is not expected within the app_voicemail code.


By: Kevin P. Fleming (kpfleming) 2005-07-25 13:23:41

Can you actually tell us what the problem is? Nowhere in your bug description or summary are you showing us any error messages or problems.

The fact that some of the fields in the INSERT statement are empty may not be a problem, if they are later updated using an UPDATE statement.

Please tell us what is actually wrong, otherwise we have no way to help you.
By: Michael Jerris (mikej) 2005-07-31 18:51:26

We need feedback in order to move forward onthis bug.  Can you re-produce this issue if fully set up acording to the documentation as described in bugnotes below.  I am suspending this bug.  Please re-open if you are able to reproduce in light of the comments and are able to respond to the comments.  Thanks.