thr3ads.net - asterisk users - [asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help [Apr 2007]

If this information is useful, please help other people find it:
Share via:

Bruce McAlister

2007-Apr-30 16:05 UTC

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

Hi All,

I have an issue with the ODBC voicemail storage option with asterisk. All
appears to work fine, however, I get several sql execute warnings. I was
wondering if anyone out there could help me get to the bottom of what is
causing this and how I could possibly go about rectifying it.

The warning message we are getting is as follows:

WARNING[30115]: app_voicemail.c:1280 delete_file: SQL Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

This warning occurs whenever a user leaves a message for an extension. It
also occurs when someone dials in to listen to their messages when they hang
up.

These messages do actually exist within the database, and asterisk does
extract them from the database when playing back or recording messages.

Here is an example when someone leaves a message for someone:

----------------------------------------------------------------------------
---------------------------------------------------------------------------

-- Executing [170@base-out:1] AGI("SIP/bruce.mcalister-09051118",
"agi://10.7.0.136:4573?app=getvoicemailexten") in new stack
-- AGI Script agi://10.7.0.136:4573?app=getvoicemailexten completed,
returning 0
-- Executing [170@base-out:2] NoOp("SIP/bruce.mcalister-09051118",
"Caller
VoiceMail Extension = 3031") in new stack
-- Executing [170@base-out:3]
VoiceMail("SIP/bruce.mcalister-09051118",
"3031@users") in new stack
-- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3031/temp' (language
'en')
-- <SIP/bruce.mcalister-09051118> Playing 'vm-intro' (language
'en')
-- <SIP/bruce.mcalister-09051118> Playing 'beep' (language
'en')
-- Recording the message
-- x=0, open writing:
/usr/local/asterisk/var/spool/voicemail/users/3031/tmp/hGkNG0 format: wav49,
0x90539c8
-- User ended message by pressing #
-- <SIP/bruce.mcalister-09051118> Playing 'auth-thankyou'
(language 'en')
[Apr 30 23:56:03] WARNING[30123]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

== Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3031/INBOX/msg0002.txt':
Found
Length is 20600
-- Executing [170@base-out:4] Hangup("SIP/bruce.mcalister-09051118",
"") in
new stack
== Spawn extension (base-out, 170, 4) exited non-zero on
'SIP/bruce.mcalister-09051118'

----------------------------------------------------------------------------
---------------------------------------------------------------------------

Here is an example when someone listens to their voicemail messages without
deleting any:

----------------------------------------------------------------------------
---------------------------------------------------------------------------

-- Executing [171@base-out:1] AGI("SIP/bruce.mcalister-09051118",
"agi://10.7.0.136:4573?app=getvoicemailexten") in new stack
    -- AGI Script agi://10.7.0.136:4573?app=getvoicemailexten completed,
returning 0
    -- Executing [171@base-out:2] NoOp("SIP/bruce.mcalister-09051118",
"voicemail extension=3031") in new stack
    -- Executing [171@base-out:3]
VoiceMailMain("SIP/bruce.mcalister-09051118", "3204@users")
in new stack
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-password'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-youhave'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/19'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-and' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/20'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-Old' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-onefor'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-first' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/8' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/9' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/1' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0003.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/1' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/6' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0003'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0004.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0004'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/6' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0005.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0005'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-next' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-delete'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0006.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0006'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-goodbye'
(language 'en')
[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

    -- Executing [171@base-out:4]
Hangup("SIP/bruce.mcalister-09051118", "")
in new stack
  == Spawn extension (base-out, 171, 4) exited non-zero on
'SIP/bruce.mcalister-09051118'

----------------------------------------------------------------------------
---------------------------------------------------------------------------


Our environment is as follows:

Asterisk Server:
	CentOS Linux 4.4 32bit
	UnixODBC 1.2.12
	PostgreSQL ODBC driver version 08.01.0200
	Asterisk 1.4.4
	
Database Server
	Solaris 10 Update 3
	PostgreSQL 8.2.4

If you need me to enable anything or require any further details, please
don't hesitate to ask.

PS: Any and all comments/suggestions/criticisms are most welcome :)

Thanks
Bruce

Remco Post

2007-Apr-30 23:59 UTC

head link

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

Bruce McAlister wrote:> Hi All,
> 
> I have an issue with the ODBC voicemail storage option with asterisk. All
> appears to work fine, however, I get several sql execute warnings. I was
> wondering if anyone out there could help me get to the bottom of what is
> causing this and how I could possibly go about rectifying it.
> 
> The warning message we are getting is as follows:
> 
> WARNING[30115]: app_voicemail.c:1280 delete_file: SQL Execute error!
> [DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]
> 
> This warning occurs whenever a user leaves a message for an extension. It
> also occurs when someone dials in to listen to their messages when they
hang
> up.
> 
> These messages do actually exist within the database, and asterisk does
> extract them from the database when playing back or recording messages.
> 
> Here is an example when someone leaves a message for someone:
It looks like the * database user doesn't have permission to delete
records from the voicemailmessages table. Make sure it has the propper
permissions on that table.

-- 

Remco Post

"I didn't write all this code, and I can't even pretend that all of
it
makes sense." -- Glen Hattrup

Bruce McAlister

2007-May-01 09:12 UTC

head link

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

Hi All,

I tried to send this email this morning, but I think it has been moderated
due to size issue's, so I'll resend it again in 3 parts:

PART 1

Hi All,

Just an update, after looking a little further into this, it appears that *
tries to delete a record that does not exist before inserting it into the
table, but the number of times it does that does not match up with the
number of warnings displayed on the asterisk console. 

I have attached the console output, database trace and the actual database
definition as text file attachments for readability as well as put it in
this email message.

Could another pair of eyes please go over this to see if I'm missing
anything.

Again, if you need any more details from me then please don't hesitate to
ask.

Asterisk Console Output (verbose 99/debug 99)

-- Executing [171@base-out:1] AGI("SIP/bruce.mcalister-096da288",
"agi://10.7.0.136:4573?app=getvoicemailexten") in new stack
    -- AGI Script agi://10.7.0.136:4573?app=getvoicemailexten completed,
returning 0
    -- Executing [171@base-out:2] NoOp("SIP/bruce.mcalister-096da288",
"voicemail extension=3031") in new stack
    -- Executing [171@base-out:3]
VoiceMailMain("SIP/bruce.mcalister-096da288", "3204@users")
in new stack
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-password'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-youhave'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'digits/8' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-and' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'digits/30'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'digits/1' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-Old' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-onefor'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-opts' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-first' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-message'
(language 'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000.txt':
Found
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000'
(language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-next' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'digits/2' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001.txt':
Found
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001'
(language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-next' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'digits/3' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002.txt':
Found
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002'
(language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-next' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-delete'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-toforward'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-savemessage'
(language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-helpexit'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-goodbye'
(language 'en')
[May  1 07:50:47] WARNING[30791]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[May  1 07:50:47] WARNING[30791]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[May  1 07:50:47] WARNING[30791]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[May  1 07:50:47] WARNING[30791]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[May  1 07:50:47] WARNING[30791]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

    -- Executing [171@base-out:4]
Hangup("SIP/bruce.mcalister-096da288", "")
in new stack
  == Spawn extension (base-out, 171, 4) exited non-zero on
'SIP/bruce.mcalister-096da288'

----------------------------------------------------------------------------

Thanks
Bruce

-----Original Message-----
From: asterisk-users-bounces@lists.digium.com
[mailto:asterisk-users-bounces@lists.digium.com] On Behalf Of Bruce
McAlister
Sent: 01 May 2007 00:06
To: asterisk-users@lists.digium.com
Subject: [asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help
Importance: High

Hi All,

I have an issue with the ODBC voicemail storage option with asterisk. All
appears to work fine, however, I get several sql execute warnings. I was
wondering if anyone out there could help me get to the bottom of what is
causing this and how I could possibly go about rectifying it.

The warning message we are getting is as follows:

WARNING[30115]: app_voicemail.c:1280 delete_file: SQL Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

This warning occurs whenever a user leaves a message for an extension. It
also occurs when someone dials in to listen to their messages when they hang
up.

These messages do actually exist within the database, and asterisk does
extract them from the database when playing back or recording messages.

Here is an example when someone leaves a message for someone:

----------------------------------------------------------------------------
---------------------------------------------------------------------------

-- Executing [170@base-out:1] AGI("SIP/bruce.mcalister-09051118",
"agi://10.7.0.136:4573?app=getvoicemailexten") in new stack
-- AGI Script agi://10.7.0.136:4573?app=getvoicemailexten completed,
returning 0
-- Executing [170@base-out:2] NoOp("SIP/bruce.mcalister-09051118",
"Caller
VoiceMail Extension = 3031") in new stack
-- Executing [170@base-out:3]
VoiceMail("SIP/bruce.mcalister-09051118",
"3031@users") in new stack
-- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3031/temp' (language
'en')
-- <SIP/bruce.mcalister-09051118> Playing 'vm-intro' (language
'en')
-- <SIP/bruce.mcalister-09051118> Playing 'beep' (language
'en')
-- Recording the message
-- x=0, open writing:
/usr/local/asterisk/var/spool/voicemail/users/3031/tmp/hGkNG0 format: wav49,
0x90539c8
-- User ended message by pressing #
-- <SIP/bruce.mcalister-09051118> Playing 'auth-thankyou'
(language 'en')
[Apr 30 23:56:03] WARNING[30123]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

== Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3031/INBOX/msg0002.txt':
Found
Length is 20600
-- Executing [170@base-out:4] Hangup("SIP/bruce.mcalister-09051118",
"") in
new stack
== Spawn extension (base-out, 170, 4) exited non-zero on
'SIP/bruce.mcalister-09051118'

----------------------------------------------------------------------------
---------------------------------------------------------------------------

Here is an example when someone listens to their voicemail messages without
deleting any:

----------------------------------------------------------------------------
---------------------------------------------------------------------------

-- Executing [171@base-out:1] AGI("SIP/bruce.mcalister-09051118",
"agi://10.7.0.136:4573?app=getvoicemailexten") in new stack
    -- AGI Script agi://10.7.0.136:4573?app=getvoicemailexten completed,
returning 0
    -- Executing [171@base-out:2] NoOp("SIP/bruce.mcalister-09051118",
"voicemail extension=3031") in new stack
    -- Executing [171@base-out:3]
VoiceMailMain("SIP/bruce.mcalister-09051118", "3204@users")
in new stack
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-password'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-youhave'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/19'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-and' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/20'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-Old' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-onefor'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-first' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/8' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/9' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/1' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0003.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/1' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/6' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0003'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0004.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0004'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/6' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0005.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0005'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-next' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-delete'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0006.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0006'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-goodbye'
(language 'en')
[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

    -- Executing [171@base-out:4]
Hangup("SIP/bruce.mcalister-09051118", "")
in new stack
  == Spawn extension (base-out, 171, 4) exited non-zero on
'SIP/bruce.mcalister-09051118'

----------------------------------------------------------------------------
---------------------------------------------------------------------------


Our environment is as follows:

Asterisk Server:
	CentOS Linux 4.4 32bit
	UnixODBC 1.2.12
	PostgreSQL ODBC driver version 08.01.0200
	Asterisk 1.4.4
	
Database Server
	Solaris 10 Update 3
	PostgreSQL 8.2.4

If you need me to enable anything or require any further details, please
don't hesitate to ask.

PS: Any and all comments/suggestions/criticisms are most welcome :)

Thanks
Bruce

_______________________________________________
--Bandwidth and Colocation provided by Easynews.com --

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-users
-------------- next part --------------
-- Executing [171@base-out:1] AGI("SIP/bruce.mcalister-096da288",
"agi://10.7.0.136:4573?app=getvoicemailexten") in new stack
    -- AGI Script agi://10.7.0.136:4573?app=getvoicemailexten completed,
returning 0
    -- Executing [171@base-out:2] NoOp("SIP/bruce.mcalister-096da288",
"voicemail extension=3031") in new stack
    -- Executing [171@base-out:3]
VoiceMailMain("SIP/bruce.mcalister-096da288", "3204@users")
in new stack
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-password'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-youhave'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'digits/8' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-and' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'digits/30'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'digits/1' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-Old' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-onefor'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-opts' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-first' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-message'
(language 'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000.txt':
Found
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-unknown-caller'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-next' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'digits/2' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001.txt':
Found
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-unknown-caller'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-next' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'digits/3' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002.txt':
Found
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-unknown-caller'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-next' (language
'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-delete'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-toforward'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-savemessage'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-helpexit'
(language 'en')
    -- <SIP/bruce.mcalister-096da288> Playing 'vm-goodbye'
(language 'en')
[May  1 07:50:47] WARNING[30791]: app_voicemail.c:1280 delete_file: SQL Execute
error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[May  1 07:50:47] WARNING[30791]: app_voicemail.c:1280 delete_file: SQL Execute
error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[May  1 07:50:47] WARNING[30791]: app_voicemail.c:1280 delete_file: SQL Execute
error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[May  1 07:50:47] WARNING[30791]: app_voicemail.c:1280 delete_file: SQL Execute
error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[May  1 07:50:47] WARNING[30791]: app_voicemail.c:1280 delete_file: SQL Execute
error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

    -- Executing [171@base-out:4]
Hangup("SIP/bruce.mcalister-096da288", "") in new stack
  == Spawn extension (base-out, 171, 4) exited non-zero on
'SIP/bruce.mcalister-096da288'

Bruce McAlister

2007-May-01 09:13 UTC

head link

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

Hi All,

I tried to send this email this morning, but I think it has been moderated
due to size issue's, so I'll resend it again in 3 parts:

PART 2

Database Table Definition (taken from asterisk readme's)

CREATE FUNCTION loin   (cstring)  RETURNS lo      AS 'oidin'   LANGUAGE
internal IMMUTABLE STRICT;
CREATE FUNCTION loout  (lo)       RETURNS cstring AS 'oidout'  LANGUAGE
internal IMMUTABLE STRICT;
CREATE FUNCTION lorecv (internal) RETURNS lo      AS 'oidrecv' LANGUAGE
internal IMMUTABLE STRICT;
CREATE FUNCTION losend (lo)       RETURNS bytea   AS 'oidrecv' LANGUAGE
internal IMMUTABLE STRICT;

CREATE TYPE lo (
                 INPUT = loin,
                 OUTPUT = loout,
                 RECEIVE = lorecv,
                 SEND = losend,
                 INTERNALLENGTH = 4,
                 PASSEDBYVALUE
               );
CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;

CREATE TRUSTED LANGUAGE plpgsql;

CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
    AS $$
    declare
      msgcount INTEGER;
    begin
      --    raise notice 'Starting lo_cleanup function for large object with
oid %',old.recording;
      --    If it is an update action but the BLOB (lo) field was not
changed, dont do anything
      if (TG_OP = 'UPDATE') then
        if ((old.recording = new.recording) or (old.recording is NULL)) then
          raise notice 'Not cleaning up the large object table, as recording
has not changed';
          return new;
        end if;
      end if;
      if (old.recording IS NOT NULL) then
        SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemailmessages WHERE
recording = old.recording;
        if (msgcount > 0) then
          raise notice 'Not deleting record from the large object table, as
object is still referenced';
          return new;
        else
          perform lo_unlink(old.recording);
          if found then
            raise notice 'Cleaning up the large object table';
            return new;
          else
            raise exception 'Failed to cleanup the large object table';
            return old;
          end if;
        end if;
      else
        raise notice 'No need to cleanup the large object table, no
recording on old row';
        return new;
      end if;
    end$$
    LANGUAGE plpgsql;

CREATE TABLE "public"."voicemailmessages" (
  id           BIGSERIAL PRIMARY KEY USING INDEX TABLESPACE
"bf_service_idx",
  msgnum       SMALLINT 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 '',
  recording    lo                DEFAULT NULL,
  mailboxuser  VARCHAR(80)       DEFAULT '',
  mailboxcontext VARCHAR(80)       DEFAULT ''
) WITHOUT OIDS;

CREATE INDEX idx_voicemailmessages_msgnum_dir ON
voicemailmessages(msgnum,dir)
  TABLESPACE "bf_service_idx";

CREATE TRIGGER trg_vm_cleanup AFTER DELETE OR UPDATE ON voicemailmessages
FOR EACH ROW EXECUTE PROCEDURE vm_lo_cleanup();

----------------------------------------------------------------------------

Thanks
Bruce

-----Original Message-----
From: asterisk-users-bounces@lists.digium.com
[mailto:asterisk-users-bounces@lists.digium.com] On Behalf Of Bruce
McAlister
Sent: 01 May 2007 00:06
To: asterisk-users@lists.digium.com
Subject: [asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help
Importance: High

Hi All,

I have an issue with the ODBC voicemail storage option with asterisk. All
appears to work fine, however, I get several sql execute warnings. I was
wondering if anyone out there could help me get to the bottom of what is
causing this and how I could possibly go about rectifying it.

The warning message we are getting is as follows:

WARNING[30115]: app_voicemail.c:1280 delete_file: SQL Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

This warning occurs whenever a user leaves a message for an extension. It
also occurs when someone dials in to listen to their messages when they hang
up.

These messages do actually exist within the database, and asterisk does
extract them from the database when playing back or recording messages.

Here is an example when someone leaves a message for someone:

----------------------------------------------------------------------------
---------------------------------------------------------------------------

-- Executing [170@base-out:1] AGI("SIP/bruce.mcalister-09051118",
"agi://10.7.0.136:4573?app=getvoicemailexten") in new stack
-- AGI Script agi://10.7.0.136:4573?app=getvoicemailexten completed,
returning 0
-- Executing [170@base-out:2] NoOp("SIP/bruce.mcalister-09051118",
"Caller
VoiceMail Extension = 3031") in new stack
-- Executing [170@base-out:3]
VoiceMail("SIP/bruce.mcalister-09051118",
"3031@users") in new stack
-- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3031/temp' (language
'en')
-- <SIP/bruce.mcalister-09051118> Playing 'vm-intro' (language
'en')
-- <SIP/bruce.mcalister-09051118> Playing 'beep' (language
'en')
-- Recording the message
-- x=0, open writing:
/usr/local/asterisk/var/spool/voicemail/users/3031/tmp/hGkNG0 format: wav49,
0x90539c8
-- User ended message by pressing #
-- <SIP/bruce.mcalister-09051118> Playing 'auth-thankyou'
(language 'en')
[Apr 30 23:56:03] WARNING[30123]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

== Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3031/INBOX/msg0002.txt':
Found
Length is 20600
-- Executing [170@base-out:4] Hangup("SIP/bruce.mcalister-09051118",
"") in
new stack
== Spawn extension (base-out, 170, 4) exited non-zero on
'SIP/bruce.mcalister-09051118'

----------------------------------------------------------------------------
---------------------------------------------------------------------------

Here is an example when someone listens to their voicemail messages without
deleting any:

----------------------------------------------------------------------------
---------------------------------------------------------------------------

-- Executing [171@base-out:1] AGI("SIP/bruce.mcalister-09051118",
"agi://10.7.0.136:4573?app=getvoicemailexten") in new stack
    -- AGI Script agi://10.7.0.136:4573?app=getvoicemailexten completed,
returning 0
    -- Executing [171@base-out:2] NoOp("SIP/bruce.mcalister-09051118",
"voicemail extension=3031") in new stack
    -- Executing [171@base-out:3]
VoiceMailMain("SIP/bruce.mcalister-09051118", "3204@users")
in new stack
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-password'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-youhave'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/19'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-and' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/20'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-Old' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-onefor'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-first' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/8' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/9' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/1' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0003.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/1' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/6' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0003'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0004.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0004'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/6' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0005.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0005'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-next' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-delete'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0006.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0006'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-goodbye'
(language 'en')
[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

    -- Executing [171@base-out:4]
Hangup("SIP/bruce.mcalister-09051118", "")
in new stack
  == Spawn extension (base-out, 171, 4) exited non-zero on
'SIP/bruce.mcalister-09051118'

----------------------------------------------------------------------------
---------------------------------------------------------------------------


Our environment is as follows:

Asterisk Server:
	CentOS Linux 4.4 32bit
	UnixODBC 1.2.12
	PostgreSQL ODBC driver version 08.01.0200
	Asterisk 1.4.4
	
Database Server
	Solaris 10 Update 3
	PostgreSQL 8.2.4

If you need me to enable anything or require any further details, please
don't hesitate to ask.

PS: Any and all comments/suggestions/criticisms are most welcome :)

Thanks
Bruce

_______________________________________________
--Bandwidth and Colocation provided by Easynews.com --

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-users
-------------- next part --------------
CREATE FUNCTION loin   (cstring)  RETURNS lo      AS 'oidin'   LANGUAGE
internal IMMUTABLE STRICT;
CREATE FUNCTION loout  (lo)       RETURNS cstring AS 'oidout'  LANGUAGE
internal IMMUTABLE STRICT;
CREATE FUNCTION lorecv (internal) RETURNS lo      AS 'oidrecv' LANGUAGE
internal IMMUTABLE STRICT;
CREATE FUNCTION losend (lo)       RETURNS bytea   AS 'oidrecv' LANGUAGE
internal IMMUTABLE STRICT;

CREATE TYPE lo (
                 INPUT = loin,
                 OUTPUT = loout,
                 RECEIVE = lorecv,
                 SEND = losend,
                 INTERNALLENGTH = 4,
                 PASSEDBYVALUE
               );
CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;

CREATE TRUSTED LANGUAGE plpgsql;

CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
    AS $$
    declare
      msgcount INTEGER;
    begin
      --    raise notice 'Starting lo_cleanup function for large object with
oid %',old.recording;
      --    If it is an update action but the BLOB (lo) field was not changed,
dont do anything
      if (TG_OP = 'UPDATE') then
        if ((old.recording = new.recording) or (old.recording is NULL)) then
          raise notice 'Not cleaning up the large object table, as recording
has not changed';
          return new;
        end if;
      end if;
      if (old.recording IS NOT NULL) then
        SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemailmessages WHERE
recording = old.recording;
        if (msgcount > 0) then
          raise notice 'Not deleting record from the large object table, as
object is still referenced';
          return new;
        else
          perform lo_unlink(old.recording);
          if found then
            raise notice 'Cleaning up the large object table';
            return new;
          else
            raise exception 'Failed to cleanup the large object table';
            return old;
          end if;
        end if;
      else
        raise notice 'No need to cleanup the large object table, no
recording on old row';
        return new;
      end if;
    end$$
    LANGUAGE plpgsql;

CREATE TABLE "public"."voicemailmessages" (
  id           BIGSERIAL PRIMARY KEY USING INDEX TABLESPACE
"bf_service_idx",
  msgnum       SMALLINT 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 '',
  recording    lo                DEFAULT NULL,
  mailboxuser  VARCHAR(80)       DEFAULT '',
  mailboxcontext VARCHAR(80)       DEFAULT ''
) WITHOUT OIDS;

CREATE INDEX idx_voicemailmessages_msgnum_dir ON voicemailmessages(msgnum,dir)
  TABLESPACE "bf_service_idx";

CREATE TRIGGER trg_vm_cleanup AFTER DELETE OR UPDATE ON voicemailmessages FOR
EACH ROW EXECUTE PROCEDURE vm_lo_cleanup();

Bruce McAlister

2007-May-01 09:17 UTC

head link

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

Hi All,

I tried to send this email this morning, but I think it has been moderated
due to size issue's, so I'll resend it again in 3 parts:

PART 3

Database statement output of the above session is attached in a text file
that has been zipped (because it is bigger than 40K (46K to be precise) and
is being moderated)


Thanks
Bruce

-----Original Message-----
From: asterisk-users-bounces@lists.digium.com
[mailto:asterisk-users-bounces@lists.digium.com] On Behalf Of Bruce
McAlister
Sent: 01 May 2007 00:06
To: asterisk-users@lists.digium.com
Subject: [asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help
Importance: High

Hi All,

I have an issue with the ODBC voicemail storage option with asterisk. All
appears to work fine, however, I get several sql execute warnings. I was
wondering if anyone out there could help me get to the bottom of what is
causing this and how I could possibly go about rectifying it.

The warning message we are getting is as follows:

WARNING[30115]: app_voicemail.c:1280 delete_file: SQL Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

This warning occurs whenever a user leaves a message for an extension. It
also occurs when someone dials in to listen to their messages when they hang
up.

These messages do actually exist within the database, and asterisk does
extract them from the database when playing back or recording messages.

Here is an example when someone leaves a message for someone:

----------------------------------------------------------------------------
---------------------------------------------------------------------------

-- Executing [170@base-out:1] AGI("SIP/bruce.mcalister-09051118",
"agi://10.7.0.136:4573?app=getvoicemailexten") in new stack
-- AGI Script agi://10.7.0.136:4573?app=getvoicemailexten completed,
returning 0
-- Executing [170@base-out:2] NoOp("SIP/bruce.mcalister-09051118",
"Caller
VoiceMail Extension = 3031") in new stack
-- Executing [170@base-out:3]
VoiceMail("SIP/bruce.mcalister-09051118",
"3031@users") in new stack
-- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3031/temp' (language
'en')
-- <SIP/bruce.mcalister-09051118> Playing 'vm-intro' (language
'en')
-- <SIP/bruce.mcalister-09051118> Playing 'beep' (language
'en')
-- Recording the message
-- x=0, open writing:
/usr/local/asterisk/var/spool/voicemail/users/3031/tmp/hGkNG0 format: wav49,
0x90539c8
-- User ended message by pressing #
-- <SIP/bruce.mcalister-09051118> Playing 'auth-thankyou'
(language 'en')
[Apr 30 23:56:03] WARNING[30123]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

== Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3031/INBOX/msg0002.txt':
Found
Length is 20600
-- Executing [170@base-out:4] Hangup("SIP/bruce.mcalister-09051118",
"") in
new stack
== Spawn extension (base-out, 170, 4) exited non-zero on
'SIP/bruce.mcalister-09051118'

----------------------------------------------------------------------------
---------------------------------------------------------------------------

Here is an example when someone listens to their voicemail messages without
deleting any:

----------------------------------------------------------------------------
---------------------------------------------------------------------------

-- Executing [171@base-out:1] AGI("SIP/bruce.mcalister-09051118",
"agi://10.7.0.136:4573?app=getvoicemailexten") in new stack
    -- AGI Script agi://10.7.0.136:4573?app=getvoicemailexten completed,
returning 0
    -- Executing [171@base-out:2] NoOp("SIP/bruce.mcalister-09051118",
"voicemail extension=3031") in new stack
    -- Executing [171@base-out:3]
VoiceMailMain("SIP/bruce.mcalister-09051118", "3204@users")
in new stack
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-password'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-youhave'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/19'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-and' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/20'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-Old' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-onefor'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-INBOX' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-messages'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-first' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0000'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/8' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/9' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0001'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/1' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/2' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0002'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0003.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing
'vm-from-phonenumber'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/1' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/6' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/3' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/4' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/0' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0003'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/5' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0004.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0004'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/6' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0005.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0005'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-repeat'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-next' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-delete'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-message'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'digits/7' (language
'en')
  == Parsing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0006.txt':
Found
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-unknown-caller'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing
'/usr/local/asterisk/var/spool/voicemail/users/3204/INBOX/msg0006'
(language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-prev' (language
'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-advopts'
(language 'en')
    -- <SIP/bruce.mcalister-09051118> Playing 'vm-goodbye'
(language 'en')
[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

[Apr 30 23:44:25] WARNING[30115]: app_voicemail.c:1280 delete_file: SQL
Execute error!
[DELETE FROM voicemailmessages WHERE dir=? AND msgnum=?]

    -- Executing [171@base-out:4]
Hangup("SIP/bruce.mcalister-09051118", "")
in new stack
  == Spawn extension (base-out, 171, 4) exited non-zero on
'SIP/bruce.mcalister-09051118'

----------------------------------------------------------------------------
---------------------------------------------------------------------------


Our environment is as follows:

Asterisk Server:
	CentOS Linux 4.4 32bit
	UnixODBC 1.2.12
	PostgreSQL ODBC driver version 08.01.0200
	Asterisk 1.4.4
	
Database Server
	Solaris 10 Update 3
	PostgreSQL 8.2.4

If you need me to enable anything or require any further details, please
don't hesitate to ask.

PS: Any and all comments/suggestions/criticisms are most welcome :)

Thanks
Bruce

_______________________________________________
--Bandwidth and Colocation provided by Easynews.com --

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-users
-------------- next part --------------
A non-text attachment was scrubbed...
Name: db_trace.zip
Type: application/x-zip-compressed
Size: 2807 bytes
Desc: not available
Url :
http://lists.digium.com/pipermail/asterisk-users/attachments/20070501/ac96965e/db_trace.bin

Bruce McAlister

2007-May-02 02:44 UTC

head link

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

Hi Remco Post,

Thank you for the tip. I have verified that the permissions are correct for
the table and procedure.

However, I think I may have got to the bottom of the issue now.

What look like was happening is that asterisk was trying to delete any
matching row prior to an insert operation. So, when a user left a message,
for example, message 1, asterisk would attempt to delete message 1 before
inserting it for that user. However, message 1 does not exist at that time
and thus the ODBC driver returns "SQL_NO DATA". 

The same happens when a user checks their voicemail, once an message has
been listened to asterisk moves it to the "Old" directory, that way it
can
distinguish between new/old messages. When a user listens to the voicemail,
asterisk then tries to insert the message into the "Old" tree, prior
to
doing the insert, asterisk tries to delete the last available message
returned from a select count(*) operation. This message does not exist and
the odbc driver returns "SQL_NO_DATA".

The delete_file function in app_voicemail.c does not accommodate for this
return code SQL_NO_DATA and thus spits out the warning on the console.

I thus changed the following condition in function delete_file in
app_voicemail.c from:

if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
	ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql);
      SQLFreeHandle (SQL_HANDLE_STMT, stmt);
      ast_odbc_release_obj(obj);
      goto yuck;
}

To:

if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO) &&
(res !SQL_NO_DATA)) {
      ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql);
      SQLFreeHandle (SQL_HANDLE_STMT, stmt);
      ast_odbc_release_obj(obj);
      goto yuck;
}

This seems to have fixed the problem.

Thanks
Bruce

Seemingly Similar Threads

Search for more possibly parallel threads

asterisk users - Apr 2007 - Asterisk 1.4.4 VoiceMail ODBC Storage Help

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

[asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help

Seemingly Similar Threads