System information

Verifying binary data stored in MySQL

To verify that your data is being written correctly, you can use the mysql application

to log into your database and export the voicemail recording to a file:

$ mysql -u asterisk -p asterisk

Enter password:

Once logged into the database, you can use a SELECT statement to dump the contents

of the recording to a file. First, though, make sure you have at least a single recording

in your voicemessages table:

mysql> SELECT uniqueid, msgnum, callerid, mailboxuser, mailboxcontext, `read`

-> FROM voicemessages;

+----------+--------+------------------------------+-------------

| uniqueid | msgnum | callerid | mailboxuser

+----------+--------+------------------------------+-------------

| 1 | 0 | "Leif Madsen" <100> | 100

| 2 | 1 | "Leif Madsen" <100> | 100

| 3 | 2 | "Leif Madsen" <100> | 100

| 5 | 0 | "Julie Bryant" <12565551111> | 100

+----------+--------+------------------------------+-------------

+----------------+------+

| mailboxcontext | read |

+----------------+------+

| shifteight.org | 0 |

| default | 0 |

+----------------+------+

You can also add the recording column to the SELECT statement, but

you’ll end up with a lot of gibberish on your screen.

Having verified that you have data in your voicemessages table, you can export one of

the recordings and play it back from the console.

mysql> SELECT recording FROM voicemessages WHERE uniqueid = '5'

-> DUMPFILE '/tmp/voicemail_recording.wav';

The user you’re exporting data with needs to have the FILE permission

in MySQL, which means it must have been granted ALL access. If you

did not grant ALL privileges to the asterisk user, you will need to utilize

the root user for file export.

Now exit the MySQL console, and use the play application from the console (assuming

you have speakers and a sound card configured on your Asterisk system, which you

386 | Chapter 16: Relational Database Integration