DB Value Lookup Issue

[Alan DeKok]

On Jun 10, 2020, at 3:22 PM, Gabriel Marais <gabriel.j.marais at gmail.com> wrote,
> 
> 
> I am currently trying to resolve an issue with calculating the
> difference between the value of acctinputoctets and acctoutputoctets
> from a MySQL lookup and the latest interim update received from the
> carrier.
> 
> My calculation is currently in the preacct section which looks like this:-
> 
> preacct {
>        preprocess
>                if("%{sql:SELECT count(*) from radacct where
> acctsessionid = '%{Acct-Session-Id}'}" >= 1) {
> 
>                update request {
> 
>        Acct-Diff = "%{sql:SELECT greatest(0, ('%{Acct-Input-Octets}'
> + '%{Acct-Output-Octets}') - (acctinputoctets + acctoutputoctets)) as
> diff from radacct where acctsessionid = '%{Acct-Session-Id}'}"

  You're doing the math in an SQL statement which is fine, but it's likely to use 32-bit integers by default.

> The query works fine, until the values from the radacct table
> (acctinputoctets + acctoutputoctets) are greater than 4 294 967 296
> for the particular acctsessionid. When the value is larger than that
> we get 0. Under that we get the actual difference.

  Yes.  The solution is to ensure that the integers are cast to 64 bits when doing the math.  You'll have to check your SQL documentation to see how to do that.

> I changed the dictionary entry for Acct-Diff from Integer to String.
> The calculation works again until the number is greater than
> 4294967296.

  Then the 4B limit is due to SQL.  If you want Acct-Diff to *also* store more than 4G of counters, make it a "integer64" data type.  That's 64 bits, and should be more than large enough.

  Alan DeKok.