| Summary: | ASTERISK-27760: Asterisk ODBC Voicemail Prompt storage fails with recent MariaDB version. | ||
| Reporter: | Nic Colledge (nic) | Labels: | patch |
| Date Opened: | 2018-03-21 19:23:46 | Date Closed: | 2018-05-21 09:06:18 |
| Priority: | Minor | Regression? | No |
| Status: | Closed/Complete | Components: | Applications/app_voicemail/ODBC |
| Versions: | 13.20.0 | Frequency of Occurrence | Constant |
| Related Issues: | |||
| 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 | Attachments: | ( 0) app_voicemail2.patch |
| Description: | 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] | ||
| Comments: | By: Asterisk Team (asteriskteam) 2018-03-21 19:23:47.549-0500 Thanks for creating a report! The issue has entered the triage process. That means the issue will wait in this status until a Bug Marshal has an opportunity to review the issue. Once the issue has been reviewed you will receive comments regarding the next steps towards resolution. A good first step is for you to review the [Asterisk Issue Guidelines|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines] if you haven't already. The guidelines detail what is expected from an Asterisk issue report. Then, if you are submitting a patch, please review the [Patch Contribution Process|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process]. By: Nic Colledge (nic) 2018-03-21 19:25:10.473-0500 Patch mentioned in issue description. By: Nic Colledge (nic) 2018-03-21 19:33:09.210-0500 I messed up the first patch. This is the correct one. By: Joshua C. Colp (jcolp) 2018-03-26 09:57:17.423-0500 Do you plan on putting these changes up on Gerrit? By: Nic Colledge (nic) 2018-03-26 10:04:27.314-0500 Hi Joshua, Yes I can put them on Gerrit this evening. Thanks, Nic. By: Joshua C. Colp (jcolp) 2018-03-26 10:09:28.553-0500 Putting into feedback until such time as you put the review up. By: Nic Colledge (nic) 2018-03-27 18:56:52.982-0500 Gerrit change 8680. For future reference should I have used the 13-branch as a starting point rather than the 13.20? Thanks, Nic. By: Joshua C. Colp (jcolp) 2018-03-27 19:00:28.555-0500 You should always use the branch, such as 13, to do changes. 13.20 is a release branch which is used if a security release needs to be made. It will not receive the change. By: Richard Mudgett (rmudgett) 2018-03-27 19:03:05.534-0500 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. By: Nic Colledge (nic) 2018-03-27 19:15:41.883-0500 Ok cool, should I abandon that change in Gerrit then and re-do it on 13? By: Joshua C. Colp (jcolp) 2018-03-27 19:19:34.152-0500 You can use the "Cherrypick" button to move it to 13, and then abandon the 13.20 review. By: Nic Colledge (nic) 2018-03-27 19:26:29.127-0500 Done :-) New change number 8681. Thanks again, Nic. By: Friendly Automation (friendly-automation) 2018-05-21 09:06:19.788-0500 Change 9011 merged by Joshua Colp: app_voicemail: Fix data-type mismatch between app_voicemail and database [https://gerrit.asterisk.org/9011|https://gerrit.asterisk.org/9011] By: Friendly Automation (friendly-automation) 2018-05-21 09:06:29.078-0500 Change 9010 merged by Joshua Colp: app_voicemail: Fix data-type mismatch between app_voicemail and database [https://gerrit.asterisk.org/9010|https://gerrit.asterisk.org/9010] By: Friendly Automation (friendly-automation) 2018-05-21 09:06:37.554-0500 Change 8681 merged by Joshua Colp: app_voicemail: Fix data-type mismatch between app_voicemail and database [https://gerrit.asterisk.org/8681|https://gerrit.asterisk.org/8681] | ||