SQL fields not lining up

[Fajar A. Nugraha]

On Sun, Oct 20, 2013 at 10:19 AM, Scott Pettit - Vorco <SPettit at vorco.net>wrote:

> Hi all,
>
> Running FreeRADIUS Version 2.1.10.
>
> I have a happily working FreeRADIUS + mySQL backend setup, however in the
> radacct table it seems FreeRADIUS just dumps fields in the order of what
> it has.
>
>
The default setup doesn't do that


> For example I store nastxspeed and nasrxspeed and disccauseext.
>
>
It's not in the default setup. Someone modified that.


> Some sessions end and store the tx speed, rx speed and cause in the
> correct field, but other sessions I end up with the remote-id in the tx
> speed field, then tx speed in the rx field, rx speed in the disccauseext
> field.
>
>
Someone modified both the schemas and queries, and they messed up your
config.

A correct sql query (e.g. in sql/mysql/dialup.conf) would look like this
(taken from the default)

        accounting_stop_query_alt = " \
          INSERT INTO ${acct_table2} \
            (acctsessionid, acctuniqueid, username, \
             realm, nasipaddress, nasportid, \
             nasporttype, acctstarttime, acctstoptime, \
             acctsessiontime, acctauthentic, connectinfo_start, \
             connectinfo_stop, acctinputoctets, acctoutputoctets, \
             calledstationid, callingstationid, acctterminatecause, \
             servicetype, framedprotocol, framedipaddress, \
             acctstartdelay, acctstopdelay) \
          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', \
             DATE_SUB('%S', \
                 INTERVAL (%{%{Acct-Session-Time}:-0} + \
                 %{%{Acct-Delay-Time}:-0}) SECOND), \
             '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', \
             '%{Connect-Info}', \
             '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
             '%{%{Acct-Input-Octets}:-0}', \
             '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
             '%{%{Acct-Output-Octets}:-0}', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', \
             '%{Acct-Terminate-Cause}', \
             '%{Service-Type}', '%{Framed-Protocol}',
'%{Framed-IP-Address}', \
             '0', '%{%{Acct-Delay-Time}:-0}')"


What does your queries look like? For example, is it possible that you
ommit field names in the queries? (e.g INSERT INTO  ${acct_table2} VALUES
...)

Is this something I can fix by getting FreeRADIUS to somehow explicitly
> put each attribute in the appropriate field?
>
>
Get someone who understands sql to write the queries.

-- 
Fajar
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20131020/6a3228db/attachment-0001.html>