[Home]

Summary:ASTERISK-20717: Voicemail access "SQL Get Data error! coltitle=msg_id"
Reporter:Alec Davis (alecdavis)Labels:
Date Opened:2012-11-21 17:45:16.000-0600Date Closed:2013-01-18 12:15:20.000-0600
Priority:MinorRegression?
Status:Closed/CompleteComponents:Applications/app_voicemail/ODBC
Versions:11.0.1 Frequency of
Occurrence
Frequent
Related
Issues:
Environment:Voicemail Build:= ODBC Storage MySQL backend Attachments:
Description:Updraded from 1.8 to 11 and added in the extra msg_id field in MySQL,
as noted in https://wiki.asterisk.org/wiki/display/AST/ODBC+Voicemail+Storage

The message "SQL Get Data error! coltitle=msg_id" is emitted on console under the following events:
1) When using app_directory/app_voicemail when playing the username.
2) When playing, unheard previous 1.8 recordings.
3) When playing, 'OLD' meessages, whether recorded on 1.8 or 11


As messages are listened to, and then moved to 'Old' they loose there msg_id - see tables below.
{code}
mysql> select id,msgnum,dir,msg_id,mailboxuser from voicemessages where mailboxuser=8512;
+--------+--------+--------------------------------------------------+----------------------+-------------+
| id     | msgnum | dir                                              | msg_id               | mailboxuser |
+--------+--------+--------------------------------------------------+----------------------+-------------+
|   2331 |      0 | /var/spool/asterisk/voicemail/default/8512/Old   | NULL                 | 8512        |
|  26321 |     -1 | /var/spool/asterisk/voicemail/default/8512/greet | NULL                 | 8512        |
|  80021 |      1 | /var/spool/asterisk/voicemail/default/8512/Old   | NULL                 | 8512        |
| 127731 |      2 | /var/spool/asterisk/voicemail/default/8512/Old   | NULL                 | 8512        |
| 127751 |      3 | /var/spool/asterisk/voicemail/default/8512/Old   | NULL                 | 8512        |
| 127761 |      0 | /var/spool/asterisk/voicemail/default/8512/INBOX | 1353539236-128701767 | 8512        |
+--------+--------+--------------------------------------------------+----------------------+-------------+
6 rows in set (0.00 sec)

mysql> select id,msgnum,dir,msg_id,mailboxuser from voicemessages where mailboxuser=8512;
+--------+--------+--------------------------------------------------+--------+-------------+
| id     | msgnum | dir                                              | msg_id | mailboxuser |
+--------+--------+--------------------------------------------------+--------+-------------+
|   2331 |      0 | /var/spool/asterisk/voicemail/default/8512/Old   | NULL   | 8512        |
|  26321 |     -1 | /var/spool/asterisk/voicemail/default/8512/greet | NULL   | 8512        |
|  80021 |      1 | /var/spool/asterisk/voicemail/default/8512/Old   | NULL   | 8512        |
| 127731 |      2 | /var/spool/asterisk/voicemail/default/8512/Old   | NULL   | 8512        |
| 127751 |      3 | /var/spool/asterisk/voicemail/default/8512/Old   | NULL   | 8512        |
| 127771 |      4 | /var/spool/asterisk/voicemail/default/8512/Old   | NULL   | 8512        |
+--------+--------+--------------------------------------------------+--------+-------------+
{code}

Comments:By: Alec Davis (alecdavis) 2012-11-21 18:08:31.877-0600

The exact message on cnsole when accessing username:
{code}2012-11-22 11:50:56.636500] WARNING[1752][C-00000135]: app_voicemail.c:3713 retrieve_file: SQL Get Data error! coltitle=msg_id
[SELECT * FROM voicemessages WHERE dir=? AND msgnum=?]{code}



By: Jonathan Rose (jrose) 2012-11-29 16:53:28.235-0600

Hey Alec, I have a patch for this that I think solves the problem.
https://reviewboard.asterisk.org/r/2220/

It doesn't magically fix messages that were broken by this whole process already, but it does keep them from getting jacked on when they change folders.

If you could verify that the patch works, that might speed up the review. If not it probably won't take very long for it to be approved since the problem seems to have been pretty straight forward.

By: Alec Davis (alecdavis) 2012-11-29 18:27:56.892-0600

I'd already found that missing copy of msg_id in the ODBC COPY, and has been in place for a few days now.
But was unsure as to whether the msd_id's in the system were supposed to be unique.

This fix potentially could make 2 messages have the same unique id.
It doesn't in this case where a message is listened to then moved to OLD.

Now this senario is getting into territory I haven't used;
 if you can copy a message from 1 mailbox to another, if the ODBC COPY method is used (with this patch), then 2 messages have the same msg_id.




By: Jonathan Rose (jrose) 2012-11-30 10:40:34.303-0600

Yeah, I wasn't sure if it needed to be unique globally or just to the specific mailbox when I was playing with that. While I'm not currently aware of any use of copy that doesn't function more as a move, the fact that the function is called copy does mean we should treat it as such and make it safe for copying files rather than just moving them, so thanks for that.

By: Jonathan Rose (jrose) 2013-01-03 15:17:36.234-0600

Hey Alec, I've updated the patch for this issue currently on reviewboard: https://reviewboard.asterisk.org/r/2220

The patch does the following:

* Fixes the warning messages you were receiving. Now when retrieving a message if Asterisk gets a NULL field when checking the msg_id field, it just silently updates the msg_id field using the new generate_msg_id function.
* msg_ids should now have a somewhat better guarantee for uniqueness. Multiple systems running Asterisk while using the same database could break this functionality, but that is a separate issue that we have identified and are considering fixing in trunk with UUID spport.
* Copying messages now supplies copies with an msg_id.

If you could verify whether or not this fix addresses your concerns, I'd appreciate it.