# Asterisk + odbc mysql voicemail storage

## mondjef

Hi first post ever so hopefully I am not too general describing my problem.

Want to have Asterisk store my voice messages in a mysql database and can not seem to get it to work no matter what I try.  It seems to me that others on the net do not have any issues getting it to work, but for me there is something that is not working or that I have missed on the config and hoping somebody else has already been down this path.

Background:

Using Asterisk 1.6, but have tried 1.8 with no luck either.

Already have mysql database setup for cdr storage via odbc

I have setup the table for the voice messages and using the same odbc dns as that was setup for the cdr logging.

The odbcstorage and odbctable variables in voicemail.conf have been set accordingly.

In the asterisk console, 'odbc show all' shows that the odbc connection to the mysql database had been established properly.  When I leave a voicemessage it just stores it the normal voicemail directories and not in the database.

I have installed the required programs, unixODBC and myodbc.

All over the net the suggestion to configure voicemessage storage via mysql requires unixODBC-dev(or devel) be installed in order for Asterisk to compile with the necessary odbc support and create the following modules:

/usr/lib/asterisk/modules/cdr_odbc.so 

/usr/lib/asterisk/modules/res_config_odbc.so 

/usr/lib/asterisk/modules/res_odbc.so 

unixODBC-dev or unixODBC-devel is non-existant in Gentoo portage tree, but when I compiled Asterisk the three odbc related modules were created so I assume the unixODBC package that is in the portage tree installs with the devel libraries that Asterisk looks for during compiling.

From what I can tell it looks like there is no attempt even made from Asterisk to communicate to the database, almost like its ignoring the fact that I have set the odbcstorage and odbctable variables in voicemail.conf.  Other evidence of this is that when odbc storage has been set up, the unavailable and busy greetings are to be stored in the mysql table as well and if * voicemail finds one of those files in the /var/spool/asterisk/voicemail directory it will play it and then delete it.  The next time someone is prompted to leave a message it will play the standard unavailable message.  What I have found on my system is the file stored in the normal voicemail directory for a given user is played back as normal and file is not deleted as suggested which makes me think that the voicemail module is not operating in odbc mode.

Is there anything that I could be missing hear, is there a way to see what if anything that Asterisk is sending to the mysql database via odbc driver?  I am totally at odds on this one.

I would find is hard to believe that nobody else has got this working on a Gentoo machine.

I can post the necessary .conf files if anyone finds it useful.

Thanks in advance for any help.

----------

## mondjef

here are the relevant config files to help determine the issue:

/etc/asterisk/res_odbc.conf

```

;;; odbc setup file 

; ENV is a global set of environmental variables that will get set.

; Note that all environmental variables can be seen by all connections,

; so you can't have different values for different connections.

[ENV]

;INFORMIXSERVER => my_special_database

;INFORMIXDIR => /opt/informix

;ORACLE_HOME => /home/oracle

; All other sections are arbitrary names for database connections.

[asterisk]

enabled => yes

dsn => MySQL-asterisk

username => username

password => password

pre-connect => yes

;

; What should we execute to ensure that our connection is still alive?  The

; statement should return a non-zero value in the first field of its first

; record.  The default is "select 1".

sanitysql => select 1

[mysql2]

enabled => no

dsn => MySQL-asterisk

username => username

password => password

pre-connect => yes

;

; On some databases, the connection times out and a reconnection will be

; necessary.  This setting configures the amount of time a connection

; may sit idle (in seconds) before a reconnection will be attempted.

;idlecheck => 3600

; Certain servers, such as MS SQL Server and Sybase use the TDS protocol, which

; limits the number of active queries per connection to 1.  By telling res_odbc

; not to share connections, Asterisk can be made to work with these servers.

[sqlserver]

enabled => no

dsn => mickeysoft

share_connections => no

limit => 5

username => oscar

password => thegrouch

pre-connect => yes

sanitysql => select count(*) from systables

; forcecommit => no            ; Default to committing uncommitted transactions?

                               ; Note:  this is NOT the autocommit flag; this

                               ; determines the end result of transactions which

                               ; are not explicitly committed or rolled back.  By

                               ; default, such transactions are rolled back if the

                               ; call ends without an explicit commit.

; isolation => read_committed  ; Isolation level; supported levels are:

                               ; read_uncommitted, read_committed, repeatable_read,

                               ; serializable.  Note that not all databases support

                               ; all isolation levels (e.g. Postgres only supports

                               ; repeatable_read and serializable).  See database

                               ; documentation for further information.

;

; Many databases have a default of '\' to escape special characters.  MS SQL

; Server does not.

backslash_is_escape => no

```

/etc/asterisk/voicemail.conf

```
;

; Voicemail Configuration

;

;

; NOTE: Asterisk has to edit this file to change a user's password.  This does

; not currently work with the "#include <file>" directive for Asterisk

; configuration files, nor when using realtime static configuration.

; Do not use them with this configuration file.

;

[general]

; Formats for writing Voicemail.  Note that when using IMAP storage for

; voicemail, only the first format specified will be used.

;format=g723sf|wav49|wav

format=wav49

;

; WARNING:

; If you change the list of formats that you record voicemail in

; when you have mailboxes that contain messages, you _MUST_ absolutely

; manually go through those mailboxes and convert/delete/add the

; the message files so that they appear to have been stored using

; your new format list. If you don't do this, very unpleasant

; things may happen to your users while they are retrieving and

; manipulating their voicemail.

;

; In other words: don't change the format list on a production system

; unless you are _VERY_  sure that you know what you are doing and are

; prepared for the consequences.

;

; Who the e-mail notification should appear to come from

serveremail=JeffTel

;serveremail=asterisk@linux-support.net

; Should the email contain the voicemail as an attachment

attach=yes

; Maximum number of messages per folder.  If not specified, a default value

; (100) is used.  Maximum value for this option is 9999.

;maxmsg=100

; Maximum length of a voicemail message in seconds

maxsecs=480

; Minimum length of a voicemail message in seconds for the message to be kept

; The default is no minimum.

minsecs=3

; Maximum length of greetings in seconds

maxgreet=60

; How many milliseconds to skip forward/back when rew/ff in message playback

skipms=3000

; How many seconds of silence before we end the recording

maxsilence=2

; Silence threshold (what we consider silence: the lower, the more sensitive)

silencethreshold=128

; Max number of failed login attempts

maxlogins=3

;

; Move heard messages to the 'Old' folder automagically.  Defaults to on.

;moveheard=yes

;

; Forward an urgent message as an urgent message.  Defaults to no so

; sender can set the urgency on the envelope of the forwarded message.

;forward_urgent_auto=no

;

; User context is where entries from users.conf are registered.  The

; default value is 'default'

;

;userscontext=default

;

; If you need to have an external program, i.e. /usr/bin/myapp

; called when a voicemail is left, delivered, or your voicemailbox 

; is checked, uncomment this.

;externnotify=/usr/bin/myapp

; If you would also like to enable SMDI notification then set smdienable to yes.

; You will also need to make sure smdiport is set to a valid port as specified in

; smdi.conf.

;smdienable=yes

;smdiport=/dev/ttyS0

; If you need to have an external program, i.e. /usr/bin/myapp

; called when a voicemail password is changed, uncomment this. The

; arguments passed to the application are: <context> <mailbox> <newpassword>

; Note: If this is set, the password will NOT be changed in voicemail.conf

; If you would like to also change the password in voicemail.conf, use

; the externpassnotify option below instead.

;externpass=/usr/bin/myapp

;externpassnotify=/usr/bin/myapp

; If you need to have an external program, i.e. /usr/bin/myapp

; called when a user changes her voicemail password, uncomment this:

;externpasscheck=/usr/bin/myapp

; Arguments for this script are:

; mailbox context oldpass newpass

; For the directory, you can override the intro file if you want

;directoryintro=dir-intro

; The character set for voicemail messages can be specified here

;charset=ISO-8859-1

; The ADSI feature descriptor number to download to 

;adsifdn=0000000F

; The ADSI security lock code

;adsisec=9BDBF7AC

; The ADSI voicemail application version number.

;adsiver=1

; Skip the "[PBX]:" string from the message title

;pbxskip=yes

; Change the From: string

;fromstring=The Asterisk PBX

; Permit finding entries for forward/compose from the directory

;usedirectory=yes

; Voicemail can be stored in a database using the ODBC driver.

; The value of odbcstorage is the database connection configured

; in res_odbc.conf.

odbcstorage=asterisk

; The default table for ODBC voicemail storage is voicemessages.

odbctable=voicemessages

;

; Change the from, body and/or subject, variables:

;     VM_NAME, VM_DUR, VM_MSGNUM, VM_MAILBOX, VM_CALLERID, VM_CIDNUM,

;     VM_CIDNAME, VM_DATE

; Additionally, on forwarded messages, you have the variables:

;     ORIG_VM_CALLERID, ORIG_VM_CIDNUM, ORIG_VM_CIDNAME, ORIG_VM_DATE

; You can select between two variables by using dialplan functions, e.g.

;     ${IF(${ISNULL(${ORIG_VM_DATE})}?${VM_DATE}:${ORIG_VM_DATE})}

;

; Note: The emailbody config row can only be up to 512 characters due to a

;       limitation in the Asterisk configuration subsystem.

;emailsubject=[PBX]: New message ${VM_MSGNUM} in mailbox ${VM_MAILBOX}

; The following definition is very close to the default, but the default shows

; just the CIDNAME, if it is not null, otherwise just the CIDNUM, or "an unknown

; caller", if they are both null.

;emailbody=Dear ${VM_NAME}:\n\n\tjust wanted to let you know you were just left a ${VM_DUR} long message (number ${VM_MSGNUM})\nin mailbox ${VM_MAILBOX} from ${VM_CALLERID}, on ${VM_DATE}, so you might\nwant to check it when you get a chance.  Thanks!\n\n\t\t\t\t--Asterisk\n

;

; Note: ${IF()} strips spacing at the beginning and end of its true and false

; values, so a newline cannot be placed at either location.  The word 'so' is

; therefore duplicated, in order for the newline to be interpreted correctly.

;emailbody=Dear ${VM_NAME}:\n\n\tjust wanted to let you know you were just ${IF($["${VM_CIDNUM}" = "${ORIG_VM_CIDNUM}"]?left:forwarded)} a ${VM_DUR} long message (number ${VM_MSGNUM})\nin mailbox ${VM_MAILBOX} from ${VM_CALLERID}, on ${VM_DATE},\n${IF($["${VM_CIDNUM}" = "${ORIG_VM_CIDNUM}"]?so:(originally sent by ${ORIG_VM_CALLERID} on ${ORIG_VM_DATE})\nso)} you might want to check it when you get a chance.  Thanks!\n\n\t\t\t\t--Asterisk\n

;

; You can also change the Pager From: string, the pager body and/or subject.

; The above defined variables also can be used here

pagerfromstring=JeffTel PBX

pagersubject=[PBX]: New message ${VM_MSGNUM} in mailbox ${VM_MAILBOX}

pagerbody=Dear ${VM_NAME}:\n\n\tJust wanted to let you know you were just left a ${VM_DUR} long message (number $

;

; Set the date format on outgoing mails. Valid arguments can be found on the

; strftime(3) man page

;

; Default

emaildateformat=%A, %B %d, %Y at %r

; 24h date format

;emaildateformat=%A, %d %B %Y at %H:%M:%S

;

; You can override the default program to send e-mail if you wish, too

;

;mailcmd=/usr/sbin/sendmail -t

;

;pollmailboxes=no    ;   If mailboxes are changed anywhere outside of app_voicemail,

;                    ; then this option must be enabled for MWI to work.  This

;                    ; enables polling mailboxes for changes.  Normally, it will

;                    ; expect that changes are only made when someone called in

;                    ; to one of the voicemail applications.

;                    ;   Examples of situations that would require this option are 

;                    ; web interfaces to voicemail or an email client in the case 

;                    ; of using IMAP storage.

;

;pollfreq=30         ;   If the "pollmailboxes" option is enabled, this option

;                    ; sets the polling frequency.  The default is once every

;                    ; 30 seconds.

; If using IMAP storage, specify whether voicemail greetings should be stored 

; via IMAP. If no, then greetings are stored as if IMAP storage were not enabled

;imapgreetings=no

; If imapgreetings=yes, then specify which folder to store your greetings in. If

; you do not specify a folder, then INBOX will be used

;greetingsfolder=INBOX

; Some IMAP server implementations store folders under INBOX instead of 

; using a top level folder (ex. INBOX/Friends).  In this case, user

; imapparentfolder to set the parent folder. For example, Cyrus IMAP does

; NOT use INBOX as the parent. Default is to have no parent folder set.

;imapparentfolder=INBOX

; 

; 

; 

; Each mailbox is listed in the form <mailbox>=<password>,<name>,<email>,<pager_email>,<options>

; if the e-mail is specified, a message will be sent when a message is

; received, to the given mailbox. If pager is specified, a message will be

; sent there as well. If the password is prefixed by '-', then it is

; considered to be unchangeable.

;

; Advanced options example is extension 4069

; NOTE: All options can be expressed globally in the general section, and

; overridden in the per-mailbox settings, unless listed otherwise.

; 

 tz=eastern       ; Timezone from zonemessages below. Irrelevant if envelope=no.

 attach=no       ; Attach the voicemail to the notification email *NOT* the pager email

 attachfmt=wav49   ; Which format to attach to the email.  Normally this is the

         ; first format specified in the format parameter above, but this

         ; option lets you customize the format sent to particular mailboxes.

         ; Useful if Windows users want wav49, but Linux users want gsm.

         ; [per-mailbox only]

 saycid=no       ; Say the caller id information before the message. If not described, 

         ;     or set to no, it will be in the envelope

; cidinternalcontexts=intern   ; Internal Context for Name Playback instead of 

         ; extension digits when saying caller id.

 sayduration=no    ; Turn on/off the duration information before the message. [ON by default]

; saydurationm=2        ; Specify the minimum duration to say. Default is 2 minutes

; dialout=fromvm ; Context to dial out from [option 4 from mailbox's advanced menu]. 

                 ; If not specified, option 4 will not be listed and dialing out 

                 ; from within VoiceMailMain() will not be permitted.

sendvoicemail=yes ; Allow the user to compose and send a voicemail while inside 

                  ; VoiceMailMain() [option 5 from mailbox's advanced menu].

                  ; If set to 'no', option 5 will not be listed.

; searchcontexts=yes   ; Current default behavior is to search only the default context

         ; if one is not specified.  The older behavior was to search all contexts.

         ; This option restores the old behavior [DEFAULT=no]

         ; Note: If you have this option enabled, then you will be required to have

         ; unique mailbox names across all contexts. Otherwise, an ambiguity is created

         ; since it is impossible to know which mailbox to retrieve when one is requested.

; callback=fromvm    ; Context to call back from  

         ;     if not listed, calling the sender back will not be permitted

; exitcontext=fromvm    ; Context to go to on user exit such as * or 0

                        ;     The default is the current context.

 review=yes       ; Allow sender to review/rerecord their message before saving it [OFF by default

; operator=yes      ; Allow sender to hit 0 before/after/during leaving a voicemail to

                    ; reach an operator.  This option REQUIRES an 'o' extension in the

                    ; same context (or in exitcontext, if set), as that is where the

                    ; 0 key will send you.  [OFF by default]

; envelope=no       ; Turn on/off envelope playback before message playback. [ON by default] 

         ;     This does NOT affect option 3,3 from the advanced options menu

delete=yes      ; After notification, the voicemail is deleted from the server. [per-mailbox only]

         ;     This is intended for use with users who wish to receive their 

         ;     voicemail ONLY by email. Note:  "deletevoicemail" is provided as an

         ;     equivalent option for Realtime configuration.

; volgain=0.0      ; Emails bearing the voicemail may arrive in a volume too

         ;     quiet to be heard.  This parameter allows you to specify how

         ;     much gain to add to the message when sending a voicemail.

         ;     NOTE: sox must be installed for this option to work.

; nextaftercmd=yes   ; Skips to the next message after hitting 7 or 9 to delete/save current message.

         ;     [global option only at this time] 

; forcename=yes      ; Forces a new user to record their name.  A new user is

         ;     determined by the password being the same as

         ;     the mailbox number.  The default is "no".

; forcegreetings=no   ; This is the same as forcename, except for recording

         ;     greetings.  The default is "no".

 hidefromdir=yes   ; Hide this mailbox from the directory produced by app_directory

         ;     The default is "no".

; tempgreetwarn=yes   ; Remind the user that their temporary greeting is set

;messagewrap=no   ; Enable next/last message to wrap around to

         ; first (from last) and last (from first) message

         ; The default is "no".

; minpassword=0 ; Enforce minimum password length

; vm-password=custom_sound

         ;     Customize which sound file is used instead of the default

         ;     prompt that says: "password"

; vm-newpassword=custom_sound

         ;     Customize which sound file is used instead of the default

         ;     prompt that says: "Please enter your new password followed by

         ;     the pound key."

; vm-passchanged=custom_sound

         ;     Customize which sound file is used instead of the default

         ;     prompt that says: "Your password has been changed."

; vm-reenterpassword=custom_sound

         ;     Customize which sound file is used instead of the default

         ;     prompt that says: "Please re-enter your password followed by

         ;     the pound key"

; vm-mismatch=custom_sound

         ;     Customize which sound file is used instead of the default

         ;     prompt that says: "The passwords you entered and re-entered

         ;     did not match."

; vm-invalid-password=custom_sound

         ;     Customize which sound file is used instead of the default

         ;     prompt that says: ...

; vm-pls-try-again=custom_sound

                        ; Customize which sound file is used instead of the

                        ; default prompt that says "Please try again."

; listen-control-forward-key=#   ; Customize the key that fast-forwards message playback

; listen-control-reverse-key=*   ; Customize the key that rewinds message playback

; listen-control-pause-key=0   ; Customize the key that pauses/unpauses message playback

; listen-control-restart-key=2   ; Customize the key that restarts message playback

; listen-control-stop-key=13456789   ; Customize the keys that interrupt message playback, probably all keys not set above

; Maximum number of messages allowed in the 'Deleted' folder. If set to 0

; or no then no deleted messages will be moved. If non-zero (max 9999) then up

; to this number of messages will be automagically saved when they are

; 'deleted' on a FIFO basis.

; defaults to being off

 backupdeleted=100

[zonemessages]

; Users may be located in different timezones, or may have different 

; message announcements for their introductory message when they enter 

; the voicemail system. Set the message and the timezone each user 

; hears here. Set the user into one of these zones with the tz= attribute 

; in the options field of the mailbox. Of course, language substitution 

; still applies here so you may have several directory trees that have 

; alternate language choices. 

; 

; Look in /usr/share/zoneinfo/ for names of timezones. 

; Look at the manual page for strftime for a quick tutorial on how the 

; variable substitution is done on the values below. 

; 

; Supported values: 

; 'filename'    filename of a soundfile (single ticks around the filename

;               required)

; ${VAR}        variable substitution 

; A or a        Day of week (Saturday, Sunday, ...) 

; B or b or h   Month name (January, February, ...) 

; d or e        numeric day of month (first, second, ..., thirty-first) 

; Y             Year 

; I or l        Hour, 12 hour clock 

; H             Hour, 24 hour clock (single digit hours preceded by "oh") 

; k             Hour, 24 hour clock (single digit hours NOT preceded by "oh") 

; M             Minute, with 00 pronounced as "o'clock" 

; N             Minute, with 00 pronounced as "hundred" (US military time)

; P or p        AM or PM 

; Q             "today", "yesterday" or ABdY

;               (*note: not standard strftime value) 

; q             "" (for today), "yesterday", weekday, or ABdY

;               (*note: not standard strftime value) 

; R             24 hour time, including minute 

;

eastern=America/New_York|'vm-received' Q 'digits/at' IMp

central=America/Chicago|'vm-received' Q 'digits/at' IMp

central24=America/Chicago|'vm-received' q 'digits/at' H N 'hours'

military=Zulu|'vm-received' q 'digits/at' H N 'hours' 'phonetic/z_p'

european=Europe/Copenhagen|'vm-received' a d b 'digits/at' HM

[default]

XXXXXXXXX => 4321,Jeff and Kristy,X@gmail.com,,|tz=eastern|attach=yes|saycid=no|delete=no|hidefromdir=yes|maxmsg=100

;4200 => 9855,Mark Spencer,markster@linux-support.net,mypager@digium.com,attach=no|serveremail=myaddy@digium.com|tz=central|maxmsg=10

;4300 => 3456,Ben Rigas,ben@american-computer.net

;4310 => -5432,Sales,sales@marko.net

;4069 => 6522,Matt Brooks,matt@marko.net,,|tz=central|attach=yes|saycid=yes|dialout=fromvm|callback=fromvm|review=yes|operator=yes|envelope=yes|moveheard=yes|sayduration=yes|saydurationm=1

;4073 => 1099,Bianca Paige,bianca@biancapaige.com,,delete=1|emailsubject=You have a new voicemail.|emailbody=Click on the attachment to listen.|rip=2010-06-04

;4110 => 3443,Rob Flynn,rflynn@blueridge.net

;4235 => 1234,Jim Holmes,jim@astricon.ips,,Tz=european

```

