[asterisk-bugs] [JIRA] (ASTERISK-27760) Asterisk ODBC Voicemail Prompt storage fails with recent MariaDB version.

[Richard Mudgett (JIRA)]

    [ https://issues.asterisk.org/jira/browse/ASTERISK-27760?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=242851#comment-242851 ] 

Richard Mudgett commented on ASTERISK-27760:
--------------------------------------------

Yes you should have used the 13-branch as the starting point.  The 13.20 branch is only for special patches before and after the 13.20 point release.  Normal fixes do not go there.  Normal fixes currently go in the 13, 15, and master branches.

> Asterisk ODBC Voicemail Prompt storage fails with recent MariaDB version.
> -------------------------------------------------------------------------
>
>                 Key: ASTERISK-27760
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-27760
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Applications/app_voicemail/ODBC
>    Affects Versions: 13.20.0
>         Environment: Ubuntu 16.04.4 LTS
> Asterisk 13.20.0
> MariaDB ODBC Driver Version: 3.0.3
> MariaDB Version (Working): 5.5.5-10.1.30
> MariaDB Version (Failing): 5.5.5-10.2.13
>            Reporter: Nic Colledge
>            Assignee: Unassigned
>            Severity: Minor
>              Labels: patch
>         Attachments: app_voicemail2.patch
>
>
> TLDR; When saving Voicemail prompts (unavailable and busy messages) Asterisk tries to insert integer columns as SQL_CHAR. This patch changes the parameter types on the query to match the types in the database (as defined by contrib/realtime script). This breaks with recent MariaDB versions.
> Long Version:
> Trying to record voicemail unavailable prompt messages using the voicemail app tonight and ran into an issue saving the message to the database with the following message on the console:
> 	WARNING[45801][C-00000000]: app_voicemail.c:4301 insert_data_cb: SQL Direct Execute failed!
> 	WARNING[45801][C-00000000]: app_voicemail.c:4419 store_file: SQL Execute error!
> 	[INSERT INTO voicemail_messages (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext,flag,msg_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)]
> Turning on ODBC logging and checking the detail gave:
> 	[ODBC][45692][1521677051.260334][SQLExecDirect.c][240]
>                 Entry:
>                         Statement = 0x36d9ed0
>                         SQL = [INSERT INTO voicemail_messages (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext,...][leng$
> 	[ODBC][45692][1521677051.261761][SQLExecDirect.c][503]
>                 Exit:[SQL_ERROR]
>                 DIAG [22007] [ma-3.0.3][10.2.13-MariaDB-10.2.13+maria~xenial-log]Incorrect integer value: '' for column 'origtime' at row 1
> Confirmed by checking the source code against the voicemail_messages table create script in contrib/realtime; Asterisk app_voicemail is trying to insert a empty string into an integer column in the database. This gets rejected by version 10.2.13 of MariaDB (see below).
> The attached patch changes the data types of the integer parameters to match the database column types.
> Please check the patch for correctness. I'm not a C programmer but hopefully I've not done anything too stupid.
> Checking for the issue without asterisk (MariaDB has changed behavior at some point):
> 	Create a voicemail_messages table using the mysql script from the asterisk contrib/realtime directory.
> 	Run this on the two versions of MariaDB (kind of what asterisk does through ODBC with the origtime int):
> 	INSERT INTO voicemail_messages (dir, msgnum, origtime) VALUES ('/TEST/X', 1, '')
> 	10.2.13 errors: SQL1.sql: Error (1,46): Incorrect integer value: '' for column 'origtime' at row 1
> 	10.1.30 works: SQL1.sql: 1 row inserted [0.036s]
> 	It only seems fussy about the "empty string" entry for integer types. '1' for example works fine.
> 	I'm not sure if this change is deliberate on MariaDBs part and/or if it affects newer MySQL also. I will probably raise it with MariaDB also to be sure.
> 	Either way, it feels like the "right thing" to do is for the datatypes in asterisk to match the DB data types.
> 	
> Patch Testing: 
> Recorded a few Voicemail unavailable messages after applying the patch.
> No errors on asterisk console.
> Observed record appearing in MariaDB database.
> Checked ODBC log for success:
> 	[ODBC][22408][1521675540.973168][SQLExecDirect.c][240]
>                 Entry:
>                         Statement = 0x7f9a800108d0
>                         SQL = [INSERT INTO voicemail_messages (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext,...][leng$
> 	[ODBC][22408][1521675541.008201][SQLExecDirect.c][503]
>                 Exit:[SQL_SUCCESS]



--
This message was sent by Atlassian JIRA
(v6.2#6252)