Summary:ASTERISK-24772: ODBC error in realtime sippeers when device unregisters under MariaDB
Reporter:Richard Miller (ulogic)Labels:
Date Opened:2015-02-09 10:35:11.000-0600Date Closed:2015-02-11 11:13:39.000-0600
Versions:11.16.0 Frequency of
Environment:CentOS 6.4, MariaDB 10.0.xAttachments:( 0) chan_sip.diff
Description:In the file contrib/realtime/mysql/sippeers.sql, the port column is defined as
`port` int(5) DEFAULT NULL

When a device unregisters, the query to clear the port number in sippeers uses an empty string in the function destroy_association.  Most database engines will coerce an empty string value to a zero value.  MariaDB does not do this.

The log shows

WARNING[17024]: res_odbc.c:646 ast_odbc_prepare_and_execute: SQL Execute returned an error -1: HY000: [MySQL][ODBC 5.1 Driver][mysqld-5.5.5-10.0.16-MariaDB]Incorrect integer value: '' for column 'port' at row 1 (108)
WARNING[17024]: res_odbc.c:658 ast_odbc_prepare_and_execute: SQL Execute error -1! Verifying connection to ...
Comments:By: Richard Miller (ulogic) 2015-02-09 10:36:48.706-0600

Use a 0 value instead of an empty string when clearing the port when a device unregisters.

By: Matt Jordan (mjordan) 2015-02-09 20:33:11.942-0600

It looks like {{build_peer}} will handle this case as well:

if (!(port = port_str2int(v->value, 0))) {
if (realtime) {
/* If stored as integer, could be 0 for some DBs (notably MySQL) */
peer->portinuri = 0;
} else {
ast_log(LOG_WARNING, "Invalid peer port configuration at line %d : %s\n", v->lineno, v->value);

By: Matt Jordan (mjordan) 2015-02-11 11:15:39.490-0600

Merged - thanks for the contribution!