> From: Alexander M. Pravking
> Sent: Sunday, 13 July 2003 11:32 PM
> On Sun, Jul 13, 2003 at 03:46:08AM +1000, Paul Hampson wrote:
> > Just looking at some of my records, would I be right in
> > observing that the default *sql.conf files don't account
> > for Acct-Output-Gigawords and Acct-Input-Gigawords?
> > If I'm right and it's not being accounted for, is there any
> > reason I wouldn't want to modify the query to be
> > SET AcctInputOctets = %{Acct-Input-Octets} +
> > (%{Acct-Input-Gigawords} * 4294967296)
>
> Or SET AcctInputOctets
> = (cast(%{Acct-Input-Gigawords:-0} as <64-bit-integer>) << 32)
> + %{Acct-Input-Octets:-0}
> if binary shift is supported by DBMS.
I don't see casting in mySQL, but apparently all arithmatic _is_ done
as bigint (64 bit) although there's a warning in the 3.23 manual (dunno
about 4):
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is -9223372036854775808 to 9223372036854775807.
The
unsigned range is 0 to 18446744073709551615. Some things you should be aware about
BIGINT columns:
+ As all arithmetic is done using signed BIGINT or DOUBLE values, so you
shouldn't use
unsigned big integers larger than 9223372036854775807 (63 bits) except with bit
functions! If you do that, some of the last digits in the result may be wrong
because of
rounding errors when converting the BIGINT to a DOUBLE.
+ You can always store an exact integer value in a BIGINT column by storing it
as a string,
as there is in this case there will be no intermediate double representation.
+ `-', `+', and `*' will use BIGINT arithmetic when both arguments are INTEGER
values! This
means that if you multiply two big integers (or results from functions that
return integers)
you may get unexpected results when the result is larger than
9223372036854775807.
The bit about the string makes me wish rlm_expr worked with 64-bit values 'cause then I
could put ''s around the %{} and it would be a safe insert. But that's crazy talk all
'round.
> However, default *sql schemas use numeric(N) fields for *Octets,
> which 1) are slow; 2) sometimes require explicit value casting;
> 3) need to be expanded to numeric(20) to avoid overflows...
> So I'll vote for second solution:
Using a numeric(N) column seems deranged as far as mySQL's
concerned, 'cause that's a floating-point value! I mean, we're
dealing with
I noticed that something in the mySQL schema became bigint
recently... I forget what it was, but I _also_ had to bigint my
NAS-Port-ID value, since I noticed that was being cropped.
Would biginting this column be evil, given that it really is one
piece of information, and is only seperated into two attributes
because Radius deals in four-byte unsigned integers?
(As far as I understand)
(Now that I look at the current mysql db schema, the Octects
columns _are_ bigint'd. So I guess this was always on the cards.)
Now that I look at it, the recent change shouldn't be bigint but
"unsigned int", since that's the same range the value in the
radius packet has... Have to go fix that, I guess... Looks like
all the int and bigint values in the mySQL db example schema
are in need of the 'unsigned' keyword. (Well, maybe not AcctStartDelay,
AcctStopDelay? Have to check the RFC for what they _are_ first)
On the other hand, the Input-Octets value _would_ be an
unsigned bigint (64 bits) since it's two 32-bit values concatenated
together.
I guess the mySQL schema and such need as much attention
as the postgresQL schema's been getting recently.
In fact, signedness in FreeRADIUS generally is on my hit-list. :-)
> > Otherwise I'll add the Gigaword columns as extra columns.
> You could put both of them into *sql.conf as an example,
> and let admins to decide themselves which one to use :)
Well, I _could_ have faith in the admins....
Is there any reason to leave the Gigaword columns _out_
of the default schema/queries, apart from breaking people who
don't update their schema and don't notice the query change...
(Which is my other vote in favour of the above, no more breakage
than already exists)
--
=========================================================
Paul "TBBle" Hampson
Bubblesworth Pty Ltd (ABN: 51 095 284 361)
[EMAIL PROTECTED]
This is a one line proof...if we start sufficiently far to the left.
-- Cambridge University Math Department
---------------------------------------------------------
Random signature generator 3.0 by Paul "TBBle" Hampson
=========================================================
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
