RE: [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
___
--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
Re: [asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help
Bruce McAlister wrote:
Hi Remco Post,
Having read your patch I suggest you report this bug at bugs.digium.com,
it seems to be legit...
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.
--
Remco Post
I didn't write all this code, and I can't even pretend that all of it
makes sense. -- Glen Hattrup
___
--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
Re: [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 ___ --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
RE: [asterisk-users] Asterisk 1.4.4 VoiceMail ODBC Storage Help
]: 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 [EMAIL PROTECTED]: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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED]: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 [EMAIL PROTECTED]:2] NoOp(SIP/bruce.mcalister-09051118, Caller VoiceMail Extension = 3031) in new stack -- Executing [EMAIL PROTECTED]:3] VoiceMail(SIP/bruce.mcalister-09051118, [EMAIL PROTECTED]) 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 [EMAIL PROTECTED]: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 [EMAIL PROTECTED]: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 [EMAIL PROTECTED]:2] NoOp(SIP/bruce.mcalister-09051118, voicemail extension=3031) in new stack -- Executing [EMAIL PROTECTED]:3] VoiceMailMain(SIP/bruce.mcalister-09051118, [EMAIL PROTECTED]) 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
RE: [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 '', recordingloDEFAULT 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED]: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 [EMAIL PROTECTED]:2] NoOp(SIP/bruce.mcalister-09051118, Caller VoiceMail Extension = 3031) in new stack -- Executing [EMAIL PROTECTED]:3] VoiceMail(SIP/bruce.mcalister-09051118, [EMAIL PROTECTED]) in new stack -- SIP/bruce.mcalister-09051118 Playing '/usr/local/asterisk/var/spool/voicemail/users/3031/temp
[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 [EMAIL PROTECTED]: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 [EMAIL PROTECTED]:2] NoOp(SIP/bruce.mcalister-09051118, Caller VoiceMail Extension = 3031) in new stack -- Executing [EMAIL PROTECTED]:3] VoiceMail(SIP/bruce.mcalister-09051118, [EMAIL PROTECTED]) 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 [EMAIL PROTECTED]: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 [EMAIL PROTECTED]: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 [EMAIL PROTECTED]:2] NoOp(SIP/bruce.mcalister-09051118, voicemail extension=3031) in new stack -- Executing [EMAIL PROTECTED]:3] VoiceMailMain(SIP/bruce.mcalister-09051118, [EMAIL PROTECTED]) 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/msg.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/msg' (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')
