5 Jan
2016
5 Jan
'16
5:38 p.m.
Hi Guys,
Wonder if anyone has experienced this before, on kamailio 4.1.
I am using an sql_pvquery and it works fine, unless I pull a value from the database that
is defined as 10 digits and starts with a 3, actually its populated as 03785702370 but due
to the table it removes the leading 0 (bigint(20) unsigned).
Running a manual query in my database for a DestinationMsisdn I get;
select DestinationMsisdn,SourceMsisdn from MsisdnPoolAllocations where
PoolMsisdn='+44162489xxxx';+-------------------+--------------+| DestinationMsisdn
| SourceMsisdn |+-------------------+--------------+| 3785702370 | 4474526xxxxx
|+-------------------+--------------+
However running in the kamailio.cfg an sql_pvquery to do the same query as below;
sql_pvquery("cd","select DestinationMsisdn,SourceMsisdn from
MsisdnPoolAllocations where
PoolMsisdn='$rU'","$var(MOdest),$var(NewSourceMSISDN)");
However this returns a value of -509264926 for $var(MOdest) which should just be the
3785702370 number.
What can cause kamailio to interpret this as a negative value? Has anyone seen this
before?
Many thanks
Jon
5 Jan
5 Jan
6:51 p.m.
On Tue, Jan 05, 2016 at 03:38:58PM +0000, Jonathan Hunter wrote:
sql_pvquery("cd","select
DestinationMsisdn,SourceMsisdn from MsisdnPoolAllocations where
PoolMsisdn='$rU'","$var(MOdest),$var(NewSourceMSISDN)");
However this returns a value of -509264926 for $var(MOdest) which should just be the
3785702370 number.
What can cause kamailio to interpret this as a negative value? Has anyone seen this
before?
What you are seeing is an integer overflow, in this case you are trying
to store a number greater than 2^31 in a signed 32bit int. -509264926
(3785702370-2^32) is the correct answer if the var is a signed 32bit
int.
I treat phonenumbers as strings (both in the database and kamailio)
since I store them as E.164 with a leading + (which results in a bit
more diskspace)
If you don't need the number as int in kamailio, try casting it to a
string in the query.
8:49 p.m.
On 05/01/16 17:51, Daniel Tryba wrote:
On Tue, Jan 05, 2016 at 03:38:58PM +0000, Jonathan
Hunter wrote:
To complete, as just looked at the source -- if the bigint number
returned does not fit in 32bit size, then it is stored as string. If it
fits in 32bit, then is stored also as int. I see the code was added in
2011 by Alex Hermann.
Maybe the behavior is not that coherent, hard to predict if not knowing
what is in the db, and should be changed to be always stored as string,
then use {s.int} in config if wanted as int.
Cheers,
Daniel
--
Daniel-Constantin Mierla
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Book: SIP Routing With Kamailio - http://www.asipto.com
http://miconda.eu
sql_pvquery("cd","select
DestinationMsisdn,SourceMsisdn from MsisdnPoolAllocations where
PoolMsisdn='$rU'","$var(MOdest),$var(NewSourceMSISDN)");
However this returns a value of -509264926 for $var(MOdest) which should just be the
3785702370 number.
What can cause kamailio to interpret this as a negative value? Has anyone seen this
before?
What you are seeing is an integer overflow, in this case you are trying
to store a number greater than 2^31 in a signed 32bit int. -509264926
(3785702370-2^32) is the correct answer if the var is a signed 32bit
int.
I treat phonenumbers as strings (both in the database and kamailio)
since I store them as E.164 with a leading + (which results in a bit
more diskspace)
If you don't need the number as int in kamailio, try casting it to a
string in the query.
6 Jan
6 Jan
12:48 p.m.
Hi to both Daniels and thank you for your responses.
I understand what you are both saying.
Currently the field I am extracting the value from in the database is set to type;
bigint(20) unsigned
If I change to varchar for example it returns fine, my only issue is that the overflow
only occurs on 10 digit numbers starting with 2 or 3.
If I add a value for example 1785702370 or 7785702370 they are returned without issue
without changing the DB value type.
Thanks
Jon
To: sr-users(a)lists.sip-router.org
From: miconda(a)gmail.com
Date: Tue, 5 Jan 2016 19:49:02 +0100
Subject: Re: [SR-Users] Negative value returned when using sql_pvquery
On 05/01/16 17:51, Daniel Tryba wrote:
On Tue, Jan 05, 2016 at 03:38:58PM +0000,
Jonathan Hunter wrote:
To complete, as just looked at the source -- if the bigint number
returned does not fit in 32bit size, then it is stored as string. If it
fits in 32bit, then is stored also as int. I see the code was added in
2011 by Alex Hermann.
Maybe the behavior is not that coherent, hard to predict if not knowing
what is in the db, and should be changed to be always stored as string,
then use {s.int} in config if wanted as int.
Cheers,
Daniel
--
Daniel-Constantin Mierla
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Book: SIP Routing With Kamailio - http://www.asipto.com
http://miconda.eu
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
sr-users(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users sql_pvquery("cd","select
DestinationMsisdn,SourceMsisdn from MsisdnPoolAllocations where
PoolMsisdn='$rU'","$var(MOdest),$var(NewSourceMSISDN)");
However this returns a value of -509264926 for $var(MOdest) which should just be the
3785702370 number.
What can cause kamailio to interpret this as a negative value? Has anyone seen this
before?
What you are seeing is an integer overflow, in this case you are trying
to store a number greater than 2^31 in a signed 32bit int. -509264926
(3785702370-2^32) is the correct answer if the var is a signed 32bit
int.
I treat phonenumbers as strings (both in the database and kamailio)
since I store them as E.164 with a leading + (which results in a bit
more diskspace)
If you don't need the number as int in kamailio, try casting it to a
string in the query.
12:58 p.m.
Hello,
the negative integers have the first bit 1 in the 32bit representation
of a signed integer. So practically 2^31 (2 147 483 648) to 2^32 - 1 ( 4
294 967 296 - 1) are the negative integers, which for unsigned int
representation are the higher values. As the other Daniel said, those
numbers are converted to negative value as: N - 2^32 (where N is the
number greater than 2^31).
For your case, the best is to use mysql cast function in sql_pvquery()
and get it as string inside kamailio.cfg, no matter what is the type of
the column in the database table. See cast/convert operators/functions
in the mysql manual.
Cheers,
Daniel
On 06/01/16 11:48, Jonathan Hunter wrote:
Hi to both Daniels and thank you for your responses.
I understand what you are both saying.
Currently the field I am extracting the value from in the database is
set to type;
bigint(20) unsigned
If I change to varchar for example it returns fine, my only issue is
that the overflow only occurs on 10 digit numbers starting with 2 or 3.
If I add a value for example 1785702370 or 7785702370 they are
returned without issue without changing the DB value type.
Thanks
Jon
--
Daniel-Constantin Mierla
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Book: SIP Routing With Kamailio - http://www.asipto.com
http://miconda.eu
To: sr-users(a)lists.sip-router.org
From: miconda(a)gmail.com
Date: Tue, 5 Jan 2016 19:49:02 +0100
Subject: Re: [SR-Users] Negative value returned when using sql_pvquery
On 05/01/16 17:51, Daniel Tryba wrote:
> On Tue, Jan 05, 2016 at 03:38:58PM +0000, Jonathan Hunter wrote:
>> sql_pvquery("cd","select DestinationMsisdn,SourceMsisdn from
MsisdnPoolAllocations where
PoolMsisdn='$rU'","$var(MOdest),$var(NewSourceMSISDN)");
>> However this returns a value of
-509264926 for $var(MOdest) which
should just be the 3785702370 number.
>> What can cause kamailio to interpret
this as a negative value?
Has anyone seen this before?
> What you are seeing is an integer overflow,
in this case you are
trying
> > to store a number greater than 2^31 in a signed 32bit int. -509264926
> > (3785702370-2^32) is the correct answer if the var is a signed 32bit
> > int.
> >
> > I treat phonenumbers as strings (both in the database and kamailio)
> > since I store them as E.164 with a leading + (which results in a bit
> > more diskspace)
> >
> > If you don't need the number as int in kamailio, try casting it to a
> > string in the query.
> >
> To complete, as just looked at the source -- if the bigint number
> returned does not fit in 32bit size, then it is stored as string. If it
> fits in 32bit, then is stored also as int. I see the code was added in
> 2011 by Alex Hermann.
>
> Maybe the behavior is not that coherent, hard to predict if not knowing
> what is in the db, and should be changed to be always stored as string,
> then use {s.int} in config if wanted as int.
>
> Cheers,
> Daniel
>
> --
> Daniel-Constantin Mierla
> http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
> Book: SIP Routing With Kamailio - http://www.asipto.com
> http://miconda.eu
>
>
> _______________________________________________
> SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
> sr-users(a)lists.sip-router.org
> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
1:04 p.m.
On Wed, Jan 06, 2016 at 10:48:38AM +0000, Jonathan Hunter wrote:
Currently the field I am extracting the value from in
the database is set to type;
bigint(20) unsigned
If I change to varchar for example it returns fine, my only issue is that the overflow
only occurs on 10 digit numbers starting with 2 or 3.
If I add a value for example 1785702370 or 7785702370 they are returned without issue
without changing the DB value type.
The problem you experience occurs for all numbers >= 2147483648 (2^31)
and <= 4294967296 (2^32). Looks like the auto conversion in kamailio is
doing funky stuff in this range :) Can you verify this?
BTW You don't have to change the table, you can do (im|ex)plicit casts
in the query in kamailio. See
https://dev.mysql.com/doc/refman/5.5/en/type-conversion.html for
example.
2:13 p.m.
Hi Both,
Thank you both for the explanations.
The cast addition to the query works perfectly!
thanks very much.
Jon
Date: Wed, 6 Jan 2016 12:04:47 +0100
From: d.tryba(a)pocos.nl
To: sr-users(a)lists.sip-router.org
Subject: Re: [SR-Users] Negative value returned when using sql_pvquery
On Wed, Jan 06, 2016 at 10:48:38AM +0000, Jonathan Hunter wrote:
Currently the field I am extracting the value
from in the database is set to type;
bigint(20) unsigned
If I change to varchar for example it returns fine, my only issue is that the overflow
only occurs on 10 digit numbers starting with 2 or 3.
If I add a value for example 1785702370 or 7785702370 they are returned without issue
without changing the DB value type.
The problem you experience occurs for all numbers >= 2147483648 (2^31)
and <= 4294967296 (2^32). Looks like the auto conversion in kamailio is
doing funky stuff in this range :) Can you verify this?
BTW You don't have to change the table, you can do (im|ex)plicit casts
in the query in kamailio. See
https://dev.mysql.com/doc/refman/5.5/en/type-conversion.html for
example.
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
sr-users(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
3273
days inactive
3274
days old
6 comments
3 participants
participants (3)
-
Daniel Tryba -
Daniel-Constantin Mierla -
Jonathan Hunter