AW: (RADIATOR) SQL-Recovery file ... only numerical values

Hugh Irvine hugh at open.com.au
Wed Nov 9 21:10:36 CST 2005


Hello Martin, Hello Jason -

This actually turns out to be quite a difficult problem to solve, due  
to the way the DBI/DBD modules work.

As a result we will probably remove support for "SQLRecoveryFile",  
but there is a much better workaround availalable in any case.

The workaround makes use of the "AcctFailedLogFileName ...."  
parameter in the AuthBy SQL clause which will write plaintext  
accounting detail records to a flat file if the database is  
unavailable. You can then use the "radimportacct" utility in the  
goodies directory to load the flat file into the database when it  
becomes available.

Jason I don't know why you never got this response previously (I must  
have missed it).

Apologies for any inconvenience.

regards

Hugh



On 10 Nov 2005, at 05:57, Martin Wallner wrote:

> Jason, all....
>
> yep, that's the same problem I'm encountering. I was NOT faking a  
> broken SQL server, the SQL-Server was (for Radiator) broken (can't  
> connect), and I get always only the integers in the insert-string.
>
> The thing is, _not_ loosing the Data is _vital_ for our setup... I  
> can and have worked around anything else what comes up on this  
> table: double, triple, n-time, delayed, too long delayed (don't  
> ask :-), mangles (THANKS, CISCO :-<), loss of clients, anything,  
> but we _have to_ have _ALL_ and _ANY_ Accounting Records, and  
> scribbling it into a plain text log is not an option (not with 48  
> Mil entries a month you would have to crosscheck)
>
> I sniffed a bit around in the code, and, as far I could say - and I  
> couldn't verify it until yet - _maybe_ it's a thing of currently  
> NOT escaping the quotes for the strings when you use 'print'  as  
> you do in the sub for the write to the SQLRecoveryFile .... _maybe_  
> the solution would be to forget the actual quote and set it  
> together for the SQLFile from scratch (but that could have timing  
> issues)...
>
> I wasn't able to test it, and I probably wont be able for the next  
> weeks due to a hefty work scedule, I would have to take down one of  
> my production servers for testing, so please, if anyone could do  
> this, I think a lot of people here would very much appreciate it.
>
> =mw=
>
>
> Von: owner-radiator at open.com.au im Auftrag von L. Jason Godsey
> Gesendet: Di 08.11.2005 19:32
> An: radiator at open.com.au
> Betreff: Re: (RADIATOR) SQL-Recovery file ... only numerical values
>
>
> I'd like to see SQLRecoveryFile properly documented.
>
> SQLRecoveryFile is for the most part not useable for PostgreSQL and
> MySQL.
>
> Here is my original post on the subject:
> http://www.open.com.au/archives/radiator/2004-03/msg00162.html
>
> I haven't received a work around yet.
>
> L. Jason Godsey
>
>
> --- Martin Wallner <Martin.Wallner at eunet.co.at> wrote:
>
> > Hi list,
> >
> > I'm a bit stuck here....
> >
> > We are using PostGresql (8) to store (among other things) our
> > accounting info.
> >
> > Last week we had to take down the SQL-Server for a short  
> maintainance
> > cycle, after finishing this, I wanted to restore the accounting data
> > that was stored in the SQL-Restore-File, which was created like it
> > was set up to.
> >
> > Boy, was I surprised when I found out that only the 'integer' and
> > 'bigint' variables were actually filled out in the statements, but
> > not the strings ... and, besides that, that the syntax for the
> > 'insert' statements was not completely correct (missing semicolon at
> > the end)?.... Any ideas what happened here? Maybe 'declare' in the
> > AcctColumnDef that it's a string? But why does it work then when the
> > db-connection is on?
> >
> > It was good that this was happening in a controlled maintainance
> > cycle, so the data loss was negligable.
> >
> > Any ideas?
> >
> > ------------------------- config AuthSQL -----------------
> > <AuthBy SQL>
> >    Identifier SQLAccounting
> >    AuthSelect
> >
> > # primary database on SQL2, ONLY DB to put Accounting on,
> > # missed entries due to error will be handled by SQLRecoveryFile
> >    DBSource dbi:Pg:dbname=radius;host=172.27.0.92
> >    DBUsername radius
> >    DBAuth xxxxxx
> >    AccountingTable accounting
> >    AcctColumnDef   USERNAME,User-Name
> >    AcctColumnDef   TIME_STAMP,Timestamp,integer
> >    AcctColumnDef   ACCTSTATUSTYPE,Acct-Status-Type
> >    AcctColumnDef   ACCTDELAYTIME,Acct-Delay-Time,integer
> >    AcctColumnDef   ACCTINPUTOCTETS,Acct-Input-Octets,integer
> >    AcctColumnDef   ACCTOUTPUTOCTETS,Acct-Output-Octets,integer
> >    AcctColumnDef   ACCTSESSIONID,Acct-Session-Id
> >    AcctColumnDef   ACCTSESSIONTIME,Acct-Session-Time,integer
> >    AcctColumnDef   ACCTTERMINATECAUSE,Acct-Terminate-Cause
> >    AcctColumnDef   NASIDENTIFIER,NAS-Identifier
> >    AcctColumnDef   NASPORT,NAS-Port,integer
> >    AcctColumnDef   FRAMEDIPADDRESS,Framed-IP-Address
> >    AcctColumnDef   CALLEDSTATIONID,Called-Station-Id
> >    AcctColumnDef   CALLINGSTATIONID,Calling-Station-Id
> >    AcctColumnDef   ACCTINPUTPACKETS,Acct-Input-Packets,integer
> >    AcctColumnDef   ACCTOUTPUTPACKETS,Acct-Output-Packets,integer
> >    AcctColumnDef   ACCTMULTISESSID,Acct-Multi-Session-Id
> >    AcctColumnDef   ACCTLINKCOUNT,Acct-Link-Count,integer
> >    AcctColumnDef   PORT_NR,NAS-Port,integer
> >    AcctColumnDef   ACCTAUTHENTIC,Acct-Authentic,integer
> >    AcctColumnDef   NASIPADDR,NAS-IP-Address
> >    AcctColumnDef   LOGIN_HOST,Login-IP-Host
> >    AcctColumnDef   LOGIN_SERVICE,Login-Service,integer
> >    AcctColumnDef   USER_SERVICE_TYPE,Service-Type,integer
> >    AcctColumnDef   FRAMED_PROTOCOL,Framed-Protocol,integer
> >    AcctColumnDef   NASPORTTYPE,NAS-Port-Type
> >    Description Generic Accounting
> >    SQLRecoveryFile %L/sqlrecovery-%y-%m-%d.sql
> > #        AcctLogFileName /var/log/radiator/radacct/radius.acct
> > </AuthBy>
> >
> > ------------------------- output found in SQL-Recovery
> > ------------------
> > insert into accounting
> >
> (ACCTAUTHENTIC,ACCTDELAYTIME,ACCTINPUTOCTETS,ACCTINPUTPACKETS,ACCTOUTP 
> UTOCTETS,ACCTOUTPUTPACKETS,ACCTSESSIONI
> >
> D,ACCTSESSIONTIME,ACCTSTATUSTYPE,FRAMEDIPADDRESS,FRAMED_PROTOCOL,NASIP 
> ADDR,NASPORT,NASPORTTYPE,PORT_NR,TIME_STAMP,USERNAME,USER_SERV
> > ICE_TYPE) values
> > (1,5,193088,8681,117254,7329,,37485,,,1,,1345,,1345,1128708835,,2)
> > insert into accounting
> >
> (ACCTAUTHENTIC,ACCTDELAYTIME,ACCTINPUTOCTETS,ACCTINPUTPACKETS,ACCTOUTP 
> UTOCTETS,ACCTOUTPUTPACKETS,ACCTSESSIONI
> >
> D,ACCTSESSIONTIME,ACCTSTATUSTYPE,FRAMEDIPADDRESS,FRAMED_PROTOCOL,NASIP 
> ADDR,NASPORT,NASPORTTYPE,PORT_NR,TIME_STAMP,USERNAME,USER_SERV
> > ICE_TYPE) values
> > (1,0,2161317,19212,11639864,19004,,3870,,,1,,334,,334,1128708840,,2)
> > insert into accounting
> >
> (ACCTAUTHENTIC,ACCTDELAYTIME,ACCTINPUTOCTETS,ACCTINPUTPACKETS,ACCTOUTP 
> UTOCTETS,ACCTOUTPUTPACKETS,ACCTSESSIONI
> >
> D,ACCTSESSIONTIME,ACCTSTATUSTYPE,FRAMEDIPADDRESS,FRAMED_PROTOCOL,NASIP 
> ADDR,NASPORT,NASPORTTYPE,PORT_NR,TIME_STAMP,USERNAME,USER_SERV
> > ICE_TYPE) values
> >
> (1,5,12843276,186390,129036554,208643,,352488,,,1,,490,,490,1128708835 
> ,,2)
> > --------------------------------------------------------
> >
> > regards
> > Martin Wallner (=mw=)
> >
> > -----
> > Eunet Telekom GmbH                    e-mail  
> 'martin.wallner at eunet.co.at
> > vorm. Nextra Österreich                       e-mail  
> 'martin.wallner at nextranet.at'
> > vorm. ViP EDV-Dienstleistungs GesmbH  e-mail 'hostmaster at vip.at'
> > vorm. Gramtel Austria GmbH.           e-mail 'hostmaster at gramtel.at'
> > Systems                                       RIPE:     WM355-RIPE
> > Nussdorfer Lände 23                   NicAT:    WM503823-NICAT
> > 1190 Wien, Vienna, Austria            Tel. +43 (0) 59 1 59 - 1354
> >
> > --
> > 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.
> >
>
> --
> 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.
>


NB:

Have you read the reference manual ("doc/ref.html")?
Have you searched the mailing list archive (www.open.com.au/archives/ 
radiator)?
Have you had a quick look on Google (www.google.com)?
Have you included a copy of your configuration file (no secrets),
together with a trace 4 debug showing what is happening?

-- 
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
-
CATool: Private Certificate Authority for Unix and Unix-like systems.


--
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.


More information about the radiator mailing list