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

[Nic Colledge (JIRA)]

Nic Colledge created ASTERISK-27760:
---------------------------------------

             Summary: 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
            Severity: Minor


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)