[OpenSIPS-Users] MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

[Brett Nemeroff]

Hello Calvin,
Looks like your coerecing a float into an integer field. I think it's your
delay field. What do you expect to happen here? I mean that seriously. If
the delay field is an integer, you can't put anything but an integer in it
if you have no access to change the database type.

Alternatively, you could try to use transformations to manipulate the
numeric into something that looks like an integer (for example, multiple
those values by 10000 and store that.

Let us know if that helps or if you need some more help.
-Brett


On Mon, Mar 9, 2020 at 12:51 AM Calvin Ellison <calvin.ellison at voxox.com>
wrote:

> It appears that OpenSIPS is assuming an incorrect integer data type on a
> field that is a floating-point value. I have no access to change the field
> type in the database. What can be done?
>
> Server version: 10.2.31-MariaDB MariaDB Server
>
> Query and response attached
>
> async(avp_db_query("call lrn.fulldataz('$var(number)',curdate())",
> "$avp(number); $avp(lrn); $avp(portType); $avp(state); $avp(network);
> $avp(ocn); $avp(ratecenter); $avp(class); $avp(lata); $avp(country);
> $avp(reachable); $avp(reason); $avp(dnc); $avp(good)", "2"), resume_dnc);
>
> ERROR:core:db_str2int: Unexpected characters: [.0011]
>
> # opensips -V
> version: opensips 2.4.7 (x86_64/linux)
> flags: STATS: On, DISABLE_NAGLE, USE_MCAST, SHM_MMAP, PKG_MALLOC,
> F_MALLOC, FAST_LOCK-ADAPTIVE_WAIT
> ADAPTIVE_WAIT_LOOPS=1024, MAX_RECV_BUFFER_SIZE 262144, MAX_LISTEN 16,
> MAX_URI_SIZE 1024, BUF_SIZE 65535
> poll method support: poll, epoll, sigio_rt, select.
> main.c compiled on  with gcc 7
>
>
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: 15 columns returned from the query
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:core:db_allocate_columns: allocate 420 bytes for result columns at
> 0x7f4bed66fa20
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fa98)[0]=[number]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_STRING result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66faa8)[1]=[lrn]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_STRING result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fab8)[2]=[port type]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_INT result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fac8)[3]=[state]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_STRING result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fad8)[4]=[network]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_STRING result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fae8)[5]=[ocn]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_STRING result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66faf8)[6]=[ratecenter]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_STRING result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fb08)[7]=[class]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_STRING result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fb18)[8]=[lata]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_STRING result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fb28)[9]=[country]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_STRING result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fb38)[10]=[reachable]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_BIGINT result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fb48)[11]=[reason]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_STRING result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fb58)[12]=[dnc]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_INT result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fb68)[13]=[good]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_BIGINT result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f4bed66fb78)[14]=[delay]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_get_columns: use DB_INT result type
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:core:db_allocate_rows: allocate 496 bytes for result rows and values at
> 0x7f4bed66e878
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting STRING [2132850556]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting STRING [2132620105]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting INT [2]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting STRING [CA]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting STRING [BANDWIDTH.COM CLEC- LLC
> - CA]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting STRING [979E]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting STRING [LSAN DA 01]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting STRING [L]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting STRING [730]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting STRING [US]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting INT BIG[1]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting STRING [SS7 ID]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting INT [0]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting INT BIG[1]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> DBG:db_mysql:db_mysql_str2val: converting INT [0.0012]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> ERROR:core:db_str2int: Unexpected characters: [.0012]
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> ERROR:db_mysql:db_mysql_str2val: error while converting integer value from
> string
> Mar  8 22:28:11 localhost /usr/sbin/opensips[32124]:
> ERROR:db_mysql:db_mysql_convert_row: failed to convert value
>
>
>
> Regards,
>
> *Calvin Ellison*
> Senior Voice Operations Engineer
> calvin.ellison at voxox.com
> +1 (213) 285-0555
>
> -----------------------------------------------
> *voxox.com <http://www.voxox.com/> *
> 5825 Oberlin Drive, Suite 5
> San Diego, CA 92121
> [image: Voxox]
>
> _______________________________________________
> Users mailing list
> Users at lists.opensips.org
> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20200309/3ae17788/attachment-0001.html>