/etc/unixODBC/odbcinst.ini

```

[myodbc-5.1]

Description=MySQL ODBC myodbc-5.1.6 Driver

Driver=/usr/lib64/libmyodbc5.so

UsageCount=2

Trace=yes

TraceFile=/tmp/mysql.log

ForceTrace=yes

```

/etc/unixODBC/odbc.ini

```

[ODBC Data Sources]

myodbc-5.1-test=MySQL ODBC myodbc-5.1.6 Driver Testing DSN

[myodbc-5.1-test]

Description=MySQL ODBC myodbc-5.1.6 Driver Testing DSN

Driver=myodbc-5.1

Socket=/var/run/mysqld/mysqld.sock

Server=localhost

User=root

Database=test

Option=3

[MySQL-asterisk]

Description=MySQL ODBC myodbc-5.1.6 Driver Asterisk database

Driver=myodbc-5.1

Socket=/var/run/mysqld/mysqld.sock

Server=localhost

UserName=username

Database=asterisk

Option=3

```

Anything else of use please let me know.Last edited by mondjef on Thu Jun 16, 2011 11:43 am; edited 1 time in total

----------

## sokhapkin

Is app_voicemail compiled with ODBC storage support?

----------

## mondjef

 *sokhapkin wrote:*   

> Is app_voicemail compiled with ODBC storage support?

 

