Hello all 
Didn’t know where to write such problem so I’m writening it here.
Some time ago I havr set up hotspot for users. Hotspot authenticates users by radius.
Service is being payed via sms. After user sends sms it gives him login and password.
Limit of the data depends from price of the sms ( 500MB limit, 1,5 GB limit and 3GB limit ).
Users are being added automatically to database with their limit and it works nice.
But yet I have problem with users that reaches their limit.
For example.
User have 500MB. He has used 501 MB so mikrotik have loged him out. Radius removes limit
and sets it up for negative value -1Mb. After he tries login again Mikrotik gets negative value
and user can connect wih unlimited data usage.
Where I have to search for a bug?
This is the trigger that updates the data for user after he disconects from hotspot.
Right now if value is negative it is being set to 1. This is tempolary just for getting users loged out all the time
if their reached theirs limit.
DROP TRIGGER IF EXISTS before_radacct_update;
DELIMITER //
CREATE TRIGGER before_radacct_update
BEFORE UPDATE ON radacct
FOR EACH ROW BEGIN
IF ( SELECT 1 FROM hotspotusers hsu WHERE login = OLD.username ) THEN
UPDATE hotspotusers AS hsu
SET hsu.limit_in = CASE WHEN hsu.limit_in - NEW.acctinputoctets<0 THEN 1 ELSE hsu.limit_in - NEW.acctinputoctets END
WHERE login = OLD.username;
UPDATE hotspotusers AS hsu
SET hsu.limit_out = CASE WHEN hsu.limit_out - NEW.acctoutputoctets <0 THEN 1 ELSE hsu.limit_out - NEW.acctoutputoctets END
WHERE login = OLD.username;
END IF;
END //
DELIMITER ;
This is the query of radius reply after authentication is correct.
It resolves the limit ammount.
SELECT
n.id, lower( n.login ) AS UserName,
'Mikrotik-Recv-Limit' AS Attribute,
n.limit_in AS Value,
':=' AS op
FROM hotspotusers AS n
WHERE n.login = login
UNION
SELECT
n.id, lower( n.login ) AS UserName,
'Mikrotik-Xmit-Limit' AS Attribute,
n.limit_out AS Value,
':=' AS op
FROM hotspotusers AS n
WHERE n.login = login;