I am using MySQL and I do not believe currently MySQL supports triggers. I am writing my own client and use 'radclient' as a mean to communicate with the server.
> -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Habib > Seifzadeh > Sent: Thursday, March 27, 2003 2:05 AM > To: [EMAIL PROTECTED] > Subject: Re: Session Timeout > > > > Why you don't use triggers? you can add a triger witch > updates radreply table > automatically when you update radacct. > by the way, which client do you use for this schema? and > which db server? do > you have any problem with postgresql? > > > > > On Tuesday 22 July 2003 04:05 am, Alex Chen wrote: > > I am trying to use Session-Timeout as the way to account > for how much total > > time > > the user is allowed to stay connect, and Acct-Session-Time > for the time the > > user > > spend on a session. Once the summation of the > Acct-Session-Time for all > > sessions > > of a user reaches the Session-Timeout, the user is denied service. > > > > The scheme is like this: > > radreply has the Session-Timeout attribute. When the user > is accepted, > > the value is sent to the client. The Radius client starts > accounting if > > the Session-Timeout is > 0. When the user disconnects, the > Radius client > > stops the accounting and sends back the new Session-Timeout > value, which is > > the difference of the original value minus the time the user spent. > > I modified the server's SQL accounting_stop_query to update not only > > 'radacct', but > > also 'radreply': > > > > accounting_stop_query = "UPDATE ${acct_table2} SET > AcctStopTime = '%S', > > AcctSessionTime = '%{Acct-Session-Time}', > > AcctInputOctets = '%{Acct-Input-Octets}', AcctOutputOctets = > > '%{Acct-Output-Octets}', > > AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = > > '%{Acct-Delay-Time}', > > ConnectInfo_stop = '%{Connect-Info}' > > WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = > > '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' > > AND AcctStopTime = 0; UPDATE ${authreply_table} SET Value = > > %{Session-Timeout} WHERE UserName = '%{SQL-User-Name}' > > AND Attribute = 'Session-Timeout'" > > > > Notice there are two UPDATE statements here. > > > > But I got the following error in the debug output: > > > > radius_xlat: 'Alex' > > rlm_sql (sql): sql_set_user escaped user --> 'Alex' > > radius_xlat: 'UPDATE radacct SET AcctStopTime = > '2003-07-21 14:57:48', > > AcctSessionTime = '5', AcctInputOctets = '', AcctOutputOctets = '', > > AcctTerminateCause = '', AcctStopDelay = '', > ConnectInfo_stop = '' WHERE > > AcctSessionId = '1' AND UserName = 'Alex' AND NASIPAddress > = '192.168.2.1' > > AND AcctStopTime = 0; UPDATE radreply SET Value = 0 WHERE > UserName = 'Alex' > > AND Attribute = 'Session-Timeout'' > > rlm_sql (sql): Reserving sql socket id: 3 > > rlm_sql_mysql: MYSQL check_error: 1064 received > > rlm_sql (sql): Couldn't update SQL accounting STOP record - > You have an > > error in your SQL syntax near '; UPDATE radreply SET Value = 0 WHERE > > UserName = 'Alex' AND Attribute = 'Session' at line 1 > > rlm_sql (sql): Released sql socket id: 3 > > modcall[accounting]: module "sql" returns fail > > modcall: group accounting returns fail > > > > If I manually type in the second update statement to change the > > Session-Timeout value in the radreply, it worked fine. > > > > What could be the problem? > > > > If this does not work as the way I expected, how can I get > the server to > > update the total time allowed for the user > > after each accounting start-stop session from within the server? > > > > > > > > - > > List info/subscribe/unsubscribe? See > > http://www.freeradius.org/list/users.html > > > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
