Problem with rml_sqlcounter with GigaByte datavolume

Hanno Schupp hanno.schupp at gmail.com
Sun Jun 5 01:22:47 CEST 2011 

Dear All,

can I ask for some pointers please. in my FreeRADIUS Version 2.1.8, for host
x86_64-pc-linux-gnu (Ubuntu LTS 10.04) installation I have followed the
Gigabyte instructions on the FreeRADIUS wiki's FAQ
http://wiki.freeradius.org/FAQ#Why+do+Acct-Input-Octets+and+Acct-Output-Octets+wrap+at+4+GB%3F.
The Usage is calculated correctly, but the check_item value is not what I
expect to see (1.7 GB as opposed th 6GB set in radcheck). I understand who
the system determines the counter value and it is correctly calculated, but
where does the check_item vlaue of 1.7GB come from? I have no idea to be
truthful.

Any pointers welcome.

Thanks in advance.


Here the data/logs:

Here the relevant debug log:
Sat Jun  4 23:10:21 2011 : Debug: rlm_sqlcounter: Entering module authorize
code
Sat Jun  4 23:10:21 2011 : Debug: sqlcounter_expand:  'SELECT
IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM radacct WHERE
UserName='%{User-Name}''
Sat Jun  4 23:10:21 2011 : Info: [noresettotal]         expand: SELECT
IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM radacct WHERE
UserName='%{User-Name}' -> SELECT IFNULL(SUM(AcctInputOctets) +
SUM(AcctOutputOctets),0) FROM radacct WHERE UserName='lapzel14'
Sat Jun  4 23:10:21 2011 : Debug: sqlcounter_expand:  '%{sql03:SELECT
IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM radacct WHERE
UserName='lapzel14'}'
Sat Jun  4 23:10:21 2011 : Info: [noresettotal] sql_xlat
Sat Jun  4 23:10:21 2011 : Info: [noresettotal]         expand: %{User-Name}
-> lapzel14
Sat Jun  4 23:10:21 2011 : Info: [noresettotal] sql_set_user escaped user
--> 'lapzel14'
Sat Jun  4 23:10:21 2011 : Info: [noresettotal]         expand: SELECT
IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM radacct WHERE
UserName='lapzel14' -> SELECT IFNULL(SUM(AcctInputOctets) +
SUM(AcctOutputOctets),0) FROM radacct WHERE UserName='lapzel14'
Sat Jun  4 23:10:21 2011 : Info: [noresettotal]         expand:
/var/log/freeradius/sqltrace.sql -> /var/log/freeradius/sqltrace.sql
Sat Jun  4 23:10:21 2011 : Debug: rlm_sql (sql03): Reserving sql socket id:
3
Sat Jun  4 23:10:21 2011 : Debug: rlm_sql_mysql: query:  SELECT
IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM radacct WHERE
UserName='lapzel14'
Sat Jun  4 23:10:21 2011 : Info: [noresettotal] sql_xlat finished
Sat Jun  4 23:10:21 2011 : Debug: rlm_sql (sql03): Released sql socket id: 3
Sat Jun  4 23:10:21 2011 : Info: [noresettotal]         expand:
%{sql03:SELECT IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM
radacct WHERE UserName='lapzel14'} -> 2147513300
Sat Jun  4 23:10:21 2011 : Debug: rlm_sqlcounter: (Check item - counter) is
less than zero
Sat Jun  4 23:10:21 2011 : Debug: rlm_sqlcounter: Rejected user lapzel14,
check_item=1705032704, counter=2147513300
Sat Jun  4 23:10:21 2011 : Info: ++[noresettotal] returns reject

Definition of the noresettotal module in counter.conf:
sqlcounter noresettotal {
        counter-name = Max-All-Total-Octets
        check-name = Max-All-Total
        reply-name = ChilliSpot-Max-Total-Octets
        sqlmod-inst = sql03
        key = User-Name
        reset = never
        query = "SELECT IFNULL(SUM(AcctInputOctets) +
SUM(AcctOutputOctets),0) FROM radacct WHERE UserName='%{%k}'"
}

