SQL Accounting... ¿Negative?

[Gabriel Trabanco Llano]

Hi everyone!

I do not know if it is really a problem… Because all data is ok but it is really strange.

Firstly, I use Freeradius 3 with SQL and SQL Counter module under Ubuntu Server and because I need billing periods by days I made a triggers in redacct table to separate users billing information by hours to another table because as you know, we can have sessions larger than our billing period. The solution is the file I added called fix_accounting.sql.

Well, In the solution I made a procedure that get the information of the session and if there is no info about the user in this new table, make an insert if not make an update with new information. This is done by the procedure call.

This procedure needs the username, the timestamp that sends the NAS to calculate the billing period, the downloaded/uploaded data to add and also the session time to add. Because of this in the trigger when update the redacct we do a rest between the new value to update the data and the previous to know the difference of data to add but, sometimes the billing data in the new table is negative because of this.


Finally, when I compare the data between the two tables they give me exactly the same data if there is no a session larger than a day even if there is negative values… Does anyone know what is happening? I have no clue about. Obviously the new data is lower than the old but, why? Am I doing something wrong?

The unique possibility I can imagine is that Freeradius is not updating the values correctly because it isn’t updating the values, it is expecting to add the amount of data to the row but that is not a possibility because in SQL you set a value and you have to calculate previously (maybe in the same SQL but you have to calculate the new value previously).

Thank you for your attention =)


Regards.



-------------- next part --------------
A non-text attachment was scrubbed...
Name: fix_accounting.sql
Type: application/octet-stream
Size: 6382 bytes
Desc: fix_accounting.sql
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20190904/e5f59d64/attachment.obj>