Triggers For Radius DB

------=_NextPart_000_018A_01C9FF5D.0197B250
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Good Day.



I hope someone can assist me.



I have a freeradius server running off mysql.



Now I would like to use triggers to negate some of the traffic logged within
it.





I tried to use following as trigger..



Create Trigger ftp

BEFORE UPDATE ON radacct

FOR EACH ROW

BEGIN

UPDATE radacct SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets) WHERE
NASPortId=21;

UPDATE radacct SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets) WHERE
NASPortId=21;

END;







And Many variants of that but only ends up locking the db in someway with
messages such as.





Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: Cannot store result

Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: MySQL error 'Can't update
table 'radacct' in stored function/trigger because it is already used by
statement which invoked this stored function/trigger.'





From Freeradius.





I have managed to get triggers working to a separate db altogether but not
from the same db to the same db.





What am I doing wrong?







Regards.





Marcel Grandemange


------=_NextPart_000_018A_01C9FF5D.0197B250--

Re: Triggers For Radius DB

In the last episode (Jul 07), Marcel Grandemange said:
> I hope someone can assist me. I have a freeradius server running off
> mysql. Now I would like to use triggers to negate some of the traffic
> logged within it. I tried to use following as trigger..
>
> Create Trigger ftp
> BEFORE UPDATE ON radacct
> FOR EACH ROW
> BEGIN
> UPDATE radacct SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets) WHERE NASPortId=21;
> UPDATE radacct SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets) WHERE NASPortId=21;
> END;
>
> And Many variants of that but only ends up locking the db in someway with
> messages such as.
>
> Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: Cannot store result
> Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: MySQL error 'Can't update table 'radacct' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.'

You don't want to UPDATE the table, since as you have seen it will cause an
error. The only row you can change in the table being modified in a trigger
is the active row itself, and you must change its values by SETting
NEW.fieldname:

IF NEW.NASPortID = 21 THEN
SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets);
SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets):
END IF;


--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Triggers For Radius DB

>In the last episode (Jul 07), Marcel Grandemange said:
> I hope someone can assist me. I have a freeradius server running off
> mysql. Now I would like to use triggers to negate some of the traffic
> logged within it. I tried to use following as trigger..
>
> Create Trigger ftp
> BEFORE UPDATE ON radacct
> FOR EACH ROW
> BEGIN
> UPDATE radacct SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets) WHERE
NASPortId=21;
> UPDATE radacct SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets) WHERE
NASPortId=21;
> END;
>
> And Many variants of that but only ends up locking the db in someway with
> messages such as.
>
> Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: Cannot store result
> Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: MySQL error 'Can't update
table >>'radacct' in stored function/trigger because it is already used by
statement which >>invoked this stored function/trigger.'

>>You don't want to UPDATE the table, since as you have seen it will cause
an
>>error. The only row you can change in the table being modified in a
trigger
>>is the active row itself, and you must change its values by SETting
>>NEW.fieldname:

>>IF NEW.NASPortID = 21 THEN
>> SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets);
>> SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets):
>>END IF;


>>--
>> Dan Nelson
>> dnelson@allantgroup.com

Thank you so much, this worked 100%!



__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4223 (20090708) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org