thanks sokhpkin for the reply.

This is one question I had myself, and from what I read it seems that if Asterisk finds the odbc libraries it will compile support for it in its modules during installation.  But I also found postes that suggest some other config during make, but I just use emerge to compile and install asterisk.  I noticed that there are more use flags for the 1.8 version including an odbc flag which I thought might do the trick but I had no luck.  Would you know how to confirm if voicemail has been compiled with odbc support, I guess the fact that the two odbc config variables being present in the voicemail.conf file is not enough to assume it is?

----------

## mondjef

Nobody has this working on Gentoo?

----------

## calvinwells

If you think you have all the configuration settings correct, you might want to verify that voicemail support is compiled in. If not you can correct it easily.

#ENABLE ODBC STORAGE FOR VOICEMAIL

Make menuselect

Select option Voicemail Build Options

Enable option ODBC_STORAGE

Escape, Escape

Enter S to save your changes, and exit

make

make install

ODBC_Storage will not be selectable if you don't have all of the dependencies on the system.

You may also want to verify outside of Asterisk that you can connect to the target system and database and authenticate correctly. I usually use isql to make this confirmation.

----------

## mondjef

 *calvinwells wrote:*   

> If you think you have all the configuration settings correct, you might want to verify that voicemail support is compiled in. If not you can correct it easily.
> 
> #ENABLE ODBC STORAGE FOR VOICEMAIL
> 
> Make menuselect
> ...

 

thanks for the reply calvinwells.  I had thought about doing this, but to be honest I am not sure how to do this on a Gentoo system as I usually just install using emerge.  I know, bit of noob question, but what directory do I go to before doing make menuselect?

Also, yes I have verified that I can connect to the required database and table.

Thanks again, as I really want to get this working so that I can put the finishing touches on my system.

----------

