[Home]

Summary:ASTERISK-28242: Can't Retrieve Voicemail from PostgreSQL database when "msgnum" is INT type
Reporter:Tahmim Ahmed Shibli (shibli05+asterisk@gmail.com)Labels:
Date Opened:2019-01-14 05:20:40.000-0600Date Closed:2020-01-14 11:13:53.000-0600
Priority:MajorRegression?
Status:Closed/CompleteComponents:Applications/app_voicemail/ODBC
Versions:13.8.2 Frequency of
Occurrence		Constant
Related
Issues:
Environment:CentOS 7, PostgreSQL 9.5, asterisk-certified-13.8-cert4Attachments:
Description:When I connect to voicemail_message table which have msgnum columnt datatype INT, I get the below messages in the asterisk log, but the message is saved in database:

{code:none}
app_voicemail.c:3838 retrieve_file: SQL Execute error!
[SELECT * FROM voicemail_messages WHERE dir=? AND msgnum=?]

[Jan  9 12:26:43] WARNING[1386][C-00000005]: res_odbc.c:459 ast_odbc_print_errors: SQL Execute returned an error: HY000: ERROR:  operator does not exist: integer = character at character 102;
Error while executing the query
[Jan  9 12:26:43] WARNING[1386][C-00000005]: res_odbc.c:403 ast_odbc_prepare_and_execute: SQL Execute error -1!
[Jan  9 12:26:43] WARNING[1386][C-00000005]: app_voicemail.c:3838 retrieve_file: SQL Execute error!
[SELECT * FROM voicemail_messages WHERE dir=? AND msgnum=?]

{code}

And, when I dial to retrieve a voicemail I can hear to how many voicemail messages I have, and when I press 1 for a new message, it says the first message, and then print the below error and skips the message and goes to advance options.

{code:none}
WARNING[1076][C-00000007]: res_odbc.c:459 ast_odbc_print_errors: SQL Execute returned an error: HY000: ERROR:  operator does not exist: integer = character at character 102;
Error while executing the query
[Jan  9 13:49:55] WARNING[1076][C-00000007]: res_odbc.c:403 ast_odbc_prepare_and_execute: SQL Execute error -1!
[Jan  9 13:49:55] WARNING[1076][C-00000007]: app_voicemail.c:3844 retrieve_file: SQL Execute error!
[SELECT * FROM voicemail_messages WHERE dir=? AND msgnum=?]

 == Parsing '/var/spool/asterisk/voicemail/shiblicus1/3206/INBOX/msg0000.txt': Found
   -- <PJSIP/shiblicus1_cus1group2_1_3206-00000008> Playing 'vm-message.ulaw' (language 'en')
[Jan  9 13:49:55] WARNING[1076][C-00000007]: app_voicemail.c:8692 play_message: No origtime?!

   -- Executing [check_voicemail@maxcore:3] Hangup("PJSIP/shiblicus1_cus1group2_1_3206-00000008", "") in new stack
{code}

I further checked the asterisk source, and from *apps/app_voicemail.c* file I can see that, from int msgnum value is copied to char[] msgnums, and this msgnums char array is finally passed as the SQL parameter. *If msgnum column from the voicemail table is an Integer field, the msgnum int should be passed to the query, isn’t it!!*

I saw this similar pattern on functions:

static int retrieve_file(char *dir, int msgnum), static int message_exists(char *dir, int msgnum), static void delete_file(const char *sdir, int smsg), etc.

I checked the recent versions to, there is no change in *apps/app_voicemail.c* in this regard.


After seeing this, I changed the msgnum column type to varchar(80) on the PostgreSQL database, then I can retrieve and hear the voicemail from database. But, then there is no msgnum greater than '10'. After reaching to '10' everytime a new voicemail arrives, the last msgnum '10' is replaced by the new message.

Am I doing something incorrectly?

I tried asking the same question on [Asterisk community| https://community.asterisk.org/t/voicemail-odbc-ast-odbc-print-errors-sql-execute-returned-an-error-hy000-error-operator-does-not-exist-integer-character-at/77990/1], but, still now no reply there.

Comments:By: Asterisk Team (asteriskteam) 2019-01-14 05:20:41.011-0600

Thanks for creating a report! The issue has entered the triage process. That means the issue will wait in this status until a Bug Marshal has an opportunity to review the issue. Once the issue has been reviewed you will receive comments regarding the next steps towards resolution.

A good first step is for you to review the [Asterisk Issue Guidelines|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines] if you haven't already. The guidelines detail what is expected from an Asterisk issue report.

Then, if you are submitting a patch, please review the [Patch Contribution Process|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process].

Please note that once your issue enters an open state it has been accepted. As Asterisk is an open source project there is no guarantee or timeframe on when your issue will be looked into. If you need expedient resolution you will need to find and pay a suitable developer. Asking for an update on your issue will not yield any progress on it and will not result in a response. All updates are posted to the issue when they occur.
By: Kevin Harwell (kharwell) 2019-01-14 11:09:43.564-0600

I noticed you have "asterisk-certified-13.8-cert4" as the version of Asterisk. Any reason you are using the certified version vs the latest release of Asterisk? That's not the latest version of Asterisk or even certified Asterisk. There have been many issues fixed between the version you are using and the current version of your branch. Please test with the latest version in your Asterisk branch and report whether the issue persists.

Please see the Asterisk Versions [1] wiki page for info on which versions of Asterisk are supported.

[1] https://wiki.asterisk.org/wiki/display/AST/Asterisk+Versions
By: Kevin Harwell (kharwell) 2019-01-14 11:11:09.010-0600

Also if the issue does still persist please provide configuration files, and steps to reproduce along with a debug log of an occurrence [1].

[1] https://wiki.asterisk.org/wiki/display/AST/Collecting+Debug+Information
By: Asterisk Team (asteriskteam) 2019-01-28 12:00:01.028-0600

Suspended due to lack of activity. This issue will be automatically re-opened if the reporter posts a comment. If you are not the reporter and would like this re-opened please create a new issue instead. If the new issue is related to this one a link will be created during the triage process. Further information on issue tracker usage can be found in the Asterisk Issue Guidlines [1].

[1] https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines