Problems with rlm_sqlcounter
After many months of fault finding Im still in the dark about the counter not understanding values greater that 2 gigabytes. School is started next week and Im starting to panic as I dont have a solution for this as yet. I am running ubuntu 6.10 and freeradius 1.1.4, I have configured the following counter in radius.conf. sqlcounter mb { counter-name = Max-All-MB check-name = Max-All-MB reply-name = ChilliSpot-Max-Output-Octets sqlmod-inst = sql key = User-Name reset = never query = "SELECT SUM(AcctOutputOctets) FROM radacct where UserName='%{%k}'" } If "Max-All-MB" is greater than two gigabytes it "wraps" that value into a negative, if the values is > 3 gigabytes it simply returns a "-1". I have ran everything in debug mode (freeradius, mysqld, etc) and cannot find the cause. I have looked into rlm_sqlcounter.c and have traced the problem to variable "check_vp->lvalue", but I cannot find the place where the variable is defined in order to change it from a int to a long as I think that the variable is defined as 32bit and that is the reason it fails. I have setup many systems with various versions of freeradius and mysql running, yet this problem has being consistent through all my setups. From version 1.1.0 to 1.1.4 of freeradius this problem has occurred, this is a shame as the "reply-name" feature has recently being setup (freeradius 1.1.4) on the rlm_sqlcounter and its hard to use with a value < 2gb. I thank you in advance for any help which might steer me in the right direction, i have attached some of my finding on the error below. Freeradius -X mode, this is a dump of a accepted user and the denied user. Accepted User: sqlcounter_expand: 'SELECT SUM(AcctOutputOctets) FROM radacct where UserName='%{User-Name}'' radius_xlat: 'SELECT SUM(AcctOutputOctets) FROM radacct where UserName='fullspec.john'' sqlcounter_expand: '%{sql:SELECT SUM(AcctOutputOctets) FROM radacct where UserName='fullspec.john'}' radius_xlat: Running registered xlat function of module sql for string 'SELECT SUM(AcctOutputOctets) FROM radacct where UserName='fullspec.john'' rlm_sql (sql): - sql_xlat radius_xlat: 'fullspec.john' rlm_sql (sql): sql_set_user escaped user --> 'fullspec.john' radius_xlat: 'SELECT SUM(AcctOutputOctets) FROM radacct where UserName='fullspec.john'' rlm_sql (sql): Reserving sql socket id: 1 rlm_sql (sql): - sql_xlat finished rlm_sql (sql): Released sql socket id: 1 radius_xlat: '1601368097' rlm_sqlcounter: (Check item - counter) is greater than zero rlm_sqlcounter: Authorized user fullspec.john, check_item=2087831324, counter=1601368097 rlm_sqlcounter: Sent Reply-Item for user fullspec.john, Type=ChilliSpot-Max-Output-Octets, value=486463227 modcall[authorize]: module "mb" returns ok for request 1 modcall: leaving group authorize (returns ok) for request 1 Denied User: sqlcounter_expand: 'SELECT SUM(AcctOutputOctets) FROM radacct where UserName='%{User-Name}'' radius_xlat: 'SELECT SUM(AcctOutputOctets) FROM radacct where UserName='duler.peter'' sqlcounter_expand: '%{sql:SELECT SUM(AcctOutputOctets) FROM radacct where UserName='duler.peter'}' radius_xlat: Running registered xlat function of module sql for string 'SELECT SUM(AcctOutputOctets) FROM radacct where UserName='duler.peter'' rlm_sql (sql): - sql_xlat radius_xlat: 'duler.peter' rlm_sql (sql): sql_set_user escaped user --> 'duler.peter' radius_xlat: 'SELECT SUM(AcctOutputOctets) FROM radacct where UserName='duler.peter'' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql (sql): row[0] returned NULL rlm_sql (sql): Released sql socket id: 3 radius_xlat: '' rlm_sqlcounter: (Check item - counter) is less than zero rlm_sqlcounter: Rejected user duler.peter, check_item=-1312351118, counter=0 modcall[authorize]: module "mb" returns reject for request 0 modcall: leaving group authorize (returns reject) for request 0 -- ************************************* Tas Dionisakos IT Manager St Mary’s College and Newman College The University of Melbourne T: 03 9342 1708 M: 0439 655 565 E: tas@newman.unimelb.edu.au C: (0o (||||)(||||) o0) *************************************
Tas Dionisakos wrote:
After many months of fault finding Im still in the dark about the counter not understanding values greater that 2 gigabytes.
The last message you sent me, and the debug log below, shows that the SQL database is returning NOTHING if the counter is more than 2G. Please explain how "fixing" the counter module will make the SQL database return the value you need. Again, right now it's returning NOTHING. Update the SQL schema to handle numbers larger than 2G. See the SQL documentation for how to do this.
rlm_sql (sql): sql_set_user escaped user --> 'duler.peter' radius_xlat: 'SELECT SUM(AcctOutputOctets) FROM radacct where UserName='duler.peter'' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql (sql): row[0] returned NULL rlm_sql (sql): Released sql socket id: 3 radius_xlat: '' rlm_sqlcounter: (Check item - counter) is less than zero
Should I fix rlm_sqlcounter to print out a message saying "SQL module returned nothing. Impossible to do any counting!" Would that let you believe it's not a FreeRADIUS problem? Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
Alan, The Freeradius is returning nothing because of the following SQL query " SELECT SUM(AcctOutputOctets) FROM radacct where UserName='duler.peter'" Because this user "peter.duler" has never logged in before there is no record of his account in the radacct table, this is why it returns null. I will re-create this situation tomorrow at work and insert a session of the user in the radacct table, this will show the result without the null statement. I have spent alot of time on this problem and I am now 100% confident that its a variable somewhere that is causing the error. Tas. Alan DeKok wrote:
Tas Dionisakos wrote:
After many months of fault finding Im still in the dark about the counter not understanding values greater that 2 gigabytes.
The last message you sent me, and the debug log below, shows that the SQL database is returning NOTHING if the counter is more than 2G.
Please explain how "fixing" the counter module will make the SQL database return the value you need. Again, right now it's returning NOTHING.
Update the SQL schema to handle numbers larger than 2G. See the SQL documentation for how to do this.
rlm_sql (sql): sql_set_user escaped user --> 'duler.peter' radius_xlat: 'SELECT SUM(AcctOutputOctets) FROM radacct where UserName='duler.peter'' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql (sql): row[0] returned NULL rlm_sql (sql): Released sql socket id: 3 radius_xlat: '' rlm_sqlcounter: (Check item - counter) is less than zero
Should I fix rlm_sqlcounter to print out a message saying "SQL module returned nothing. Impossible to do any counting!"
Would that let you believe it's not a FreeRADIUS problem?
Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Tas Dionisakos wrote:
Because this user "peter.duler" has never logged in before there is no record of his account in the radacct table, this is why it returns null.
<sigh> So you said that the module didn't work when the counter got above 2G, and then to demonstrate this, showed a user who had never logged in before. Did I get that right? Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
participants (2)
-
Alan DeKok -
Tas Dionisakos