(RADIATOR) Problem w/ SQLRecoveryFile

[Jason Godsey]

I am not sure if I've found a bug or that I'm partially brain dead.

A project I'm working on is to log accounting information directly to sql, however
I don't want to lose any accounting information should the SQL server be out to lunch.

After reading through the manual again, I happened upon SQLRecoveryFile.  I'm sure my use for
it at the moment isn't how it was intended.

What I wanted was to log accounting info into a text file in sql friendly format, so I could import
and test the SQL server while not disturbing radiator.

What actually happens is a little strange, and I think it's tied to how Radiator escapes mysql strings.

(just to mention this up front, the AcctFailedLogFileName always logs the proper data)

First run, I had dummy DBSource where not only did I define an invalid user/pass but also a bad 

database.  I realize that most people would never do this intentionally, but doesn't this also simulate 

a very bad database error?  The exact case this would actually be handy.

I will include some logs here of what happens when radiator tries talking to dummied up/non existant 

mysql database.

radius, radiator, password:  Access denied for user: 'radiator at localhost' (Using password: YE
S)
Mon Mar 22 21:00:06 2004: ERR: Could not connect to any SQL database. Request is ignored. Bac
king off for 600 seconds
Mon Mar 22 21:00:06 2004: DEBUG: do query is: 'insert into _RADUSAGE200403 (ACCTDELAYTIME,ACC
TINPUTOCT,ACCTOUTPUTOCT,ACCTSESSIONID,ACCTSESSTIME,ACCTSTATUSTYPE,MYIP,NASIDENTIFIER,NASPORT,
TIME_STAMP,USERNAME) values (0,1658,229,,7,,,,101081014,1080018006,)':

Here you can see the only values that actually make it to the query are integer.

Now, when I made a login/password and create an empty table for radiator to talk to:

Mon Mar 22 21:46:32 2004: ERR: do failed for 'insert into _RADUSAGE200403 (ACCTSESSIONID,ACCT
SESSTIME,ACCTSTATUSTYPE,CALLEDID,CALLERID,FRAMEDIPADDR,MYIP,NASIDENTIFIER,NASIPADDR,NASPORT,T
IME_STAMP,USERNAME) values ('447890528',7,'Stop','3608990616','3608553319','66.42.42.40','66.
218.206.44','apx8102.tkwl',101084003,101084003,1080020792,'tech at fidalgo.net')': Table 'radius
._RADUSAGE200403' doesn't exist

At this point it logs the correct data to SQLRecoveryFile.

        AcctFailedLogFileName %L/sql/%Y/%m/%d/faildetail.%H.%M.%{GlobalVar:myip}.txt
        AcctLogFileFormat %{User-Name}  %{Timestamp}    %{Acct-Status-Type}     %{Acct-Sessision-Time}   

 %{Framed-IP-Address}    %{Acct-Session-Id}      %{NAS-IP-Address}        %{NAS-Port}     

%{GlobalVar:myip}       %{Calling-Station-Id}   %{Called-Station-Id}
        SQLRecoveryFile %L/sql/%Y/%m/%d/failsql.%H.%M.%{GlobalVar:myip}.txt



<AuthBy SQL>
        Identifier AuthBySQL
        DBSource dbi:mysql:radius
        DBUsername radiatorsqlacct
        DBAuth	radiatorsqlpass
        AuthSelect

        Timeout 1

        FailureBackoffTime 6000

        AcceptIfMissing

        AccountingTable _RADUSAGE%Y%m

#	I have also tried USERNAME,%{User-Name},formatted
#       which also did not work when radiator could not talk to mysql.

        AcctColumnDef   USERNAME,User-Name
        AcctColumnDef   USERNAME,User-Name
        AcctColumnDef   TIME_STAMP,Timestamp,integer
        AcctColumnDef   ACCTSTATUSTYPE,Acct-Status-Type
        AcctColumnDef   ACCTSESSTIME,Acct-Session-Time,integer
        AcctColumnDef   ACCTSESSIONID,Acct-Session-Id
        AcctColumnDef   FRAMEDIPADDR,Framed-IP-Address
        AcctColumnDef   NASIDENTIFIER,NAS-Identifier
        AcctColumnDef   NASIPADDR,NAS-Port,integer
        AcctColumnDef   NASPORT,NAS-Port,integer
        AcctColumnDef   CALLERID,Calling-Station-Id
        AcctColumnDef   CALLEDID,Called-Station-Id
        AcctColumnDef   MYIP,%{GlobalVar:myip},formatted

        AcctFailedLogFileName %L/sql/%Y/%m/%d/faildetail.%H.%M.%{GlobalVar:myip}.txt
        AcctLogFileFormat %{User-Name}  %{Timestamp}    %{Acct-Status-Type}     %{Acct-Sessision-Time}   

 %{Framed-IP-Address}    %{Acct-Session-Id}      %{NAS-IP-Address}        %{NAS-Port}     

%{GlobalVar:myip}       %{Calling-Station-Id}   %{Called-Station-Id}
        SQLRecoveryFile %L/sql/%Y/%m/%d/failsql.%H.%M.%{GlobalVar:myip}.txt
</AuthBy>

<Handler Client-Identifier=RadiatorList>
        AuthByPolicy ContinueAlways
        AuthBy AuthBySQL
        AuthBy UsersFILE
        AuthLog AuthLOG
        AuthLog AuthLOGUserPW
        #       ExcludeFromPasswordLog godsey jason dave
        AcctLogFileName %L/dailyusage/%Y/%m/%d/detail.%{GlobalVar:myip}txt
        AcctLogFileName %L/users/%U/%n.%Y.%m.%d.%{GlobalVar:myip}.txt
        PasswordLogFileName %L/pacwest/%Y/%m/%d/passwd.%{GlobalVar:myip}.txt
</Handler>


--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.