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

[Joshua Colp (JIRA)]

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

Joshua Colp commented on ASTERISK-27760:
----------------------------------------

You can use the "Cherrypick" button to move it to 13, and then abandon the 13.20 review.

> 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)