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

Brett Nemeroff brett at nemeroff.com
Mon Mar 9 19:34:38 EST 2020 

Hey Calvin,
Glad that helped. I'd need to see how you fixed res.c to know why that
isn't working. However, most likely it's because of a lack of proper
support of doubles. if you take a look at:
/avpops/avpops_db.c

You'll see in there that it checks the AVP type and there is no type for
DB_DOUBLE from what I can see. I'm surprised this hasn't come up more
often.

I don't think the fix would be too hard, at least to make it more usable.
You can see the handing in avpops_db isn't super. It's basically coheresing
the returned values to an INT or a STRING. You could do the same by asking
for a CAST or having the function cast the value before returning (ie: if
your SP returned only strings and integers, you would not see this issue.)
-Brett






On Mon, Mar 9, 2020 at 2:23 PM Calvin Ellison <calvin.ellison at voxox.com>
wrote:

> Updating the C code per Brett's suggestion resolved that specific error:
>
> DBG:db_mysql:db_mysql_str2val: converting DOUBLE [0.0018]
>
> Now there is a warning when trying to print that avp:
>
> WARNING:avpops:db_query_avp_print_results: Unknown type 2
>
> I don't really care about this particular variable, but if the same issue
> were to come up again in another context then what fix is needed to print
> this as a string?
>
> 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]
>
>
> On Mon, Mar 9, 2020 at 11:16 AM Ben Newlin <Ben.Newlin at genesys.com> wrote:
>
>> 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>
>> 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
>> +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: Image removed by sender. Voxox]
>>
>>
>>
>>
>>
>> On Sun, Mar 8, 2020 at 11:33 PM Brett Nemeroff <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>
>> 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
>> <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: Image removed by sender. Voxox]
>>
>> _______________________________________________
>> Users mailing list
>> Users at lists.opensips.org
>> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>>
>> _______________________________________________
>> Users mailing list
>> Users at lists.opensips.org
>> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>>
>> _______________________________________________
> 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/8bfa845f/attachment-0001.html>

More information about the Users mailing list