and here the radcheck values for user 'lapzel14':
[image: Full Texts]<http://login02.chillifire.net/phpmyadmin/sql.php?db=radius&table=radcheck&sql_query=SELECT+%2A++FROM+%60radcheck%60+WHERE+%60UserName%60+LIKE+%27lapzel14%27&goto=sql.php%3Fdb%3Dradius%26amp%3Btable%3Dradcheck%26amp%3Btoken%3Ddf845dec99ab8224acc2d1d0fceddd89%26amp%3Bsql_query%3DSELECT%2B%252A%2B%2BFROM%2B%2560radcheck%2560%2BWHERE%2B%2560UserName%2560%2BLIKE%2B%2527lapzel14%2527&display_options_form=1&display_text=F&token=df845dec99ab8224acc2d1d0fceddd89>
id<http://login02.chillifire.net/phpmyadmin/sql.php?db=radius&table=radcheck&sql_query=SELECT+%2A++FROM+%60radcheck%60+WHERE+%60UserName%60+LIKE+%27lapzel14%27+ORDER+BY+%60radcheck%60.%60id%60+ASC&token=df845dec99ab8224acc2d1d0fceddd89>
UserName<http://login02.chillifire.net/phpmyadmin/sql.php?db=radius&table=radcheck&sql_query=SELECT+%2A++FROM+%60radcheck%60+WHERE+%60UserName%60+LIKE+%27lapzel14%27+ORDER+BY+%60radcheck%60.%60UserName%60+ASC&token=df845dec99ab8224acc2d1d0fceddd89>
Attribute<http://login02.chillifire.net/phpmyadmin/sql.php?db=radius&table=radcheck&sql_query=SELECT+%2A++FROM+%60radcheck%60+WHERE+%60UserName%60+LIKE+%27lapzel14%27+ORDER+BY+%60radcheck%60.%60Attribute%60+ASC&token=df845dec99ab8224acc2d1d0fceddd89>
op<http://login02.chillifire.net/phpmyadmin/sql.php?db=radius&table=radcheck&sql_query=SELECT+%2A++FROM+%60radcheck%60+WHERE+%60UserName%60+LIKE+%27lapzel14%27+ORDER+BY+%60radcheck%60.%60op%60+ASC&token=df845dec99ab8224acc2d1d0fceddd89>
Value<http://login02.chillifire.net/phpmyadmin/sql.php?db=radius&table=radcheck&sql_query=SELECT+%2A++FROM+%60radcheck%60+WHERE+%60UserName%60+LIKE+%27lapzel14%27+ORDER+BY+%60radcheck%60.%60Value%60+ASC&token=df845dec99ab8224acc2d1d0fceddd89>[image:
Edit]<http://login02.chillifire.net/phpmyadmin/tbl_change.php?db=radius&table=radcheck&where_clause=%60radcheck%60.%60id%60+%3D+572098&clause_is_unique=1&sql_query=SELECT+%2A++FROM+%60radcheck%60+WHERE+%60UserName%60+LIKE+%27lapzel14%27&goto=sql.php&token=df845dec99ab8224acc2d1d0fceddd89>[image:
Delete]<http://login02.chillifire.net/phpmyadmin/sql.php?db=radius&table=radcheck&sql_query=DELETE+FROM+%60radius%60.%60radcheck%60+WHERE+%60radcheck%60.%60id%60+%3D+572098&zero_rows=The+row+has+been+deleted&goto=sql.php%3Fdb%3Dradius%26table%3Dradcheck%26sql_query%3DSELECT%2B%252A%2B%2BFROM%2B%2560radcheck%2560%2BWHERE%2B%2560UserName%2560%2BLIKE%2B%2527lapzel14%2527%26zero_rows%3DThe%2Brow%2Bhas%2Bbeen%2Bdeleted%26goto%3Dsql.php%253Fdb%253Dradius%2526amp%253Btable%253Dradcheck%2526amp%253Btoken%253Ddf845dec99ab8224acc2d1d0fceddd89%2526amp%253Bsql_query%253DSELECT%252B%25252A%252B%252BFROM%252B%252560radcheck%252560%252BWHERE%252B%252560UserName%252560%252BLIKE%252B%252527lapzel14%252527%26token%3Ddf845dec99ab8224acc2d1d0fceddd89&token=df845dec99ab8224acc2d1d0fceddd89>
572098lapzel14User-Password:=loxdas[image:
Edit]<http://login02.chillifire.net/phpmyadmin/tbl_change.php?db=radius&table=radcheck&where_clause=%60radcheck%60.%60id%60+%3D+572102&clause_is_unique=1&sql_query=SELECT+%2A++FROM+%60radcheck%60+WHERE+%60UserName%60+LIKE+%27lapzel14%27&goto=sql.php&token=df845dec99ab8224acc2d1d0fceddd89>[image:
Delete]<http://login02.chillifire.net/phpmyadmin/sql.php?db=radius&table=radcheck&sql_query=DELETE+FROM+%60radius%60.%60radcheck%60+WHERE+%60radcheck%60.%60id%60+%3D+572102&zero_rows=The+row+has+been+deleted&goto=sql.php%3Fdb%3Dradius%26table%3Dradcheck%26sql_query%3DSELECT%2B%252A%2B%2BFROM%2B%2560radcheck%2560%2BWHERE%2B%2560UserName%2560%2BLIKE%2B%2527lapzel14%2527%26zero_rows%3DThe%2Brow%2Bhas%2Bbeen%2Bdeleted%26goto%3Dsql.php%253Fdb%253Dradius%2526amp%253Btable%253Dradcheck%2526amp%253Btoken%253Ddf845dec99ab8224acc2d1d0fceddd89%2526amp%253Bsql_query%253DSELECT%252B%25252A%252B%252BFROM%252B%252560radcheck%252560%252BWHERE%252B%252560UserName%252560%252BLIKE%252B%252527lapzel14%252527%26token%3Ddf845dec99ab8224acc2d1d0fceddd89&token=df845dec99ab8224acc2d1d0fceddd89>
572102lapzel14Expiration:=1311932528[image:
Edit]<http://login02.chillifire.net/phpmyadmin/tbl_change.php?db=radius&table=radcheck&where_clause=%60radcheck%60.%60id%60+%3D+572106&clause_is_unique=1&sql_query=SELECT+%2A++FROM+%60radcheck%60+WHERE+%60UserName%60+LIKE+%27lapzel14%27&goto=sql.php&token=df845dec99ab8224acc2d1d0fceddd89>[image:
Delete]<http://login02.chillifire.net/phpmyadmin/sql.php?db=radius&table=radcheck&sql_query=DELETE+FROM+%60radius%60.%60radcheck%60+WHERE+%60radcheck%60.%60id%60+%3D+572106&zero_rows=The+row+has+been+deleted&goto=sql.php%3Fdb%3Dradius%26table%3Dradcheck%26sql_query%3DSELECT%2B%252A%2B%2BFROM%2B%2560radcheck%2560%2BWHERE%2B%2560UserName%2560%2BLIKE%2B%2527lapzel14%2527%26zero_rows%3DThe%2Brow%2Bhas%2Bbeen%2Bdeleted%26goto%3Dsql.php%253Fdb%253Dradius%2526amp%253Btable%253Dradcheck%2526amp%253Btoken%253Ddf845dec99ab8224acc2d1d0fceddd89%2526amp%253Bsql_query%253DSELECT%252B%25252A%252B%252BFROM%252B%252560radcheck%252560%252BWHERE%252B%252560UserName%252560%252BLIKE%252B%252527lapzel14%252527%26token%3Ddf845dec99ab8224acc2d1d0fceddd89&token=df845dec99ab8224acc2d1d0fceddd89>
572106lapzel14Simultaneous-Use:=1[image:
Edit]<http://login02.chillifire.net/phpmyadmin/tbl_change.php?db=radius&table=radcheck&where_clause=%60radcheck%60.%60id%60+%3D+572110&clause_is_unique=1&sql_query=SELECT+%2A++FROM+%60radcheck%60+WHERE+%60UserName%60+LIKE+%27lapzel14%27&goto=sql.php&token=df845dec99ab8224acc2d1d0fceddd89>[image:
Delete]<http://login02.chillifire.net/phpmyadmin/sql.php?db=radius&table=radcheck&sql_query=DELETE+FROM+%60radius%60.%60radcheck%60+WHERE+%60radcheck%60.%60id%60+%3D+572110&zero_rows=The+row+has+been+deleted&goto=sql.php%3Fdb%3Dradius%26table%3Dradcheck%26sql_query%3DSELECT%2B%252A%2B%2BFROM%2B%2560radcheck%2560%2BWHERE%2B%2560UserName%2560%2BLIKE%2B%2527lapzel14%2527%26zero_rows%3DThe%2Brow%2Bhas%2Bbeen%2Bdeleted%26goto%3Dsql.php%253Fdb%253Dradius%2526amp%253Btable%253Dradcheck%2526amp%253Btoken%253Ddf845dec99ab8224acc2d1d0fceddd89%2526amp%253Bsql_query%253DSELECT%252B%25252A%252B%252BFROM%252B%252560radcheck%252560%252BWHERE%252B%252560UserName%252560%252BLIKE%252B%252527lapzel14%252527%26token%3Ddf845dec99ab8224acc2d1d0fceddd89&token=df845dec99ab8224acc2d1d0fceddd89>
572110lapzel14Max-All-Total:=6000000000
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20110605/19f60bff/attachment.html>

More information about the Freeradius-Users mailing list