MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

classic Classic list List threaded Threaded
28 messages Options
12
Reply | Threaded
Open this post in threaded view
|

MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Calvin Ellison
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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Voxox


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users

numberdata.pcap (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Voxox

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Calvin Ellison
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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Voxox


On Sun, Mar 8, 2020 at 11:33 PM Brett Nemeroff <[hidden email]> 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 <[hidden email]> 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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Voxox

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Brett Nemeroff
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 <[hidden email]> 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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Voxox


On Sun, Mar 8, 2020 at 11:33 PM Brett Nemeroff <[hidden email]> 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 <[hidden email]> 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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Voxox

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> on behalf of Brett Nemeroff <[hidden email]>
Reply-To: OpenSIPS users mailling list <[hidden email]>
Date: Monday, March 9, 2020 at 1:20 PM
To: Calvin Ellison <[hidden email]>, OpenSIPS users mailling list <[hidden email]>
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 <[hidden email]> 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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

 

 

On Sun, Mar 8, 2020 at 11:33 PM Brett Nemeroff <[hidden email]> 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 <[hidden email]> 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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Calvin Ellison
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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Voxox


On Mon, Mar 9, 2020 at 11:16 AM Ben Newlin <[hidden email]> 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 <[hidden email]> on behalf of Brett Nemeroff <[hidden email]>
Reply-To: OpenSIPS users mailling list <[hidden email]>
Date: Monday, March 9, 2020 at 1:20 PM
To: Calvin Ellison <[hidden email]>, OpenSIPS users mailling list <[hidden email]>
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 <[hidden email]> 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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Image removed by sender. Voxox

 

 

On Sun, Mar 8, 2020 at 11:33 PM Brett Nemeroff <[hidden email]> 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 <[hidden email]> 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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Image removed by sender. Voxox

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Brett Nemeroff
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 <[hidden email]> 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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Voxox


On Mon, Mar 9, 2020 at 11:16 AM Ben Newlin <[hidden email]> 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 <[hidden email]> on behalf of Brett Nemeroff <[hidden email]>
Reply-To: OpenSIPS users mailling list <[hidden email]>
Date: Monday, March 9, 2020 at 1:20 PM
To: Calvin Ellison <[hidden email]>, OpenSIPS users mailling list <[hidden email]>
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 <[hidden email]> 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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Image removed by sender. Voxox

 

 

On Sun, Mar 8, 2020 at 11:33 PM Brett Nemeroff <[hidden email]> 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 <[hidden email]> 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
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Image removed by sender. Voxox

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Calvin Ellison
In reply to this post by Calvin Ellison
Copying Brett's reply back to the list. I deleted some of the previous conversation to avoid "message too large".

I also noticed DB_DOUBLE is missing from /avpops/avpops_db.c but I don't know C well enough to do the needful. Do you know what should be done to make it a string?

Here's my updated /db_mysql/res.c. I did as you suggested and moved MYSQL_TYPE_NEWDECIMAL along with its #if condition to just after  MYSQL_TYPE_FLOAT so it is DB_DOUBLE not DB_INT. It looks like the same change is needed for MYSQL_TYPE_DECIMAL since this can also be non-integer, but I didn't touch that.

                switch(fields[col].type) {
                        case MYSQL_TYPE_TINY:
                        case MYSQL_TYPE_SHORT:
                        case MYSQL_TYPE_LONG:
                        case MYSQL_TYPE_INT24:
                        case MYSQL_TYPE_DECIMAL:
                        case MYSQL_TYPE_TIMESTAMP:
                                LM_DBG("use DB_INT result type\n");
                                RES_TYPES(_r)[col] = DB_INT;
                                break;

                        case MYSQL_TYPE_FLOAT:
                        #if MYSQL_VERSION_ID > 49999
                        case MYSQL_TYPE_NEWDECIMAL:
                        #endif
                        case MYSQL_TYPE_DOUBLE:
                                LM_DBG("use DB_DOUBLE result type\n");
                                RES_TYPES(_r)[col] = DB_DOUBLE;
                                break;


Regards,

Calvin Ellison
Senior Voice Operations Engineer
[hidden email]

On Mon, Mar 9, 2020 at 2:50 PM Brett Nemeroff <[hidden email]> wrote:
Hey Calvin, my reply to you got blocked by the list auto-moderator for being too large. I have no idea why? Here’s my reply: 

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.)


On Mon, Mar 9, 2020 at 2:23 PM Calvin Ellison <[hidden email]> 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
[hidden email]


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Brett Nemeroff
Calvin,

If you look at the code blocks, I’d start by mirroring one of the string type conversion code blocks and see how you do. You should see it repeated a bunch of times (in avpops_db.c). I’m away from the code right now, so I can only be of limited help at the moment. 

-Brett

On Mon, Mar 9, 2020 at 5:25 PM Calvin Ellison <[hidden email]> wrote:
Copying Brett's reply back to the list. I deleted some of the previous conversation to avoid "message too large".

I also noticed DB_DOUBLE is missing from /avpops/avpops_db.c but I don't know C well enough to do the needful. Do you know what should be done to make it a string?

Here's my updated /db_mysql/res.c. I did as you suggested and moved MYSQL_TYPE_NEWDECIMAL along with its #if condition to just after  MYSQL_TYPE_FLOAT so it is DB_DOUBLE not DB_INT. It looks like the same change is needed for MYSQL_TYPE_DECIMAL since this can also be non-integer, but I didn't touch that.

                switch(fields[col].type) {
                        case MYSQL_TYPE_TINY:
                        case MYSQL_TYPE_SHORT:
                        case MYSQL_TYPE_LONG:
                        case MYSQL_TYPE_INT24:
                        case MYSQL_TYPE_DECIMAL:
                        case MYSQL_TYPE_TIMESTAMP:
                                LM_DBG("use DB_INT result type\n");
                                RES_TYPES(_r)[col] = DB_INT;
                                break;

                        case MYSQL_TYPE_FLOAT:
                        #if MYSQL_VERSION_ID > 49999
                        case MYSQL_TYPE_NEWDECIMAL:
                        #endif
                        case MYSQL_TYPE_DOUBLE:
                                LM_DBG("use DB_DOUBLE result type\n");
                                RES_TYPES(_r)[col] = DB_DOUBLE;
                                break;


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Liviu Chircu
In reply to this post by Calvin Ellison
On 10.03.2020 00:25, Calvin Ellison wrote:
> I also noticed DB_DOUBLE is missing from /avpops/avpops_db.c but I
> don't know C well enough to do the needful. Do you know what should be
> done to make it a string?
>
> Here's my updated /db_mysql/res.c. I did as you suggested and
> moved MYSQL_TYPE_NEWDECIMAL along with its #if condition to just after
> MYSQL_TYPE_FLOAT so it is DB_DOUBLE not DB_INT. It looks like the same
> change is needed for MYSQL_TYPE_DECIMAL since this can also be
> non-integer, but I didn't touch that.

Hello, Calvin/Brett!

You guys are absolutely right, that definitely looks like a bug. 
Calvin, can you
please apply the below patch on a clean OpenSIPS tree and let me know if it
fixes your problem?  Thanks!

git apply <(base64 -d <<EOF
ZGlmZiAtLWdpdCBhL21vZHVsZXMvYXZwb3BzL2F2cG9wc19kYi5jIGIvbW9kdWxlcy9hdnBvcHMv
YXZwb3BzX2RiLmMKaW5kZXggNGZjNzQ3ZmI2Li40Yjk1M2U0ZWQgMTAwNjQ0Ci0tLSBhL21vZHVs
ZXMvYXZwb3BzL2F2cG9wc19kYi5jCisrKyBiL21vZHVsZXMvYXZwb3BzL2F2cG9wc19kYi5jCkBA
IC01NDQsNiArNTQ0LDEyIEBAIGludCBkYl9xdWVyeV9hdnBfcHJpbnRfcmVzdWx0cyhzdHJ1Y3Qg
c2lwX21zZyAqbXNnLCBjb25zdCBkYl9yZXNfdCAqZGJfcmVzLAogCQkJCQlhdnBfdmFsLm4gPQog
CQkJCQkJKGludClSRVNfUk9XUyhkYl9yZXMpW2ldLnZhbHVlc1tqXS52YWwuYmlnaW50X3ZhbDsK
IAkJCQlicmVhazsKKwkJCQljYXNlIERCX0RPVUJMRToKKwkJCQkJYXZwX3R5cGUgfD0gQVZQX1ZB
TF9TVFI7CisJCQkJCWF2cF92YWwucy5zID0gZG91YmxlMnN0cigKKwkJCQkJICAgICAgICBSRVNf
Uk9XUyhkYl9yZXMpW2ldLnZhbHVlc1tqXS52YWwuZG91YmxlX3ZhbCwKKwkJCQkJICAgICAgICAm
YXZwX3ZhbC5zLmxlbik7CisJCQkJYnJlYWs7CiAJCQkJZGVmYXVsdDoKIAkJCQkJTE1fV0FSTigi
VW5rbm93biB0eXBlICVkXG4iLAogCQkJCQkJUkVTX1JPV1MoZGJfcmVzKVtpXS52YWx1ZXNbal0u
dHlwZSk7CmRpZmYgLS1naXQgYS9tb2R1bGVzL2RiX215c3FsL3Jlcy5jIGIvbW9kdWxlcy9kYl9t
eXNxbC9yZXMuYwppbmRleCBiMjRjZmVjODkuLjE0NTBhNmQ0NSAxMDA2NDQKLS0tIGEvbW9kdWxl
cy9kYl9teXNxbC9yZXMuYworKysgYi9tb2R1bGVzL2RiX215c3FsL3Jlcy5jCkBAIC03NiwxMCAr
NzYsNiBAQCBpbnQgZGJfbXlzcWxfZ2V0X2NvbHVtbnMoY29uc3QgZGJfY29uX3QqIF9oLCBkYl9y
ZXNfdCogX3IpCiAJCQljYXNlIE1ZU1FMX1RZUEVfU0hPUlQ6CiAJCQljYXNlIE1ZU1FMX1RZUEVf
TE9ORzoKIAkJCWNhc2UgTVlTUUxfVFlQRV9JTlQyNDoKLQkJCWNhc2UgTVlTUUxfVFlQRV9ERUNJ
TUFMOgotCQkJI2lmIE1ZU1FMX1ZFUlNJT05fSUQgPiA0OTk5OQotCQkJY2FzZSBNWVNRTF9UWVBF
X05FV0RFQ0lNQUw6Ci0JCQkjZW5kaWYKIAkJCWNhc2UgTVlTUUxfVFlQRV9USU1FU1RBTVA6CiAJ
CQkJTE1fREJHKCJ1c2UgREJfSU5UIHJlc3VsdCB0eXBlXG4iKTsKIAkJCQlSRVNfVFlQRVMoX3Ip
W2NvbF0gPSBEQl9JTlQ7CkBAIC04Nyw2ICs4MywxMCBAQCBpbnQgZGJfbXlzcWxfZ2V0X2NvbHVt
bnMoY29uc3QgZGJfY29uX3QqIF9oLCBkYl9yZXNfdCogX3IpCiAKIAkJCWNhc2UgTVlTUUxfVFlQ
RV9GTE9BVDoKIAkJCWNhc2UgTVlTUUxfVFlQRV9ET1VCTEU6CisJCQljYXNlIE1ZU1FMX1RZUEVf
REVDSU1BTDoKKwkJCSNpZiBNWVNRTF9WRVJTSU9OX0lEID4gNDk5OTkKKwkJCWNhc2UgTVlTUUxf
VFlQRV9ORVdERUNJTUFMOgorCQkJI2VuZGlmCiAJCQkJTE1fREJHKCJ1c2UgREJfRE9VQkxFIHJl
c3VsdCB0eXBlXG4iKTsKIAkJCQlSRVNfVFlQRVMoX3IpW2NvbF0gPSBEQl9ET1VCTEU7CiAJCQkJ
YnJlYWs7CmRpZmYgLS1naXQgYS91dC5oIGIvdXQuaAppbmRleCA3ODIwZTZlODkuLmM0ZDQ4MWQ2
MyAxMDA2NDQKLS0tIGEvdXQuaAorKysgYi91dC5oCkBAIC0yODgsNiArMjg4LDE3IEBAIHN0YXRp
YyBpbmxpbmUgY2hhciogc2ludDJzdHIobG9uZyBsLCBpbnQqIGxlbikKIAlyZXR1cm4gcDsKIH0K
IAorc3RhdGljIGlubGluZSBjaGFyKiBkb3VibGUyc3RyKGRvdWJsZSBkLCBpbnQqIGxlbikKK3sK
KwlzdGF0aWMgaW50IGJ1ZjsKKworCWJ1ZiA9IChidWYgKyAxKSAlIElOVDJTVFJfQlVGX05POwor
CSpsZW4gPSBzbnByaW50ZihpbnQyc3RyX2J1ZltidWZdLCBJTlQyU1RSX01BWF9MRU4gLSAxLCAi
JWxmIiwgZCk7CisJaW50MnN0cl9idWZbYnVmXVsqbGVuXSA9ICdcMCc7CisKKwlyZXR1cm4gaW50
MnN0cl9idWZbYnVmXTsKK30KKwogCiAvKiBmYXN0ZXIgbWVtY2hyIHZlcnNpb24gKi8KIHN0YXRp
YyBpbmxpbmUgY2hhciogcV9tZW1jaHIoY2hhciogcCwgaW50IGMsIHVuc2lnbmVkIGludCBzaXpl
KQo=
EOF
)

Best regards,

--
Liviu Chircu
www.twitter.com/liviuchircu | www.opensips-solutions.com

OpenSIPS Summit, Amsterdam, May 2020
   www.opensips.org/events
OpenSIPS Bootcamp, Miami, March 2020
   www.opensips.org/training


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Brett Nemeroff
Liviu, 
Thanks for the quick fix! I can’t believe this hasn’t been more of an issue. But then again, it may be a weird thing to want a double in the script context since you can’t or at least shouldn’t be doing a lot of high level stuff there. 

Calvin / Liviu,
I haven’t tested this fix yet, but I reviewed it. I just wanted to point out for the group that this converts a DOUBLE as received form the Database into a STRING for the script context in an AVP. This isn’t really a problem unless you are going to math it or push it into something else without casting it. If you, for example, wanted to pull a rate per minute and push it to a SIP header, this would work great for that.

-Brett



On Tue, Mar 10, 2020 at 10:57 AM Liviu Chircu <[hidden email]> wrote:
On 10.03.2020 00:25, Calvin Ellison wrote:
> I also noticed DB_DOUBLE is missing from /avpops/avpops_db.c but I
> don't know C well enough to do the needful. Do you know what should be
> done to make it a string?
>
> Here's my updated /db_mysql/res.c. I did as you suggested and
> moved MYSQL_TYPE_NEWDECIMAL along with its #if condition to just after
> MYSQL_TYPE_FLOAT so it is DB_DOUBLE not DB_INT. It looks like the same
> change is needed for MYSQL_TYPE_DECIMAL since this can also be
> non-integer, but I didn't touch that.

Hello, Calvin/Brett!

You guys are absolutely right, that definitely looks like a bug. 
Calvin, can you
please apply the below patch on a clean OpenSIPS tree and let me know if it
fixes your problem?  Thanks!

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Liviu Chircu
On 10.03.2020 18:39, Brett Nemeroff wrote:
> This isn’t really a problem unless you are going to math it or push it
> into something else without casting it.

I agree, but at script level, there is no support for manipulating
"long" integers,
let along "double" precision integers.  So casting the "double" number
to a string
within avpops and returning it to the script seemed like a pretty good
workaround.

Additionally, this doesn't stop you from doing floating point math in
the script using mathops [1]!

[1]: https://opensips.org/docs/modules/3.1.x/mathops.html

--
Liviu Chircu
www.twitter.com/liviuchircu | www.opensips-solutions.com

OpenSIPS Summit, Amsterdam, May 2020
   www.opensips.org/events


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Brett Nemeroff
Totally agree that this is the right thing to do. I just wanted the list to know it’s a STRING. I don’t think it’s obvious.

Thanks again!!


On Tue, Mar 10, 2020 at 11:45 AM Liviu Chircu <[hidden email]> wrote:
On 10.03.2020 18:39, Brett Nemeroff wrote:
> This isn’t really a problem unless you are going to math it or push it
> into something else without casting it.

I agree, but at script level, there is no support for manipulating
"long" integers,
let along "double" precision integers.  So casting the "double" number
to a string
within avpops and returning it to the script seemed like a pretty good
workaround.

Additionally, this doesn't stop you from doing floating point math in
the script using mathops [1]!

[1]: https://opensips.org/docs/modules/3.1.x/mathops.html

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Calvin Ellison
The patch appears to be working as intended with regards to storing and later using the decimal value as a string:

Mar 10 09:50:15 localhost /usr/sbin/opensips[27541]: DBG:db_mysql:db_mysql_get_columns: RES_NAMES(0x7f8ecd2b5d68)[14]=[delay]
Mar 10 09:50:15 localhost /usr/sbin/opensips[27541]: DBG:db_mysql:db_mysql_get_columns: use DB_DOUBLE result type
Mar 10 09:50:15 localhost /usr/sbin/opensips[27541]: DBG:db_mysql:db_mysql_str2val: converting DOUBLE [0.0019]

However, when printed it appears as "0.001900". If that his how thing string would be passed to mathops, etc., it would cause problems for anyone interested in significant digits. E.g this could affect the precision of billing calculations. At this point, I'm just happy the Error and Warning are gone and the value is useful at all, but if someone wanted to take this a step further, they could get the precision of the value from the SQL data. Here's an example MySQL data packet with "Decimals: 4":

MySQL Protocol
    Packet Length: 27
    Packet Number: 16
    Catalog: def
    Database:
    Table:
    Original table:
    Name: delay
    Original name:
    Charset number: binary COLLATE binary (63)
    Length: 26
    Type: FIELD_TYPE_NEWDECIMAL (246)
    Flags: 0x0080
    Decimals: 4

Regards,

Calvin Ellison
Senior Voice Operations Engineer
[hidden email]
+1 (213) 285-0555

-----------------------------------------------
voxox.com 
5825 Oberlin Drive, Suite 5
San Diego, CA 92121

Voxox


On Tue, Mar 10, 2020 at 9:49 AM Brett Nemeroff <[hidden email]> wrote:
Totally agree that this is the right thing to do. I just wanted the list to know it’s a STRING. I don’t think it’s obvious.

Thanks again!!


On Tue, Mar 10, 2020 at 11:45 AM Liviu Chircu <[hidden email]> wrote:
On 10.03.2020 18:39, Brett Nemeroff wrote:
> This isn’t really a problem unless you are going to math it or push it
> into something else without casting it.

I agree, but at script level, there is no support for manipulating
"long" integers,
let along "double" precision integers.  So casting the "double" number
to a string
within avpops and returning it to the script seemed like a pretty good
workaround.

Additionally, this doesn't stop you from doing floating point math in
the script using mathops [1]!

[1]: https://opensips.org/docs/modules/3.1.x/mathops.html
_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Liviu Chircu
On 10.03.2020 19:07, Calvin Ellison wrote:
> However, when printed it appears as "0.001900". If that his how thing
> string would be passed to mathops, etc., it would cause problems for
> anyone interested in significant digits.

Indeed, the "%lf" format specified in C truncates double numbers down to
6 decimal places.
Shall I force it to always print 8 decimals before I push the patch
upstream?  Or maybe 10?
What value do you consider to be solid enough here for a default?  Many
thanks!

--
Liviu Chircu
www.twitter.com/liviuchircu | www.opensips-solutions.com

OpenSIPS Summit, Amsterdam, May 2020
   www.opensips.org/events


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Brett Nemeroff
Liviu,
My preference would be to default to 8, but make it defined in the header file for those who want to recompile it. 6 is ok, it’s usually the minimum precision that is acceptable. 

Thanks!!

On Tue, Mar 10, 2020 at 12:19 PM Liviu Chircu <[hidden email]> wrote:
On 10.03.2020 19:07, Calvin Ellison wrote:
> However, when printed it appears as "0.001900". If that his how thing
> string would be passed to mathops, etc., it would cause problems for
> anyone interested in significant digits.

Indeed, the "%lf" format specified in C truncates double numbers down to
6 decimal places.
Shall I force it to always print 8 decimals before I push the patch
upstream?  Or maybe 10?
What value do you consider to be solid enough here for a default?  Many
thanks!

--
Liviu Chircu
www.twitter.com/liviuchircu | www.opensips-solutions.com

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Calvin Ellison
On Tue, Mar 10, 2020 at 10:31 AM Brett Nemeroff <[hidden email]> wrote:
Liviu,
My preference would be to default to 8, but make it defined in the header file for those who want to recompile it. 6 is ok, it’s usually the minimum precision that is acceptable. 

My comment about significant digits was with regards to having extraneous trailing zeros, regardless of how many. Depending on the other value used in a calculation, 0.0019 versus 0.001900 might dictate rounding at a different number of decimal places. This is why I suggested grabbing the number of decimal places from the SQL response. My newbie googling of C printf/scanf indicates you can pass the number of decimal places via the ".precision" format specifier as a value or reference. There's a great answer on the topic here: https://stackoverflow.com/a/19897395 and some followup on C display precision versus mathematical precision and significant digits.

It's probably a corner case. This stuff was pounded into me during high school chemistry class :)

Regards,

Calvin Ellison
Senior Voice Operations Engineer
[hidden email]


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Brett Nemeroff
Actually, I think that may be a good idea Liviu,
Can you inspect the DB type to derive a precision for the STRING format? Then maybe default to 8 if you can't derive it? I think that gives us the best of everything. But it does require a column datatype inspection. What do you think?
-Brett

On Tue, Mar 10, 2020 at 1:50 PM Calvin Ellison <[hidden email]> wrote:
On Tue, Mar 10, 2020 at 10:31 AM Brett Nemeroff <[hidden email]> wrote:
Liviu,
My preference would be to default to 8, but make it defined in the header file for those who want to recompile it. 6 is ok, it’s usually the minimum precision that is acceptable. 

My comment about significant digits was with regards to having extraneous trailing zeros, regardless of how many. Depending on the other value used in a calculation, 0.0019 versus 0.001900 might dictate rounding at a different number of decimal places. This is why I suggested grabbing the number of decimal places from the SQL response. My newbie googling of C printf/scanf indicates you can pass the number of decimal places via the ".precision" format specifier as a value or reference. There's a great answer on the topic here: https://stackoverflow.com/a/19897395 and some followup on C display precision versus mathematical precision and significant digits.


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Liviu Chircu
On 10.03.2020 20:55, Brett Nemeroff wrote:
> Can you inspect the DB type to derive a precision for the STRING
> format? Then maybe default to 8 if you can't derive it?

We probably could, but looking at db/db_val.h +75, you can see that the
generic db_val_t type has no support for storing the precision.  So we'd
have to:

* extend this struct so it includes "precision digits" for floating
point types.  Hopefully with 0 side-effects!
* add some handy get/set macros for the above
* for the MySQL driver: inspect the column properties (it has to be
possible) and extract/store the decimal digits into the result
* for other SQL drivers: feature not implemented for now?!
* in avpops, make use of the db_val_t precision digits and finally use
them to properly format the output data

So this seems like too much of a hassle for a minor (if any)
improvement, at least in my opinion.  There are other tasks far more
important to be done for 3.1 instead of this little quirk.

PS: Yesterday, I already pushed the 8-digits patch upstream anyway :)

Best regards,

--
Liviu Chircu
www.twitter.com/liviuchircu | www.opensips-solutions.com

OpenSIPS Summit, Amsterdam, May 2020
   www.opensips.org/events


_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Type: FIELD_TYPE_NEWDECIMAL (246) uses DB_INT result type but should use float

Brett Nemeroff
Liviu,
In my opinion, way too much work for almost no value. I wouldn’t worry about it. I think the 8 digits of precision is ok for now really. Certainly meets my needs.  

As a middle ground, instead of extending db_val_t, what if you used the cues from the DB driver to enforce the digits of precision while casting to a STRING. The problem with how you do it now is that you assume the precision which might actually be wrong. 

-Brett

On Wed, Mar 11, 2020 at 10:40 AM Liviu Chircu <[hidden email]> wrote:
On 10.03.2020 20:55, Brett Nemeroff wrote:
> Can you inspect the DB type to derive a precision for the STRING
> format? Then maybe default to 8 if you can't derive it?

We probably could, but looking at db/db_val.h +75, you can see that the
generic db_val_t type has no support for storing the precision.  So we'd
have to:

* extend this struct so it includes "precision digits" for floating
point types.  Hopefully with 0 side-effects!
* add some handy get/set macros for the above
* for the MySQL driver: inspect the column properties (it has to be
possible) and extract/store the decimal digits into the result
* for other SQL drivers: feature not implemented for now?!
* in avpops, make use of the db_val_t precision digits and finally use
them to properly format the output data

So this seems like too much of a hassle for a minor (if any)
improvement, at least in my opinion.  There are other tasks far more
important to be done for 3.1 instead of this little quirk.

PS: Yesterday, I already pushed the 8-digits patch upstream anyway :)

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
12