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

[Ben Newlin]

I’m pretty sure OpenSIPS doesn’t support floating point numbers in script variables, so I don’t think it’s a bug. Even if you change the C code to make it a Float the $var and $avp concepts are only string or int. There is a MATHOPS module that offers some floating point math, but it’s based on using string variables.

Ben Newlin

From: Users <users-bounces at lists.opensips.org> on behalf of Brett Nemeroff <brett at nemeroff.com>
Reply-To: OpenSIPS users mailling list <users at lists.opensips.org>
Date: Monday, March 9, 2020 at 1:20 PM
To: Calvin Ellison <calvin.ellison at voxox.com>, OpenSIPS users mailling list <users at lists.opensips.org>
Subject: Re: [OpenSIPS-Users] MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Can you just statically put a integer in there? Like say, 0?

I actually think this is a bug. You are using 2.4.7? I don’t see this fixed in newer versions.

The bug is on Line 81 of res.c. It incorrectly assumes that MYSQL_TYPE_NEWDECIMAL is an INT. This should be easy for you to attempt to fix and recompile. I’m not entirely sure it’ll work, but it’s worth a shot. I’d move that data type down to the FLOAT block and give it a whirl.

Good luck,
Brett


On Mon, Mar 9, 2020 at 12:00 PM Calvin Ellison <calvin.ellison at voxox.com<mailto:calvin.ellison at voxox.com>> wrote:
The problem, as I see it, is that the MariaDB server is indicating this field is a floating-point (FIELD_TYPE_NEWDECIMAL), but OpenSIPS is casting that integer (DB_INT).

I don't really need the delay field but I have no way to exclude it from the result and avp_db_query want every field mapped to an avp.

Can avp_db_query force a translation on the result before storing it?

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: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<mailto:calvin.ellison at voxox.com>
+1 (213) 285-0555

-----------------------------------------------
voxox.com<http://www.voxox.com/>
5825 Oberlin Drive, Suite 5<https://www.google.com/maps/search/5825+Oberlin+Drive,+Suite+5+San+Diego,+CA+92121?entry=gmail&source=g>
San Diego, CA 92121<https://www.google.com/maps/search/5825+Oberlin+Drive,+Suite+5+San+Diego,+CA+92121?entry=gmail&source=g>
[Image removed by sender. Voxox]


On Sun, Mar 8, 2020 at 11:33 PM Brett Nemeroff <brett at nemeroff.com<mailto:brett at nemeroff.com>> wrote:

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<mailto: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<http://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<mailto:calvin.ellison at voxox.com>
+1 (213) 285-0555

-----------------------------------------------
voxox.com<http://www.voxox.com/>
5825 Oberlin Drive, Suite 5<https://www.google.com/maps/search/5825+Oberlin+Drive,+Suite+5+San+Diego,+CA+92121?entry=gmail&source=g>
San Diego, CA 92121<https://www.google.com/maps/search/5825+Oberlin+Drive,+Suite+5+San+Diego,+CA+92121?entry=gmail&source=g>
[Image removed by sender. Voxox]
_______________________________________________
Users mailing list
Users at lists.opensips.org<mailto:Users at lists.opensips.org>
http://lists.opensips.org/cgi-bin/mailman/listinfo/users<http://lists.opensips.org/cgi-bin/mailman/listinfo/users>
_______________________________________________
Users mailing list
Users at lists.opensips.org<mailto:Users at lists.opensips.org>
http://lists.opensips.org/cgi-bin/mailman/listinfo/users<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/e663ada3/attachment-0001.html>