On Mon, 14 Jul 2003 12:02 pm, Paul Hampson wrote:
> > 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.
We can work together on this on the -devel list.
Changing NUMERIC to INT is one of the things I have been doing for Postgres.
I still have a few more changes to make to the postgres files but they are
mostly complete now I think. Would you mind comparing what I have done for
Postgres and seeing how much of it will work/port to MySQL??
MySQL doesnt have INET field types for instance but alot of the rest should
work.. (Dunno about the datetime calc though..)
> 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)
--
Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
